1 PACKAGE BODY JAI_OM_WDD_TRIGGER_PKG AS
2 /* $Header: jai_om_wdd_t.plb 120.32.12020000.6 2013/04/07 05:52:39 mmurtuza ship $ */
3
4 /* REM +======================================================================+
5 REM NAME ARD_T1
6 REM
7 REM DESCRIPTION Called from trigger JAI_OM_WDD_ARIUD_T1
8 REM
9 REM NOTES Refers to old trigger JAI_OM_WDD_ARD_T3
10 REM
11 REM +======================================================================+
12 */
13 PROCEDURE ARD_T1 ( pr_old t_rec%type , pr_new t_rec%type , pv_action varchar2 , pv_return_code out nocopy varchar2 , pv_return_message out nocopy varchar2 ) IS
14 v_DELIVERY_DETAIL_ID Number; --File.Sql.35 Cbabu := pr_old.DELIVERY_DETAIL_ID;
15 x Number; --File.Sql.35 Cbabu := 0;
16
17 Cursor del_count IS
18 Select 1
19 From JAI_OM_WSH_LINES_ALL
20 Where delivery_detail_id = v_delivery_detail_id;
21
22
23 BEGIN
24 pv_return_code := jai_constants.successful ;
25
26 v_DELIVERY_DETAIL_ID := pr_old.DELIVERY_DETAIL_ID;
27 x := 0;
28
29 /*------------------------------------------------------------------------------------------
30 FILENAME: JA_IN_WSH_DLRY_DTLS_AD_TRG.sql
31
32 CHANGE HISTORY:
33 S.No Date Author and Details
34 1. 2001/07/12 Anuradha Parthasarathy
35 Code added to ensure that the trigger does not fire
36 For Non Indian OU.
37
38 2. 29-nov-2004 ssumaith - bug# 4037690 - File version 115.1
39 Check whether india localization is being used was done using a INR check in every trigger.
40 This check has now been moved into a new package and calls made to this package from this trigger
41 If the function jai_cmn_utils_pkg.check_jai_exists returns true it means INR is the set of books currency ,
42 Hence if this function returns FALSE , control should return.
43
44 3. 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
45 DB Entity as required for CASE COMPLAINCE. Version 116.1
46
47 4. 13-Jun-2005 File Version: 116.2
48 Ramananda for bug#4428980. Removal of SQL LITERALs is done
49
50
51 02/11/2006 for Bug 5228046 by SACSETHI, File version 120.2
52 Forward porting the change in 11i bug 5365523 (Additional CVD Enhancement).
53 This bug has datamodel and spec changes.
54 5. 30-AUG-2011 vkaranam for bug#12807591
55 Issue:In Trading Organization, RG23D reversal accounting for Sales order shipment is hitting the OSP Excise account
56 provided in the Organization additional Info screen instead of the excise paid payble account.
57 fix:
58 Replace excise_paid_account with excise_rcvble_account for excise tax.
59
60 6. 27-Dec-2012 mmurtuza for bug 16022303
61 Issue: RG23D REVERSAL ENTRY DEBITS COGS IN CASE OF INCLUSIVE TAX
62 Fix: Changed code in RG23D_REV_ACCOUNTING to pass excise paid account. Also added code bifurcate amount to excise, cess and she cess
63 paid accounts
64
65
66
67 Future Dependencies For the release Of this Object:-
68 (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/
69 A datamodel change )
70 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
71 Current Version Current Bug Dependent Files Version Author Date Remarks
72 Of File On Bug/Patchset Dependent On
73
74 ja_in_wsh_dlry_dtls_ad_trg.sql
75 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
76 115.1 4037690 IN60105D2 ja_in_util_pkg_s.sql 115.0 Ssumaith 29-Nov-2004 Call to this function.
77 ja_in_util_pkg_s.sql 115.0 Ssumaith
78
79 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
80 --------------------------------------------------------------------------------------------*/
81
82 /* following code added by ssumaith - bug# 4037690 */
83 -- if pr_new.org_id is not null then
84 -- If jai_cmn_utils_pkg.check_jai_exists( P_CALLING_OBJECT => 'JA_IN_WSH_DLRY_DTLS_AD_TRG', P_ORG_ID => pr_new.org_id) = false then
85 -- return;
86 -- end if;
87 -- end if;
88
89 /* ends here additions by ssumaith */
90
91 Open del_count;
92 Fetch del_count into x;
93 Close del_count;
94
95 If nvl(x,0) <> 1 then
96 Return;
97 Else
98 DELETE JAI_OM_WSH_LINES_ALL
99 WHERE delivery_detail_id = v_DELIVERY_DETAIL_ID;
100
101 DELETE JAI_OM_WSH_LINE_TAXES
102 WHERE delivery_detail_id = v_DELIVERY_DETAIL_ID;
103 End If;
104
105 END ARD_T1 ;
106
107 /*
108 REM +======================================================================+
109 REM NAME ARU_T1
110 REM
111 REM DESCRIPTION Called from trigger JAI_OM_WDD_ARIUD_T1
112 REM
113 REM NOTES Refers to old trigger JAI_OM_WDD_ARU_T1
114 REM
115 REM +======================================================================+
116 */
117 PROCEDURE ARU_T1 ( pr_old t_rec%type , pr_new t_rec%type , pv_action varchar2 , pv_return_code out nocopy varchar2 , pv_return_message out nocopy varchar2 ) IS
118 BEGIN
119 pv_return_code := jai_constants.successful ;
120 /*------------------------------------------------------------------------------------------
121 FILENAME: ja_in_receipts_match_trg.sql
122
123 CHANGE HISTORY:
124 S.No Date Author and Details
125 1. 2002/03/11 Vijay
126 Trigger written to delete data from tables
127 JAI_CMN_MATCH_RECEIPTS, JAI_CMN_MATCH_TAXES.
128 After Match Receipt is done, when back order is done,
129 this trigger enables to match again.
130
131 2. 29/11/2005 Aparajita for bug#4036241. Version#115.1
132
133 Introduced the call to centralized packaged procedure,
134 jai_cmn_utils_pkg.check_jai_exists to check if localization has been installed.
135
136 3. 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
137 DB Entity as required for CASE COMPLAINCE. Version 116.1
138
139 4. 13-Jun-2005 Ramananda for bug#4428980. File Version: 116.2
140 Removal of SQL LITERALs is done
141 Dependency:
142 ----------
143
144 Sl No. Bug Dependent on
145 Bug/Patch set Details
146 -------------------------------------------------------------------------------------------------
147 1 4036241 4033992 Call to jai_cmn_utils_pkg.check_jai_exists, whcih was created thru bug
148 4033992.
149 ja_in_util_pkg_s.sql 115.0
150 ja_in_util_pkg_b.sql 115.0
151
152 --------------------------------------------------------------------------------------------------*/
153 --if
154 -- jai_cmn_utils_pkg.check_jai_exists (p_calling_object => 'JA_IN_WSH_BACKORDER_AU_TRG', p_inventory_orgn_id => pr_new.organization_id)
155 -- =
156 -- FALSE
157 --then
158 /* India Localization funtionality is not required */
159 -- return;
160 --end if;
161
162
163 Delete from JAI_CMN_MATCH_RECEIPTS
164 Where ref_line_id = pr_new.delivery_detail_id;
165
166 Delete from JAI_CMN_MATCH_TAXES
167 Where ref_line_id = pr_new.delivery_detail_id;
168 END ARU_T1 ;
169
170 /*
171 REM +======================================================================+
172 REM NAME ARU_T2
173 REM
174 REM DESCRIPTION Called from trigger JAI_OM_WDD_ARIUD_T1
175 REM
176 REM NOTES Refers to old trigger JAI_OM_WDD_ARU_T2
177 REM
178 REM +======================================================================+
179 */
180 PROCEDURE ARU_T2 ( pr_old t_rec%type , pr_new t_rec%type , pv_action varchar2 , pv_return_code out nocopy varchar2 , pv_return_message out nocopy varchar2 ) IS
181 v_inventory_item_id NUMBER; --File.Sql.35 Cbabu :=pr_new.inventory_item_id ;
182 v_organization_id NUMBER ; --File.Sql.35 Cbabu :=pr_new.organization_id ;
183 v_subinventory VARCHAR2(10); --File.Sql.35 Cbabu :=pr_new.subinventory ;
184 v_delivery_detail_id NUMBER; --File.Sql.35 Cbabu :=pr_new.delivery_detail_id ;
185 v_source_header_type_id NUMBER ; --File.Sql.35 Cbabu :=pr_new.source_header_type_id ;
186 v_shipped_quantity NUMBER ; --File.Sql.35 Cbabu := nvl(pr_new.shipped_quantity,0) ;
187 v_matched_qty NUMBER ; --File.Sql.35 Cbabu := 0 ;
188 v_trading_flag VARCHAR2(1) ;
189 --added for bug#6327274, start
190 v_bonded JAI_INV_SUBINV_DTLS.bonded%TYPE;
191 lv_allow_shipment_wo_excise VARCHAR2(1);
192 -- bug#6327274, end
193 v_trad_register_code VARCHAR2(30) ;
194 v_item_trading_flag VARCHAR2(1) ;
195 v_location_id NUMBER ;
196 v_exe_flag VARCHAR2(150) ;
197 v_mod_flag VARCHAR2(150) ;
198 v_container_item_flag mtl_system_items.container_item_flag%type ;
199 lv_inventory_item_flag mtl_system_items.inventory_item_flag%type ;/*added by csahoo for bug#8500697*/
200
201 /*
202 ||Added by bgowrava for the forward porting bug 5631784 (TCS enhancement)
203 */
204
205 /* variables used in debug package */
206 lv_object_name jai_cmn_debug_contexts.LOG_CONTEXT%TYPE ;
207 lv_member_name jai_cmn_debug_contexts.LOG_CONTEXT%TYPE;
208 lv_context jai_cmn_debug_contexts.LOG_CONTEXT%TYPE;
209 ln_reg_id NUMBER ;
210 le_error EXCEPTION ;
211
212 CURSOR Location_Cursor
213 IS
214 SELECT
215 NVL(Location_id,0),
216 trading,
217 NVL(bonded,'N') bonded --added for bug#6327274
218 FROM
219 JAI_INV_SUBINV_DTLS
220 WHERE
221 Sub_Inventory_Name = v_subinventory AND
222 organization_id = v_organization_id ;
223
224 --added the cursor for bug#6327274
225 cursor c_orgn_Null_site_info is
226 select exc_shpt_wo_exc_tax_flag
227 from JAI_CMN_INVENTORY_ORGS
228 where organization_id = pr_new.organization_id
229 and location_id = 0;
230
231
232 CURSOR item_trading_cur
233 IS
234 SELECT
235 Item_Trading_Flag
236 FROM
237 JAI_INV_ITM_SETUPS
238 WHERE
239 organization_id = v_organization_id AND
240 inventory_item_id = v_inventory_item_id ;
241
242 CURSOR Trading_register_code_cur(
243 p_organization_id NUMBER ,
244 p_location_id NUMBER ,
245 p_delivery_detail_id NUMBER ,
246 p_order_type_id NUMBER
247 )
248 IS
249 SELECT
250 A.register_code
251 FROM
252 JAI_OM_OE_BOND_REG_HDRS A, JAI_OM_OE_BOND_REG_DTLS b
253 WHERE
254 a.organization_id = p_organization_id AND
255 a.location_id = p_location_id AND
256 a.register_id = b.register_id AND
257 b.order_flag = 'Y' AND
258 b.order_type_id = p_order_type_id ;
259 -- a.register_code LIKE '23D%'; -- commented for bug#6327274
260
261 /*
262 Code added by aiyer for the bug 3844145.
263 Removed the group by subinventory clause from the query. The matched qty should be considered irrespective of the
264 subinventory
265 */
266
267 CURSOR matched_receipt_cur1
268 IS
269 SELECT
270 sum(a.quantity_applied) quantity_applied
271 FROM
272 JAI_CMN_MATCH_RECEIPTS a
273 WHERE
274 a.ref_line_id = v_delivery_detail_id
275 AND a.order_invoice = 'O' ;
276
277 CURSOR get_item_attributes
278 IS
279 SELECT
280 excise_flag ,-- Commented attribute1 by Brathod for Bug# 4299606 (DFF Elimination)
281 modvat_flag ,-- Commented attribute2 by Brathod for Bug# 4299606 (DFF Elimination)
282 nvl(container_item_flag,'N'), --Added by Nagaraj.s for Bug3123613.
283 nvl(inventory_item_flag,'N') --added by csahoo for bug#8500697
284 FROM
285 mtl_system_items msi,
286 JAI_INV_ITM_SETUPS jmsi -- Added by Brathod for Bug# 4299606 (DFF Elimination)
287 WHERE msi.organization_id = jmsi.organization_id
288 AND msi.inventory_item_id = jmsi.inventory_item_id
289 AND jmsi.inventory_item_id = v_Inventory_Item_Id -- Added by Brathod for Bug# 4299606 (DFF Elimination)
290 AND jmsi.organization_id = v_organization_id; -- Added by Brathod for Bug# 4299606 (DFF Elimination)
291
292
293 -- Following cursors added by sriram bug# 2165355
294 CURSOR c_check_lc_order
295 IS
296 SELECT
297 lc_flag
298 FROM
299 JAI_OM_OE_SO_LINES
300 WHERE
301 lc_flag = 'Y' AND
302 header_id = pr_new.source_header_id;
303
304 /*
305 This query has been modified aiyer for the bug #3039521.
306 Add the delivery_detail_id in the where clause so that the lc matched quantity for a particular delivery_detail_id is checked.
307 This would ensure that even though a order line has been split before release ,every split order Line
308 (based on the delivery detail id ) needs to be lc matched for the shipped quantity.
309 */
310
311 --Added parameter by JMEENA for bug#6731913
312 CURSOR c_matched_qty_cur(cp_delivery_detail_id IN NUMBER)
313 IS
314 SELECT
315 sum(qty_matched)
316 FROM
317 JAI_OM_LC_MATCHINGS
318 WHERE
319 order_header_id = pr_new.source_header_id AND
320 -- order_line_id = pr_new.source_line_id AND --commented by csahoo for bug#5686360
321 delivery_detail_id = cp_delivery_detail_id AND
322 release_flag is null;
323
324 v_check_lc_order VARCHAR2(1);
325 v_lc_qty_matched NUMBER;
326 v_lc_shipped_qty NUMBER; --File.Sql.35 Cbabu := pr_new.Shipped_quantity;
327
328 -- ends here additions bug sriram bug# 2165355
329
330 /* Start, bug#5686360
331 following code is to correct the existing lc_matching order line to new split line id
332 */
333 cursor c_order_line is
334 select split_from_line_id --, split_by
335 from oe_order_lines_all
336 where line_id = pr_new.source_line_id;
337
338 cursor c_lc_mtch_dlry_line is
339 select order_line_id
340 from JAI_OM_LC_MATCHINGS
341 where delivery_detail_id = v_delivery_detail_id;
342 r_order_line c_order_line%rowtype;
343 r_lc_mtch_dlry_line c_lc_mtch_dlry_line%rowtype;
344 ln_lc_update_cnt number;
345 /* end bug#5686360 */
346
347
348 -- Following cursors added by sriram - bug # 2689417
349 -- The following cursor gets the tax amount for the line_id from JAI_OM_OE_SO_LINES table
350 CURSOR c_ja_in_so_lines_tax_amt
351 IS
352 SELECT
353 tax_amount
354 FROM
355 JAI_OM_OE_SO_LINES
356 WHERE
357 line_id = pr_new.source_line_id AND
358 header_id = pr_new.source_header_id;
359
360
361 -- The following cursor gets the sum of tax amount for the line_id from JAI_OM_OE_SO_TAXES table
362 CURSOR c_ja_in_so_tax_lines_tax_amt
363 IS
364 SELECT
365 nvl(sum(so_tax.tax_amount),0)
366 FROM
367 JAI_OM_OE_SO_TAXES so_tax
368 , jai_cmn_taxes_all tax -- Added by Jia Li for inclusive tax on 2008/01/07
369 WHERE
370 line_id = pr_new.source_line_id AND
371 header_id = pr_new.source_header_id
372 AND so_tax.tax_id = tax.tax_id -- Added by Jia Li for inclusive tax on 2008/01/07
373 AND NVL(tax.inclusive_tax_flag,'N') = 'N' ; -- Added by Jia Li for inclusive tax on 2008/01/07
374
375
376
377 v_line_tax_amount Number; --File.Sql.35 Cbabu :=0; -- to hold the tax amount for a line - based on JAI_OM_OE_SO_LINES
378 v_sum_tax_amount Number; --File.Sql.35 Cbabu :=0; -- to hold the sum of taxes for a line - based on JAI_OM_OE_SO_TAXES
379 -- ends here -- cursors added by sriram - bug # 2689417
380
381
382 /*
383 The following cursor has been added by aiyer for the bug #3039521.
384 Get the Currency code using the current org id from the table hr_operating_units and gl_sets_of_books
385 */
386 -- Start of cursor added by aiyer for the bug #3039521
387 /* bug 5243532. Added by Lakshmi Gopalsami
388 Removed the reference to cursor sob_cur
389 as this is not used anywhere.
390 */
391
392 v_currency_code GL_SETS_OF_BOOKS.CURRENCY_CODE%TYPE ;
393
394 -- End of cursor Sob_Cur added for the bug #3039521.
395 -- Start Of Bug #
396 CURSOR c_chk_exc_exmpt_rule
397 IS
398 SELECT
399 a.excise_exempt_type ,
400 a.line_number ,
401 a.shipment_line_number ,
402 quantity -- added by sriram bug# 3441684
403 FROM
404 JAI_OM_OE_SO_LINES a
405 WHERE
406 a.line_id = pr_new.source_line_id AND
407 a.header_id = pr_new.source_header_id ;
408
409 lv_excise_exempt_type JAI_OM_OE_SO_LINES.EXCISE_EXEMPT_TYPE%TYPE ;
410 ln_line_number JAI_OM_OE_SO_LINES.LINE_NUMBER%TYPE ;
411 ln_shipment_line_number JAI_OM_OE_SO_LINES.SHIPMENT_LINE_NUMBER%TYPE ;
412 lv_ret_flag VARCHAR2(10) ;
413 lv_error_msg VARCHAR2(1996) ;
414 v_quantity number;
415 -- End Of Bug #
416
417 -- added by sriram - bug#3441684
418 cursor c_orgn_info is
419 select trading
420 from JAI_CMN_INVENTORY_ORGS
421 where organization_id = pr_new.organization_id
422 and location_id = pr_new.ship_from_location_id ;
423 --ends here additions by sriram - bug#3441684
424
425 /*
426 || Begin Bug#4245073
427 || Author : Brathod
428 || Date : 17-Mar-2005
429 */
430 ln_vat_cnt NUMBER DEFAULT 0 ;
431 ln_exc_cnt NUMBER DEFAULT 0 ; --added for bug#8538431
432 ln_cnt_org_loc_setup NUMBER DEFAULT 0 ;
433 lv_applicable JAI_RGM_ITM_TMPL_ATTRS.ATTRIBUTE_CODE%TYPE;
434 lv_process_flag VARCHAR2 (2);
435 lv_process_message VARCHAR2 (1000);
436
437 CURSOR cur_chk_vat_exists (cp_line_id JAI_OM_OE_SO_TAXES.LINE_ID%TYPE,
438 cp_header_id JAI_OM_OE_SO_TAXES.HEADER_ID%TYPE
439 )
440 IS
441 SELECT 1
442 FROM JAI_OM_OE_SO_TAXES jstl ,
443 JAI_CMN_TAXES_ALL jtc ,
444 jai_regime_tax_types_v tax_types
445 WHERE jstl.line_id = cp_line_id
446 AND jstl.header_id = cp_header_id
447 AND jtc.tax_id = jstl.tax_id
448 AND jtc.tax_type = tax_types.tax_type
449 AND tax_types.regime_code = jai_constants.vat_regime;
450
451 --added the following cursor for bug#8538431
452 CURSOR cur_chk_excise_exists (cp_line_id JAI_OM_OE_SO_TAXES.LINE_ID%TYPE,
453 cp_header_id JAI_OM_OE_SO_TAXES.HEADER_ID%TYPE
454 )
455 IS
456 SELECT 1
457 FROM JAI_OM_OE_SO_TAXES jstl ,
458 JAI_CMN_TAXES_ALL jtc
459 WHERE jstl.line_id = cp_line_id
460 AND jstl.header_id = cp_header_id
461 AND jtc.tax_id = jstl.tax_id
462 AND jtc.tax_type in ( jai_constants.tax_type_excise,
463 jai_constants.tax_type_exc_additional,
464 jai_constants.tax_type_exc_other,
465 jai_constants.tax_type_exc_edu_cess,
466 jai_constants.tax_type_sh_exc_edu_cess);
467
468 CURSOR cur_chk_org_loc_setup (cp_organization_id JAI_OM_WSH_LINES_ALL.LOCATION_ID%TYPE,
469 cp_location_id JAI_OM_WSH_LINES_ALL.ORGANIZATION_ID%TYPE
470 )
471 IS
472 SELECT 1
473 FROM jai_rgm_parties rgmpt,
474 JAI_RGM_DEFINITIONS rgms
475 WHERE rgmpt.regime_id = rgms.regime_id
476 AND rgmpt.location_id = cp_location_id
477 AND rgmpt.organization_id = cp_organization_id
478 AND rgms.regime_code = jai_constants.vat_regime;
479
480 /*
481 ||End of bug 4245073
482 */
483
484
485 /*
486 || Added by bgowrava for the forward porting bug 5631784
487 */
488 tab_ooh OE_ORDER_HEADERS_ALL%ROWTYPE ;
489 CURSOR cur_get_org_hdr (cp_header_id OE_ORDER_HEADERS_ALL.HEADER_ID%TYPE)
490 IS
491 SELECT
492 *
493 FROM
494 oe_order_headers_all
495 WHERE
496 header_id = cp_header_id ;
497 --Added cursor by JMEENA for bug#6731913
498 CURSOR cur_tot_shipped_quantity (cp_delivery_detail_id IN NUMBER)
499 IS
500 SELECT sum ( shipped_quantity)
501 FROM jai_wsh_del_details_gt
502 WHERE delivery_detail_id = cp_delivery_detail_id
503 OR split_from_delivery_detail_id = cp_delivery_detail_id;
504 v_tot_lc_shipped_qty NUMBER;
505 ln_cnt NUMBER;
506 ln_unprocessed_recs NUMBER;
507 --End bug#6731913
508
509 /* Added for bug#8924003, Start */
510 CURSOR c_ato_line_id IS
511 SELECT
512 oel.ato_line_id
513 FROM
514 oe_order_headers_all oeh,
515 oe_order_lines_all oel
516 WHERE
517 oeh.header_id = pr_new.source_header_id
518 AND oeh.header_id = oel.header_id
519 AND item_type_code = 'CONFIG' ;
520
521 CURSOR c_model_item_id(cp_ato_line_id oe_order_lines_all.line_id%TYPE) IS
522 SELECT
523 oel.inventory_item_id
524 FROM
525 oe_order_lines_all oel
526 WHERE
527 oel.line_id = cp_ato_line_id
528 AND item_type_code = 'MODEL' ;
529
530 ln_ato_line_id NUMBER ;
531 ln_model_item_id NUMBER ;
532 /*bug#8924003, end*/
533
534 PROCEDURE set_debug_context
535 IS
536 BEGIN
537 lv_context := rtrim(lv_object_name || '.'||lv_member_name,'.');
538 END set_debug_context;
539
540 /* End of bug 5631784*/
541
542 BEGIN
543 pv_return_code := jai_constants.successful ;
544 /***************************************************************************************************************************************************
545
546 Change History :
547
548 1. Sriram - Bug # 2645439. File Version 615.1
549 Created the trigger.This trigger was created because , when the
550 interface trip stop runs into an error in the case of a trading organization , because the
551 Shipment is not matched against any receipt , it shows a form level error message and does
552 not allow shipment to continue.
553
554 2. Sriram - Bug # 2689417 File Version 615.2
555 Added another condition in the WHEN Clause of the trigger to check that the trigger
556 fires only when the Released_status field is set to 'C' and does not fire
557 on updates of other fields at which point the releaed_status is 'C'.
558
559 Also the update statement , which updates the released_flag in the JAI_OM_LC_MATCHINGS table
560 has been commented , because the update should happen after the shipping has completed.
561
562 Also an error message has to be thrown , when shipping is done , for an order
563 where the tax amounts in the JAI_OM_OE_SO_TAXES and JAI_OM_OE_SO_LINES do not tally.
564
565 3. Avishek - Bug # 2928261
566 Added a RAISE_APPLICATION_ERROR to give an error message when a sub-inventory is not
567 associated properly with a location ID.
568
569 4. Aiyer - Bug # 3039521, File Version 616.1 Date 09-Jul-2003
570 Issue:-
571 1. Orders which are partially matched and shipped post splitting of lines without full matching get shipped and are not stopped by
572 this trigger as it does when the lines are not split.
573
574 2. Another issue reported with this trigger was that it used to get fired even in case for non Indian Shipments.
575 These shipments need not have the Localization sub-inventory setup - Mandatory for all Indian Shipments
576 (using localization). However as this trigger used to get fired in those cases also hence the error
577 "Mandatory Localization sub-inventory setup is not made" used to get invoked and the execution used
578 to stop.
579
580 Solution:-
581 1. While checking that for a line being shipped, if the LC flag is enabled , then the amount being shipped should be
582 lc matched. However it was not being checked that, in case of a split line also the amount pertaining to the
583 same delivery_detail_id should be lc matched.
584 Added a where clause in the cursor c_matched_qty_cur to also include the check for the delivery_detail_id.
585
586 2.Put a check in the beginning of the trigger that if the functional currency is NOT 'INR' then
587 return from the trigger, i.e this trigger should get bypassed in case of Non Indian Shipments
588 (Global Scenario).
589
590 5. Aiyer - Bug #3032569, File Version 616.2 Date 24-Jul-2003
591 Issue:-
592 The trigger checks that the match receipt functionality is performed in scenario's of
593 Trading Domestic Without Excise and Export Without excise.
594 This check is not required. The match receipts only needs to be done for
595 'Trading Domestic With Excise' and 'Export with Excise' type of Scenario's
596
597 Solution:-
598 Modified the IF statment to raise an error only when trading register code is in 23D_DOMESTIC_EXCISE and 23D_EXPORT_EXCISE.
599 The other two trading register_codes '23D_DOM_WITHOUT_EXCISE' and '23D_EXPORT_WITHOUT_EXCISE' have been removed as matched receipts
600 is not relevant in this case.
601 Dependency Introduced Due to this Bug : -
602 None
603
604 6. Nagaraj.s -Bug#3123613, File Version : 616.3 Date:04-Sep-2003
605 Added the container_item_flag also in the cursor get_item_attributes and the
606 check for Mandatory Location is added with one more condition of :
607 v_container_item_flag ='N' , so that in case of containerization, the check
608 does not hold good.
609
610 7. Aiyer -Bug# 3392528 , File Version : 618.1 Date:11-Feb-2004
611 Issue
612 =====
613 An sales order In India Localization Order Management should not be allowed to be shipped on the following
614 conditions : -
615 1. A Sales order with excise exemption types like EXCISE_EXEMPT_CERT,
616 CT2,CT3 has modvat type of taxes attached
617
618 2. A Sales order with excise exemption types like EXCISE_EXEMPT_CERT_OTH,
619 CT2_OTH does not have modvat recovery type of tax attached
620
621 3. A sales order does not have any excise exemptions specified and still
622 has Modvat Recovery type of taxes.
623
624 These check also needs to be implemented at shipping level
625
626 Solution:-
627 Created the procedure jai_om_utils_pkg.validate_excise_exemption to validate all the above conditions and called the same
628 with the relevent parameters. This has resolved the issue
629
630 Dependency Due To The Current Bug :
631 1. This trigger ja_in_wsh_dlry_au_rel_stat_trg.sql (618.1) call the
632 function ->jai_om_utils_pkg.validate_excise_exemption(618.1) and hence has dependencies
633
634 8. ssumaith - Bug#3441684 file version 618.2 : 20-feb-04
635
636 After match receipts is done for a delivery , when back-ordering is performed, the delivery
637 is back-ordered , but the matched info is not removed from the match receipts table.
638
639 The requirement is to raise an error when backordering is done without unmatching the delivery.
640
641 Dependency Due to this bug:
642 None
643
644 9. Aiyer 23/08/2004 Bug# 3844145 file version 115.2
645 Issue:-
646 The inventory trip stop concurrent program errors out for a Internal sales order with qty being matched to two different subinventories.
647
648 Reason :-
649 The existing code was considering matched quantites as per the subinventories. This should not be considered.
650 Matched quantities should be considered irrespective of the subinventories.
651
652 Solution:-
653 Modified the cursor matched_receipt_cur1 in the current trigger.Removed the group by subinventory clause from the query.
654
655 Dependency Due to this bug:
656 None
657
658
659 10. Aparajita 30/11/2004. Bug#4036241. Version#115.3
660
661 Introduced the call to centralized packaged procedure,
662 jai_cmn_utils_pkg.check_jai_exists to check if localization has been installed.
663
664
665 11. Brathod 21/03/2005. Bug#4245073. Version# 115.4, 115.5
666 Trigger modified to check whether vat type of tax exists or not.
667 If vat type of tax exits and organization, location setup does not
668 exits for vat rigme, trigger will throw an exeception.
669 Also if vat type of tax exits but the item is not vatable trigger
670 will throw an exception
671
672 12 Brathod 26/04/2005for Bug# 4299606 File Version 116.1
673 Issue:-
674 Item DFF Elimination
675 Fix:-
676 Changed the code that references attributeN (where N=1,2,3,4,5,15) of
677 mtl_system_items to corrosponding columns in JAI_INV_ITM_SETUPS
678
679 Dependency :-
680 IN60106 + 4239736 (Service Tax) + 4245089 (VAT)
681
682 13 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
683 DB Entity as required for CASE COMPLAINCE. Version 116.1
684
685 14. 13-Jun-2005 File Version: 116.2
686 Ramananda for bug#4428980. Removal of SQL LITERALs is done
687
688 15. Aiyer 22/08/2005. Bug#4566002(Forward Porting for bug 4426615), File Version# 120.3
689 Issue : -
690 After LC Matching, the user is not being allowed to Backorder the quantity.
691
692 Fix:-
693 The matched quantity = shipped quantity should be checked only in case of ship confirm release_status = 'C'.
694 This check should not happen on Backordering release_status = 'B'.
695
696 16. 30-Jan-2007 bgowrava for forward porting bug#5631784(4742259), File Version 120.4
697 Added the call to jai_ar_tcs_rep_pkg.process_transaction. for TCS related validations.
698 Dependencies Due to this bug:-
699 This bug has lots of datamodel abd specification changes.
700
701 17. 17-May-2007 CSahoo for bug#5686360 File Version 120.5
702 Forward porting of 11i BUG#5665937
703 modified the cursor c_matched_qty_cur to filter the data only by delivery_detail_id
704 and order header_id and removed the filter by order line_id
705
706 Added the cursors c_order_line and c_lc_mtch_dlry_line, to check if the order line attached
707 the delivery detail in LC matching table is same or not. If not, then the related delivery_detail
708 line is updated with the :new.source_line_id
709
710 Appended SQLERRM to the fnd_message.set_token value parameter to display the error
711
712 18. 25-May-2009 CSahoo for bug#8538431, File Version 120.9.12010000.6
713 Issue: ERROR SHOULD BE SHOWN WHEN THE ITEM THE EXCISE TAXES BUT ITEM DFF NOT GIVEN.
714 Fix: Added the cursor cur_chk_excise_exists to check if excise taxes exists or not.
715 Also added the code to check if item is not excisable and still excise taxes are
716 added, then it would raise an error.
717
718 19. 25-JUN-2009 JMEENA for bug#6731913
719 Issue: INDIA LOCAL: SHIP CONFIRM GIVES LC MATCH ERROR EVEN AFTER MATCHING IS DONE
720 Fix: The fix inlvoves a lot of modification in the code. A new procedure ARU_T4 and temp
721 table JAI_WSH_DEL_DETAILS_GT is created.
722 This table gets populated when the shipped_quantity gets updated. Then when the released Quantity changes to 'C'
723 the shipped quantity is calculated from this temp table. Then finally the table gets flushed after all the
724 records are processed.
725 Commented LC Matching code in procedure ARU_T3 as it exists in ARU_T2.
726
727 20. 27-Jul-2009 CSahoo for bug#8731696, File Version 120.9.12010000.9
728 FP 12.0 8687223 :VAT INV IS NOT GENERATING WHEN BILL TO AND SHIP TO LOCATIONS AR
729 Modified the cursor get_ship_to_org_id_cur to get the bill_to_org_id. Added the cursor
730 cur_get_bill_to_cust_id to obtain the bill_to_customer_id.
731
732 21. 14-Aug-2009 CSahoo for bug#6327274, File Version 120.9.12010000.10
733 Issue: FP :INDIA EXCISE INV GENERATION ENDS IN ERROR WHEN ED TAX NOT ATTACHED
734 Fix: Modified the code in this procedure. Added the cursor c_orgn_Null_site_info
735
736 22. 23-Sep-2009 CSahoo for bug#8924003, File Version 120.9.12010000.11
737 Issue : TAXES AND UNIT RATE COMING WRONGLY FOR THE ATO/PTO ITEM
738 Fix: forward ported the changes done for bug#6147494. Added cursors to identify
739 the ato_line_id of config item and inturn derived inventory_item_id of model item.
740 If star(Config) item is being shipped from shipping transactions form, model items
741 'Item Classification' is validated.
742
743 Future Dependencies For the release Of this Object:-
744 (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/
745 A datamodel change )
746
747 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
748 Current Version Current Bug Dependent Dependency On Files Version Author Date Remarks
749 Of File On Bug/Patchset
750 ja_in_wsh_dlry_au_rel_stat_trg.sql
751 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
752 618.1 3392528 IN60105D2 ja_in_val_exc_exmpt_f.sql 618.1 Aiyer 11-Feb-2004 This trigger calls the function ja_in_val_exc_exmpt_f.sql
753
754 115.3 4036241 4033992 ja_in_util_pkg_s.sql 115.0 115.0 Apdas 30-nov-04
755 ja_in_util_pkg_b.sql 115.0 115.0 apdas 30-11-2004
756
757 115.5 4245073 4245089 ALL VAT Objects
758
759 115.6 4299606 IN60106
760 + 4239736 (Service Tax)
761 + 4245089 (VAT)
762
763 18. 01-JAN-2008 Added by Jia Li
764 for Inclusive tax Computation
765 -------------------------------------------------------------------------------------------------------------------*/
766
767 --File.Sql.35 Cbabu
768 v_inventory_item_id :=pr_new.inventory_item_id ;
769 v_organization_id :=pr_new.organization_id ;
770 v_subinventory :=pr_new.subinventory ;
771 v_delivery_detail_id :=pr_new.delivery_detail_id ;
772 v_source_header_type_id :=pr_new.source_header_type_id ;
773 v_shipped_quantity := nvl(pr_new.shipped_quantity,0) ;
774 lv_object_name :='TRIGGER.ARAA.AFTER.JA_IN_WSH_DLRY_AU_REL_STAT_TRG' ; -- added Date 02-Feb-2007 by bgowrava for Bug bug#5631784
775 v_matched_qty := 0 ;
776 v_lc_shipped_qty := pr_new.Shipped_quantity;
777 v_line_tax_amount :=0;
778 v_sum_tax_amount :=0;
779
780
781 --if
782 -- jai_cmn_utils_pkg.check_jai_exists (p_calling_object => 'JA_IN_WSH_DLRY_AU_REL_STAT_TRG', p_org_id => pr_new.org_id)
783 -- =
784 -- FALSE
785 --then
786 -- /* India Localization funtionality is not required */
787 -- return;
788 --end if;
789
790 -- Following statements added by sriram - bug # 2689417
791 OPEN c_ja_in_so_lines_tax_amt;
792 FETCH c_ja_in_so_lines_tax_amt INTO v_line_tax_amount;
793 CLOSE c_ja_in_so_lines_tax_amt;
794
795 OPEN c_ja_in_so_tax_lines_tax_amt;
796 FETCH c_ja_in_so_tax_lines_tax_amt INTO v_sum_tax_amount;
797 CLOSE c_ja_in_so_tax_lines_tax_amt;
798
799 IF NVL(v_line_tax_amount,0) <> NVL(v_sum_tax_amount,0) THEN
800 /* RAISE_APPLICATION_ERROR(-20405,'Taxes are not matching in JAI_OM_OE_SO_LINES and JA_IN_SO_TAX_LINE FOR LINE_ID ' || pr_new.Source_line_id);
801 */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'Taxes are not matching in JAI_OM_OE_SO_LINES and JA_IN_SO_TAX_LINE FOR LINE_ID ' || pr_new.Source_line_id ; return ;
802 END IF;
803 -- ends here bug # 2689417
804
805 -- Start Of Bug #3392528,
806 /*
807 This code has been added by aiyer for the bug 3392528.
808 Call the function jai_om_utils_pkg.validate_excise_exemption to validate the different valid combination of values
809 that can exists between JAI_OM_OE_SO_LINES.excise_exempt_type and tax types associated with the
810 table JAI_OM_OE_SO_TAXES
811 */
812
813 OPEN c_chk_exc_exmpt_rule;
814 FETCH c_chk_exc_exmpt_rule INTO lv_excise_exempt_type,ln_line_number,ln_shipment_line_number,v_quantity;
815 CLOSE c_chk_exc_exmpt_rule ;
816
817 lv_ret_flag := jai_om_utils_pkg.validate_excise_exemption (
818 p_line_id => pr_new.source_line_id ,
819 p_excise_exempt_type => lv_excise_exempt_type ,
820 p_line_number => ln_line_number ,
821 p_shipment_line_number => ln_shipment_line_number ,
822 p_error_msg => lv_error_msg
823 ) ;
824 IF nvl(lv_ret_flag,'S') = 'EE' THEN
825 /* Handle all expected errors in this section. */
826 /* raise_application_error(-20406, lv_error_msg ); */ pv_return_code := jai_constants.expected_error ; pv_return_message := lv_error_msg ; return ;
827
828 ELSIF nvl(lv_ret_flag,'S') = 'UE' THEN
829 /* Handle all unexpected errors in this section. */
830 /* raise_application_error(-20406, lv_error_msg );
831 */ pv_return_code := jai_constants.expected_error ; pv_return_message := lv_error_msg ; return ;
832 END IF ;
833 -- End Of Bug #3392528
834
835 OPEN location_cursor;
836 FETCH location_cursor INTO v_location_id, v_trading_flag , v_bonded; --added lv_bonded for bug#6327274
837 CLOSE location_cursor;
838 --moved the code here for bug#6327274, start
839 OPEN trading_register_code_cur( v_organization_id ,
840 v_location_id ,
841 v_delivery_detail_id ,
842 v_source_header_type_id
843 );
844 FETCH trading_register_code_cur INTO v_trad_register_code;
845 CLOSE trading_register_code_cur;
846 --bug#6327274, end
847
848 /*moved the code to here for bug#8500697*/
849 OPEN get_item_attributes;
850 FETCH get_item_attributes INTO v_exe_flag,v_mod_flag,v_container_item_flag,lv_inventory_item_flag;
851 CLOSE get_item_attributes;
852
853
854 if nvl(pr_new.Released_status,'N') = 'C' then
855 --added for bug#8538431, start
856 OPEN cur_chk_excise_exists(cp_line_id => pr_new.source_line_id,
857 cp_header_id => pr_new.source_header_id
858 );
859 FETCH cur_chk_excise_exists INTO ln_exc_cnt;
860 CLOSE cur_chk_excise_exists ;
861
862 IF nvl (ln_exc_cnt,0) > 0 AND nvl(v_exe_flag,'N') = 'N' THEN
863 pv_return_code := jai_constants.expected_error ;
864 pv_return_message := 'An item which is not Excisable has Excise Taxes attached.
865 Please correct the item attribute or remove the Excise type of taxes' ;
866 return ;
867 END IF;
868 --bug#8538431, end
869 --added the following for bug#6327274, start
870
871
872 IF (
873 (
874 NVL(v_bonded,'N') = 'Y' OR
875 NVL(v_trading_flag,'N') = 'Y'
876 ) AND
877 NVL(v_exe_flag,'N') = 'Y' AND
878 v_trad_register_code IN (
879 '23D_DOMESTIC_EXCISE' ,
880 '23D_EXPORT_EXCISE' ,
881 'DOMESTIC_EXCISE' ,
882 'EXPORT_EXCISE' ,
883 'BOND_REG' ,
884 '23D_EXPORT_WITHOUT_EXCISE'
885 ) AND
886 nvl(ln_exc_cnt,0) = 0
887 )
888 THEN
889
890 OPEN c_orgn_null_site_info;
891 FETCH c_orgn_null_site_info INTO lv_allow_shipment_wo_excise;
892 CLOSE c_orgn_null_site_info;
893
894 IF NVL(lv_allow_shipment_wo_excise,'N') = 'Y' THEN
895 raise_application_error(-20412, 'Delivery can not be ship confirmed as Excisable Item in the shipment does not have Excise taxes' );
896 END IF;
897 END IF;
898 --bug#6327274, end
899 --csahoo for bug#8500697. Added a check on lv_inventory_item_flag in the following if clause
900 IF v_location_id IS NULL and v_container_item_flag ='N' and lv_inventory_item_flag = 'Y' then
901 /* raise_application_error(-20406, 'Mandatory India Localization Sub-inventory Setup not done for this Location from where shipment is made');
902 */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'Mandatory India Localization Sub-inventory Setup not done for this Location from where shipment is made' ; return ;
903 end if ;
904 end if;
905 -- ends here - Bug # 2928261
906
907 -- starts here additions by sriram - bug#3441684
908 if v_location_id is null then
909 v_location_id := pr_new.ship_from_location_id;
910 end if;
911 -- ends here additions by sriram - bug# 3441684
912 /*bug#8500697,end*/
913
914 /*
915 || Begin Bug#4245073
916 || Author : Brathod
917 || Date : 17-Mar-2005
918 */
919
920 IF nvl(pr_new.Released_status,'N') = 'C' THEN
921 OPEN cur_chk_vat_exists ( cp_line_id => pr_new.source_line_id,
922 cp_header_id => pr_new.source_header_id
923 );
924 FETCH cur_chk_vat_exists INTO ln_vat_cnt;
925 CLOSE cur_chk_vat_exists ;
926
927 IF nvl (ln_vat_cnt,0) > 0 THEN
928
929 OPEN cur_chk_org_loc_setup (cp_organization_id => v_organization_id ,
930 cp_location_id => v_location_id
931 );
932 FETCH cur_chk_org_loc_setup INTO ln_cnt_org_loc_setup;
933 CLOSE cur_chk_org_loc_setup ;
934
935 IF nvl(ln_cnt_org_loc_setup,0) = 0 THEN
936 /*
937 || For vat regime organization-location specific setup does not exist in
938 || jai_rgm_parties (Regime Organization Registration)
939 */
940 app_exception.raise_exception( EXCEPTION_TYPE => 'APP'
941 ,EXCEPTION_CODE => NULL
942 ,EXCEPTION_TEXT => 'Organization-Location setup does not exist at regime level'
943 );
944 END IF;
945 /* Added for bug#8924003, Start */
946 ln_ato_line_id := NULL ;
947 ln_model_item_id := NULL ;
948
949 OPEN c_ato_line_id ;
950 FETCH c_ato_line_id INTO ln_ato_line_id ;
951 CLOSE c_ato_line_id ;
952
953 IF ln_ato_line_id IS NOT NULL THEN
954 OPEN c_model_item_id(ln_ato_line_id) ;
955 FETCH c_model_item_id INTO ln_model_item_id ;
956 CLOSE c_model_item_id ;
957
958 jai_inv_items_pkg.jai_get_attrib ( p_regime_code => jai_constants.vat_regime
959 , p_organization_id => pr_new.organization_id
960 , p_inventory_item_id => ln_model_item_id
961 , p_attribute_code => jai_constants.rgm_attr_item_applicable
962 , p_attribute_value => lv_applicable
963 , p_process_flag => lv_process_flag
964 , p_process_msg => lv_process_message
965 );
966 ELSE
967 /*bug#8924003, End */
968
969 jai_inv_items_pkg.jai_get_attrib ( p_regime_code => jai_constants.vat_regime
970 , p_organization_id => pr_new.organization_id
971 , p_inventory_item_id => pr_new.inventory_item_id
972 , p_attribute_code => jai_constants.rgm_attr_item_applicable
973 , p_attribute_value => lv_applicable
974 , p_process_flag => lv_process_flag
975 , p_process_msg => lv_process_message
976 );
977 END IF;
978 IF lv_process_flag = jai_constants.successful
979 AND nvl(lv_applicable,'N') = 'N' THEN
980 /*
981 ||item is not vatable
982 */
983 app_exception.raise_exception( EXCEPTION_TYPE => 'APP'
984 ,EXCEPTION_CODE => NULL
985 ,EXCEPTION_TEXT => 'An item which is not Vatable has VAT Taxes attached.
986 Please correct the item attribute or remove the VAT type of taxes'
987 );
988 ELSIF lv_process_flag <> jai_constants.successful THEN
989 app_exception.raise_exception( EXCEPTION_TYPE => 'APP'
990 ,EXCEPTION_CODE => NULL
991 ,EXCEPTION_TEXT => substr (lv_process_message,1,999)
992 );
993
994
995 END IF;
996 END IF;
997 END IF;
998
999 /*
1000 || End Bug#4245073
1001 */
1002
1003
1004 -- SJS
1005 open c_orgn_info;
1006 fetch c_orgn_info into v_trading_flag;
1007 close c_orgn_info;
1008 -- SJS
1009 -- code to be added by AVIKUMAR Bug # 2928261
1010
1011 OPEN item_trading_cur;
1012 FETCH item_trading_cur INTO v_item_trading_flag;
1013 CLOSE item_trading_cur;
1014
1015 /*
1016 Code modified by aiyer for the bug 3032569
1017 Modified the IF statment to raise an error only when trading register code is in 23D_DOMESTIC_EXCISE and 23D_EXPORT_EXCISE.
1018 The other two trading register_codes '23D_DOM_WITHOUT_EXCISE' and '23D_EXPORT_WITHOUT_EXCISE' have been removed from this if statement
1019 as matched receipts is not relevant in this case.
1020 */
1021 -- Start of Bug #3032569
1022
1023 IF v_trad_register_code IN(
1024 '23D_DOMESTIC_EXCISE' ,
1025 '23D_EXPORT_EXCISE'
1026 )
1027 THEN
1028 -- End of Bug #3032569
1029
1030 IF nvl(v_trading_flag,'N') = 'Y' AND nvl(V_item_trading_flag,'N') = 'Y' AND NVL(v_exe_flag,'N')= 'Y' THEN
1031
1032 OPEN matched_receipt_cur1;
1033 FETCH matched_receipt_cur1 INTO v_matched_qty/*,v_matched_subinv*/;
1034 CLOSE matched_receipt_cur1;
1035 IF nvl(pr_new.Released_status,'N') = 'C' THEN
1036 IF nvl(v_shipped_quantity,0) <> nvl(v_matched_qty,0) THEN
1037 /* raise_application_error(-20401, 'Matched Quantity -- ' || TO_CHAR(NVL(v_matched_qty,0)) ||
1038 ' should be equal to Shipped Quantity -- ' || TO_CHAR(NVL(v_shipped_quantity,0))); */
1039 pv_return_code := jai_constants.expected_error ; pv_return_message := 'Matched Quantity -- ' || TO_CHAR(NVL(v_matched_qty,0)) ||
1040 ' should be equal to Shipped Quantity -- ' || TO_CHAR(NVL(v_shipped_quantity,0)); return ;
1041
1042 END IF;
1043 END IF;
1044
1045 -- starts additions by sriram - bug# 3441684
1046 if nvl(pr_new.Released_status,'N') = 'B' then
1047 -- if v_matched_qty = v_quantity and v_matched_qty is not null then
1048 if nvl(v_matched_qty,0) > 0 then
1049 /* raise_application_error (-20402, 'Please Unmatch the Delivery prior to backordering ');
1050 */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'Please Unmatch the Delivery prior to backordering ' ; return ;
1051 end if ;
1052 end if;
1053 -- ends here additions by sriram - bug# 3441684
1054 END IF;
1055 END IF;
1056
1057 /*
1058 || Start of bug 4566002
1059 || Code added by aiyer for the bug 4566002
1060 || Added the IF statment to make sure that The matched quantity = shipped quantity condition should be checked only
1061 || in case of ship confirm release_status = 'C'.
1062 || This check should not happen on Backordering release_status = 'B'.
1063 */
1064 IF nvl(pr_new.Released_status,'N') = 'C' THEN
1065 /*
1066 the following lines added by sriram - lc functionality - bug# 2165355 - 19/09/2002
1067 moved into the trigger on 27th november 2002.
1068 */
1069 OPEN c_check_lc_order;
1070 FETCH c_check_lc_order INTO v_check_lc_order;
1071 CLOSE c_check_lc_order;
1072
1073 IF NVL(v_check_lc_order,'N') = 'Y' THEN
1074 OPEN c_matched_qty_cur(pr_new.DELIVERY_DETAIL_ID) ; --Added parameter for bug#6731913
1075 FETCH c_matched_qty_cur INTO v_lc_qty_matched;
1076 CLOSE c_matched_qty_cur;
1077
1078 --Added below code by JMEENA for bug#6731913
1079 IF v_lc_qty_matched IS NULL AND pr_new.SPLIT_FROM_DELIVERY_DETAIL_ID IS NOT NULL THEN
1080 OPEN c_matched_qty_cur (pr_new.SPLIT_FROM_DELIVERY_DETAIL_ID);
1081 FETCH c_matched_qty_cur INTO v_lc_qty_matched;
1082 CLOSE c_matched_qty_cur;
1083
1084 END IF;
1085 IF NVL(v_lc_qty_matched,-999) <> NVL(v_lc_shipped_qty,-888) THEN
1086 IF pr_new.SPLIT_FROM_DELIVERY_DETAIL_ID IS NULL THEN
1087 OPEN cur_tot_shipped_quantity (pr_new.delivery_detail_id);
1088 FETCH cur_tot_shipped_quantity INTO v_tot_lc_shipped_qty;
1089 CLOSE cur_tot_shipped_quantity;
1090 ELSE
1091 OPEN cur_tot_shipped_quantity (pr_new.SPLIT_FROM_DELIVERY_DETAIL_ID);
1092 FETCH cur_tot_shipped_quantity INTO v_tot_lc_shipped_qty;
1093 CLOSE cur_tot_shipped_quantity;
1094 END IF;
1095
1096 IF NVL(v_lc_qty_matched,-999) <> NVL(v_tot_lc_shipped_qty,-888) THEN
1097
1098 /* raise_application_error(-20401, 'LC Matched Quantity -- ' || TO_CHAR(NVL(v_lc_qty_matched,0)) ||
1099 ' should be equal to Shipped Quantity -- ' || TO_CHAR(NVL(v_lc_shipped_qty,0)) || ' for LC enabled Orders'
1100 ); */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'LC Matched Quantity -- ' || TO_CHAR(NVL(v_lc_qty_matched,0)) ||
1101 ' should be equal to Shipped Quantity -- ' || TO_CHAR(NVL(v_lc_shipped_qty,0)) || ' for LC enabled Orders'
1102 ; return ;
1103 END IF;
1104 END IF;
1105
1106 Update jai_wsh_del_details_gt
1107 set processed_flag = 'Y'
1108 where delivery_detail_id = pr_new.delivery_detail_id;
1109 --End bug#6731913
1110
1111 /* Start, bug#5686360 csahoo
1112 following code is to correct the existing lc_matching order line to new split line id
1113 */
1114 open c_order_line;
1115 fetch c_order_line into r_order_line;
1116 close c_order_line;
1117
1118 ln_lc_update_cnt := -1;
1119 if r_order_line.split_from_line_id is not null then
1120 open c_lc_mtch_dlry_line;
1121 fetch c_lc_mtch_dlry_line into r_lc_mtch_dlry_line;
1122 close c_lc_mtch_dlry_line;
1123 if pr_new.source_line_id <> r_lc_mtch_dlry_line.order_line_id then
1124
1125 update JAI_OM_LC_MATCHINGS
1126 set order_line_id = pr_new.source_line_id
1127 where delivery_detail_id = v_delivery_detail_id
1128 -- and order_line_id = r_order_line.split_from_line_id
1129 and release_flag is null;
1130 ln_lc_update_cnt := sql%rowcount;
1131 end if;
1132
1133 end if;
1134
1135
1136 /* End, bug#5686360 csahoo*/
1137
1138 END IF;
1139
1140 /*
1141 ENDS HERE - CHANGES BY SRIRAM FOR LC FUNCTIONALITY - BUG # 2165355 - 19/09/2002
1142 */
1143 END IF;
1144 /*
1145 || End of bug 4566002
1146 */
1147 --Added below code by JMEENA for bug#6731913
1148 IF NVL(v_check_lc_order,'N') = 'Y' THEN
1149 select count(1) into ln_unprocessed_recs from jai_wsh_del_details_gt
1150 where processed_flag = 'N'
1151 --modified for bug#6443738
1152 AND (delivery_detail_id = nvl(pr_new.SPLIT_FROM_DELIVERY_DETAIL_ID,pr_new.delivery_detail_id )
1153 OR split_from_delivery_detail_id = nvl(pr_new.SPLIT_FROM_DELIVERY_DETAIL_ID,pr_new.delivery_detail_id));
1154
1155 IF ln_unprocessed_recs = 0 THEN
1156 delete jai_wsh_del_details_gt
1157 where processed_flag = 'Y'
1158 --modified for bug#6443738
1159 and (delivery_detail_id = nvl(pr_new.SPLIT_FROM_DELIVERY_DETAIL_ID,pr_new.delivery_detail_id )
1160 OR split_from_delivery_detail_id = nvl(pr_new.SPLIT_FROM_DELIVERY_DETAIL_ID,pr_new.delivery_detail_id));
1161
1162 END IF;
1163 END IF;
1164 --End for bug#6731913
1165
1166
1167 /****************************************************
1168 || Added by bgowrava for forward porting bug#5631784(4742259)
1169 ||TCS Validation
1170 ****************************************************/
1171 OPEN cur_get_org_hdr (cp_header_id => pr_new.source_header_id);
1172 FETCH cur_get_org_hdr INTO tab_ooh ;
1173 CLOSE cur_get_org_hdr ;
1174
1175 /*jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
1176 pv_log_msg => ' Before call to jai_ar_tcs_rep_pkg.process_transactions .'
1177 );*/ --commented by bgowrava for Bug #5631784
1178
1179 jai_ar_tcs_rep_pkg.process_transactions ( p_ooh => tab_ooh ,
1180 p_event => jai_constants.wsh_ship_confirm ,
1181 p_process_flag => lv_process_flag ,
1182 p_process_message => lv_process_message
1183 );
1184
1185 /*jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
1186 pv_log_msg => ' returned from jai_ar_tcs_rep_pkg.process_transactions .'
1187 );*/ --commented by bgowrava for Bug #5631784
1188
1189 IF lv_process_flag = jai_constants.expected_error OR ---------A2
1190 lv_process_flag = jai_constants.unexpected_error
1191 THEN
1192 /*
1193 || As Returned status is an error hence:-
1194 || Set out variables p_process_flag and p_process_message accordingly
1195 */
1196 --call to debug package
1197 /*jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
1198 pv_log_msg => ' Error : - lv_process_flag -> '||lv_process_flag||' - '||lv_process_message
1199 ); */ --commented by bgowrava for Bug #5631784
1200
1201 raise le_error;
1202 END IF; ---------A2
1203
1204
1205 /* jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
1206 pv_log_msg => ' TRIGGER ja_in_wsh_dlry_au_rel_stat_trg COMPLETED SUCCESSFUL'
1207 ); */ --commented by bgowrava for Bug #5631784
1208 jai_cmn_debug_contexts_pkg.deregister (pn_reg_id => ln_reg_id);
1209
1210
1211 EXCEPTION
1212 WHEN le_error THEN
1213 IF lv_process_flag = jai_constants.unexpected_error THEN
1214 lv_process_message := substr (lv_process_message || ' Object = ja_in_wsh_dlry_au_rel_stat_trg ', 1,1999) ;
1215 END IF;
1216
1217
1218 fnd_message.set_name (application => 'JA',
1219 name => 'JAI_ERR_DESC'
1220 );
1221
1222 fnd_message.set_token ( token => 'JAI_ERROR_DESCRIPTION',
1223 value => lv_process_message
1224 );
1225
1226
1227 app_exception.raise_exception;
1228
1229 WHEN others THEN
1230 fnd_message.set_name ( application => 'JA',
1231 name => 'JAI_ERR_DESC'
1232 );
1233
1234 fnd_message.set_token ( token => 'JAI_ERROR_DESCRIPTION',
1235 value => 'Exception Occured in ' || ' Object = ja_in_wsh_dlry_au_rel_stat_trg'||fnd_global.local_chr(10)||SQLERRM /* added SQLERRM for bug#5686360*/
1236 );
1237
1238 app_exception.raise_exception;
1239 /* end of Bug#5631784 */
1240
1241 END ARU_T2 ;
1242
1243 /*
1244 REM +======================================================================+
1245 REM NAME ARU_T3
1246 REM
1247 REM DESCRIPTION Called from trigger JAI_OM_WDD_ARIUD_T1
1248 REM
1249 REM NOTES Refers to old trigger JAI_OM_WDD_ARU_T4
1250 REM
1251 REM 02/11/2006 for Bug 5228046 by SACSETHI, File version 120.2
1252 REM Forward porting the change in 11i bug 5365523 (Additional CVD Enhancement).
1253 REM This bug has datamodel and spec changes.
1254 REM 16-Apr-2010 modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement).
1255 REM modified population logic of table jai_om_wsh_lines_all to populate column
1256 REM shippable_flag as 'Y'.
1257 REM 28-Apr-2010 modified by Allen Yang for bug 9666476
1258 REM logic of shippable_flag population is changed back. For shippable lines,
1259 REM shippable_flag will be populated with NULL.
1260 REM
1261 REM
1262 REM +======================================================================+
1263 */
1264 PROCEDURE ARU_T3 ( pr_old t_rec%type , pr_new t_rec%type , pv_action varchar2 , pv_return_code out nocopy varchar2 , pv_return_message out nocopy varchar2 ) IS
1265 v_creation_date DATE; --File.Sql.35 Cbabu :=pr_new.Creation_Date;
1266 v_created_by NUMBER; --File.Sql.35 Cbabu :=pr_new.Created_By;
1267 v_last_update_date DATE ; --File.Sql.35 Cbabu :=pr_new.Last_Update_Date;
1268 v_last_updated_by NUMBER; --File.Sql.35 Cbabu :=pr_new.Last_Updated_By;
1269 v_last_update_login NUMBER; --File.Sql.35 Cbabu :=pr_new.Last_Update_Login;
1270 v_delivery_detail_id NUMBER; --File.Sql.35 Cbabu :=pr_new.Delivery_Detail_Id;
1271 v_source_header_id NUMBER; --File.Sql.35 Cbabu :=pr_new.Source_Header_Id;
1272 v_source_line_id NUMBER; --File.Sql.35 Cbabu :=pr_new.Source_Line_Id;
1273 v_Inventory_Item_Id NUMBER; --File.Sql.35 Cbabu :=pr_new.Inventory_Item_Id;
1274 v_Requested_Quantity_Uom VARCHAR2(3); --File.Sql.35 Cbabu :=pr_new.Requested_Quantity_Uom;
1275 v_org_id NUMBER; --File.Sql.35 Cbabu :=pr_new.ORG_ID;
1276 v_customer_id NUMBER; --File.Sql.35 Cbabu :=pr_new.CUSTOMER_ID;
1277 v_source_header_type_id NUMBER ; --File.Sql.35 Cbabu :=pr_new.SOURCE_HEADER_TYPE_ID;
1278 v_subinventory VARCHAR2(10); --File.Sql.35 Cbabu :=pr_new.SUBINVENTORY;
1279 v_released_status VARCHAR2(1); --File.Sql.35 Cbabu :=pr_new.Released_Status;
1280 v_ordered_quantity NUMBER; --File.Sql.35 Cbabu := NVL(pr_new.Requested_Quantity,0);
1281 v_shipped_quantity NUMBER; --File.Sql.35 Cbabu := NVL(pr_new.Shipped_Quantity,0);
1282 v_Organization_Id NUMBER ; --File.Sql.35 Cbabu :=pr_new.Organization_Id;
1283 v_trading_flag VARCHAR2(1);
1284 v_status_code VARCHAR2(2);
1285 v_so_lines_count NUMBER;
1286 v_selling_price NUMBER;
1287 v_tax_category_id NUMBER(15);
1288 v_assessable_value NUMBER;
1289 v_excise_exempt_type VARCHAR2(60);
1290 v_excise_exempt_refno VARCHAR2(30);
1291 v_excise_exempt_date DATE;
1292 v_quantity NUMBER;
1293 v_picking_tax_lines_count NUMBER;
1294 v_tax_amount NUMBER;
1295 v_base_tax_amount NUMBER;
1296 v_func_tax_amount NUMBER;
1297 v_basic_excise_duty_amount NUMBER; --File.Sql.35 Cbabu := 0;
1298 v_add_excise_duty_amount NUMBER; --File.Sql.35 Cbabu := 0;
1299 v_oth_excise_duty_amount NUMBER; --File.Sql.35 Cbabu := 0;
1300 v_excise_amount NUMBER; --File.Sql.35 Cbabu := 0;
1301 v_left_shipped_qty NUMBER; --File.Sql.35 Cbabu := 0;
1302 v_rg23d_tax_amount NUMBER; --File.Sql.35 Cbabu := 0;
1303 v_rg23d_base_tax_amount NUMBER; --File.Sql.35 Cbabu := 0;
1304 v_rg23d_func_tax_amount NUMBER; --File.Sql.35 Cbabu := 0;
1305 v_tax_amt NUMBER; --File.Sql.35 Cbabu := 0;
1306 v_base_tax_amt NUMBER; --File.Sql.35 Cbabu := 0;
1307 v_func_tax_amt NUMBER; --File.Sql.35 Cbabu := 0;
1308 v_tot_tax_amount NUMBER;
1309 v_delivery_line_count NUMBER;
1310 v_location_id NUMBER;
1311 v_sqlerrm VARCHAR2(500);
1312 v_delivery_id NUMBER;
1313 v_ship_to_org_id NUMBER;
1314 v_bill_to_org_id NUMBER; --bug 8731696
1315 ln_bill_to_cust_id NUMBER; --added for bug#8731696
1316 v_date_confirmed DATE;
1317 counter NUMBER; --File.Sql.35 Cbabu :=0;
1318 -- Added by subbu
1319 v_raise_error_flag VARCHAR2(1);
1320 v_bonded_flag VARCHAR2(1);
1321 v_register_code VARCHAR2(30);
1322 v_fin_year NUMBER;
1323 v_old_register JAI_OM_WSH_LINES_ALL.register%TYPE;
1324 v_old_excise_invoice_no VARCHAR2(200);
1325 v_reg_type VARCHAR2(10);
1326 v_rg_type VARCHAR2(1);
1327 v_exc_invoice_no JAI_OM_WSH_LINES_ALL.excise_invoice_no%TYPE;
1328 v_tot_excise_amt NUMBER;
1329 v_tot_basic_ed_amt NUMBER;
1330 v_tot_addl_ed_amt NUMBER;
1331 v_tot_oth_ed_amt NUMBER;
1332 v_pref_rg23a NUMBER;
1333 v_pref_rg23c NUMBER;
1334 v_pref_pla NUMBER;
1335 v_ssi_unit_flag VARCHAR2(1);
1336 v_rg23a_balance NUMBER;
1337 v_rg23c_balance NUMBER;
1338 v_pla_balance NUMBER;
1339 v_order_type_id NUMBER;
1340 v_excise_flag VARCHAR2(1);
1341 v_item_class JAI_INV_ITM_SETUPS.item_class%TYPE;
1342 v_modvat_tax_rate NUMBER;
1343 v_rounding_factor NUMBER;
1344 v_exempt_bal NUMBER;
1345 v_basic_ed_amt NUMBER;
1346 v_remarks VARCHAR2(60);
1347 v_register_balance NUMBER;
1348 v_bond_tax_amount NUMBER;
1349 v_raise_exempt_flag VARCHAR2(1);
1350 v_exe_flag VARCHAR2(150);
1351 v_mod_flag VARCHAR2(150);
1352 --New Variables Declared by Nagaraj.s for Enh2415656
1353 v_output NUMBER ;-- By Nagaraj.s to get the output of the function jai_om_wsh_processing_pkg.excise_balance_check
1354 v_export_oriented_unit JAI_CMN_INVENTORY_ORGS.export_oriented_unit%TYPE;
1355 v_basic_pla_balance NUMBER;
1356 v_additional_pla_balance NUMBER;
1357 v_other_pla_balance NUMBER;
1358 v_error_message NUMBER; --This is for Capturing the Error Message
1359 v_myfilehandle UTL_FILE.FILE_TYPE; -- This is for File handling
1360 v_utl_location VARCHAR2(512);
1361 v_trip_id NUMBER;
1362 v_debug_flag VARCHAR2(1); --File.Sql.35 Cbabu := 'N'; -- Debug flag made to 'N' by arun iyer 12/03/2003 -- bug # 2828927
1363 --Ends here for Enh2415656
1364 -- end of addition by subbu
1365
1366 -- Added by Brathod for Bug#4215808
1367 ln_vat_assessable_value JAI_OM_OE_SO_LINES.VAT_ASSESSABLE_VALUE%TYPE;
1368 lv_vat_exemption_flag JAI_OM_WSH_LINES_ALL.VAT_EXEMPTION_FLAG%TYPE;
1369 lv_vat_exemption_type JAI_OM_WSH_LINES_ALL.VAT_EXEMPTION_TYPE%TYPE;
1370 ld_vat_exemption_date JAI_OM_WSH_LINES_ALL.VAT_EXEMPTION_DATE%TYPE;
1371 lv_vat_exemption_refno JAI_OM_WSH_LINES_ALL.VAT_EXEMPTION_REFNO%TYPE;
1372 -- End of Bug#4215808
1373
1374
1375 CURSOR Get_Status_Cur IS
1376 SELECT A.delivery_id,
1377 A.confirm_date,
1378 A.status_code
1379 FROM Wsh_Delivery_Assignments B,
1380 Wsh_New_deliveries A
1381 WHERE B.Delivery_Id = A.Delivery_Id
1382 AND B.Delivery_Detail_id = v_delivery_detail_id;
1383 --Added by GSRI for BUG 2283066
1384 /* Commented by Brathod for Bug# 4299606 (DFF Elimination)*/
1385 /* CURSOR get_item_attributes IS
1386 SELECT attribute1 ,attribute2
1387 FROM mtl_system_items
1388 WHERE inventory_item_id = v_Inventory_Item_Id
1389 AND organization_id = v_organization_id;
1390 End of Bug# 4299606 */
1391 /* Added by Brathod for Bug# 4299606 */
1392 CURSOR get_item_attributes IS
1393 SELECT excise_flag, modvat_flag
1394 FROM JAI_INV_ITM_SETUPS
1395 WHERE inventory_item_id = v_Inventory_Item_Id
1396 AND organization_id = v_organization_id;
1397 /* End of Bug# 4299606 */
1398
1399 -- End of Addition
1400 CURSOR Get_So_Lines_Count_Cur IS
1401 SELECT COUNT(*)
1402 FROM JAI_OM_OE_SO_LINES
1403 WHERE Line_id = v_source_line_id;
1404
1405 CURSOR Get_So_Lines_Details_Cur IS
1406 SELECT NVL(Selling_Price,0),
1407 NVL(Quantity,0),
1408 NVL(Tax_Category_Id,0),
1409 NVL(Assessable_Value,0),
1410 NVL(vat_assessable_value,0),
1411 Excise_Exempt_Type,
1412 Excise_Exempt_Refno,
1413 Excise_Exempt_Date,
1414 -- Added by Brathod for Bug#4215808
1415 vat_exemption_flag,
1416 vat_exemption_type,
1417 vat_exemption_date,
1418 vat_exemption_refno
1419 -- End of Bug#4215808
1420 FROM JAI_OM_OE_SO_LINES
1421 WHERE Line_id = v_source_line_id;
1422 /*
1423 Code changed by aiyer for the bug #3139718.
1424 Added the cursor to details required for currency conversion.
1425 */
1426
1427 --Start of #3139718
1428 /* bug 5243532. Added by Lakshmi Gopalsami
1429 Removed the reference to cursor sob_cur
1430 as this is not used anywhere.
1431 */
1432
1433
1434 CURSOR get_conv_detail_cur
1435 IS
1436 SELECT
1437 transactional_curr_code ,
1438 conversion_type_code ,
1439 conversion_rate ,
1440 nvl(b.actual_shipment_date,sysdate) actual_shipment_date
1441 FROM
1442 oe_order_headers_all a ,
1443 oe_order_lines_all b
1444 WHERE
1445 a.header_id = b.header_id AND
1446 b.line_id = v_source_line_id AND
1447 a.header_id = v_source_header_id ;
1448
1449 v_currency_code GL_SETS_OF_BOOKS.CURRENCY_CODE%TYPE ;
1450 v_set_of_books_id HR_OPERATING_UNITS.SET_OF_BOOKS_ID%TYPE ;
1451 v_conv_type_code oe_order_headers_all.conversion_type_code%TYPE;
1452 v_conv_rate NUMBER;
1453 v_conv_date DATE;
1454
1455 v_curr_conv_rate NUMBER;
1456
1457 --End of #3139718
1458
1459 CURSOR Get_Tax_Lines_Details_Cur IS
1460 SELECT b.Tax_Type,
1461 NVL(b.Rounding_Factor,2) Rounding_Factor, --changed the rounding factor to 2 if it is null, JMEENA bug#6280735 (FP 6164922)
1462 A.Tax_Line_No,
1463 A.Precedence_1,
1464 A.Precedence_2,
1465 A.Precedence_3,
1466 A.Precedence_4,
1467 A.Precedence_5,
1468 A.Precedence_6, -- CHANGE ON 02/11/2006 BY SACSETHI FOR BUG 5228046
1469 A.Precedence_7,
1470 A.Precedence_8,
1471 A.Precedence_9,
1472 A.Precedence_10,
1473 A.Tax_Id,
1474 A.Tax_Rate,
1475 A.Qty_Rate,
1476 A.Uom,
1477 A.Tax_Amount,
1478 A.Base_Tax_Amount,
1479 A.Func_Tax_Amount
1480 FROM JAI_OM_OE_SO_TAXES A,
1481 JAI_CMN_TAXES_ALL b
1482 WHERE Line_id = v_source_line_id
1483 AND A.Tax_Id = b.Tax_Id
1484 ORDER BY A.Tax_Line_No;
1485 CURSOR Pick_Tax_Line_Count_Cur(P_Tax_Id NUMBER) IS
1486 SELECT COUNT(*)
1487 FROM JAI_OM_WSH_LINE_TAXES
1488 WHERE Delivery_Detail_Id = v_delivery_detail_id
1489 AND Tax_Id = P_Tax_Id;
1490 CURSOR Get_Tot_Tax_Amount_Cur IS
1491 SELECT SUM(A.Tax_Amount)
1492 FROM JAI_OM_WSH_LINE_TAXES A,
1493 JAI_CMN_TAXES_ALL b
1494 WHERE A.Delivery_Detail_Id = v_delivery_detail_id
1495 AND b.Tax_Id = A.Tax_Id
1496 AND b.Tax_Type <> 'TDS';
1497 CURSOR Get_Delivery_Line_Count_Cur IS
1498 SELECT COUNT(*)
1499 FROM JAI_OM_WSH_LINES_ALL
1500 WHERE Delivery_Detail_Id = v_delivery_detail_id
1501 AND Delivery_id = v_delivery_id; /* Added delivery_id by mmurtuza for bug 13885787*/
1502 CURSOR Location_Cursor IS
1503 SELECT NVL(Location_id,0),
1504 trading
1505 FROM JAI_INV_SUBINV_DTLS
1506 WHERE Sub_Inventory_Name = v_subinventory
1507 AND organization_id = v_organization_id;
1508 CURSOR get_ship_to_org_id_cur( p_line_id NUMBER) IS
1509 SELECT ship_to_org_id, invoice_to_org_id --added invoice_to_org_id for bug#8731696
1510 FROM Oe_order_lines_all
1511 WHERE line_id = p_line_id;
1512
1513 --added the cursor for bug#8731696
1514 Cursor cur_get_bill_to_cust_id (cp_site_use_id IN NUMBER)
1515 IS
1516 SELECT customer_id
1517 FROM oe_invoice_to_orgs_v
1518 WHERE site_use_id = cp_site_use_id;
1519
1520 CURSOR rg23d_amount_cur(p_tax_id NUMBER) IS
1521 SELECT nvl(sum(tax_amount),0) ,
1522 nvl(sum(base_tax_amount),0),
1523 nvl(sum(func_tax_amount),0)
1524 FROM
1525 JAI_CMN_MATCH_TAXES
1526 WHERE
1527 ref_line_id = v_delivery_detail_id AND
1528 receipt_id IS NOT NULL AND
1529 tax_id = p_tax_id;
1530
1531 CURSOR ed_cur (p_tax_type VARCHAR2)IS
1532 -- SELECT SUM(NVL(A.func_tax_amount,0))
1533 SELECT
1534 nvl(sum(a.func_tax_amount),0) -- cbabu for Bug# 2736191
1535 FROM
1536 JAI_CMN_MATCH_TAXES a,
1537 JAI_CMN_TAXES_ALL b
1538 WHERE
1539 a.tax_id = b.tax_id AND
1540 b.tax_type = p_tax_type AND
1541 A.ref_line_id = v_delivery_detail_id AND
1542 A.receipt_id IS NOT NULL;
1543
1544 CURSOR ja_in_so_picking_exc_check(p_delivery_id NUMBER) IS
1545 SELECT DISTINCT register
1546 FROM JAI_OM_WSH_LINES_ALL
1547 WHERE delivery_id = p_delivery_id;
1548
1549 --2001/07/10 Anuradha Parthasarathy
1550 CURSOR item_trading_cur IS
1551 SELECT
1552 item_trading_flag
1553 FROM
1554 JAI_INV_ITM_SETUPS
1555 WHERE
1556 organization_id = v_organization_id AND
1557 inventory_item_id = v_inventory_item_id;
1558
1559 v_item_trading_flag VARCHAR2(1);
1560
1561 --2001/10/03 Anuradha Parthasarathy
1562 CURSOR uom_code IS
1563 SELECT order_quantity_uom
1564 FROM oe_order_lines_all
1565 WHERE line_id = v_source_line_id;
1566 v_order_quantity_uom VARCHAR2(3);
1567 v_conversion_rate NUMBER; --File.Sql.35 Cbabu := 0;
1568 --2001/12/20
1569 CURSOR Trading_register_code_cur(
1570 p_organization_id NUMBER,
1571 p_location_id NUMBER,
1572 p_delivery_detail_id NUMBER,
1573 p_order_type_id NUMBER
1574 ) IS
1575 SELECT
1576 a.register_code
1577 FROM
1578 JAI_OM_OE_BOND_REG_HDRS a,
1579 JAI_OM_OE_BOND_REG_DTLS b
1580 WHERE
1581 A.organization_id = p_organization_id AND
1582 A.location_id = p_location_id AND
1583 A.register_id = b.register_id AND
1584 b.order_flag = 'Y' AND
1585 b.order_type_id = p_order_type_id AND
1586 A.register_code LIKE '23D%';
1587
1588 v_trad_register_code VARCHAR2(30);
1589
1590 /*
1591 Code added by aiyer for the bug 3844145.
1592 Removed the group by subinventory clause from the query. The matched qty should be considered irrespective of the
1593 subinventory
1594 */
1595
1596 CURSOR matched_receipt_cur1 IS
1597 SELECT
1598
1599 sum(a.quantity_applied) quantity_applied
1600 FROM
1601 JAI_CMN_MATCH_RECEIPTS a
1602 WHERE
1603 a.ref_line_id = v_delivery_detail_id;
1604
1605 v_matched_qty NUMBER; --File.Sql.35 Cbabu := 0;
1606 --------------------------------------------------------------
1607 -- start of cursors for checking RG balances by subbu
1608 CURSOR get_item_dtls (p_organization_id NUMBER,p_item_id NUMBER) IS
1609 SELECT
1610 excise_flag,
1611 item_class
1612 FROM
1613 JAI_INV_ITM_SETUPS
1614 WHERE
1615 organization_id = p_organization_id AND
1616 inventory_item_id = p_item_id;
1617
1618 CURSOR bonded_cur(p_organization_id NUMBER, p_subinventory VARCHAR2) IS
1619 SELECT NVL(A.bonded,'Y') bonded
1620 FROM JAI_INV_SUBINV_DTLS A
1621 WHERE A.sub_inventory_name = p_subinventory
1622 AND A.organization_id = p_organization_id;
1623 CURSOR register_code_cur(p_organization_id NUMBER, p_location_id NUMBER, p_order_type_id NUMBER) IS
1624 SELECT A.register_code
1625 FROM JAI_OM_OE_BOND_REG_HDRS A, JAI_OM_OE_BOND_REG_DTLS b
1626 WHERE A.organization_id = p_organization_id
1627 AND A.location_id = p_location_id
1628 AND A.register_id = b.register_id
1629 AND b.order_flag = 'Y'
1630 AND b.order_type_id = p_order_type_id ;
1631 CURSOR fin_year_cur(p_organization_id IN NUMBER) IS
1632 SELECT MAX(A.fin_year)
1633 FROM JAI_CMN_FIN_YEARS A
1634 WHERE organization_id = p_organization_id
1635 AND fin_active_flag = 'Y';
1636 CURSOR pref_cur(p_organization_id NUMBER, p_location_id NUMBER)IS
1637 --This is included in the select by Nagaraj.s for Enh2415656
1638 SELECT pref_rg23a, pref_rg23c, pref_pla,
1639 NVL(Export_oriented_unit ,'N')
1640 FROM JAI_CMN_INVENTORY_ORGS
1641 WHERE organization_id = p_organization_id
1642 AND location_id = p_location_id ;
1643 --This is included in the select by Nagaraj.s for Enh2415656
1644 CURSOR rg_bal_cur(p_organization_id NUMBER, p_location_id NUMBER)IS
1645 SELECT NVL(rg23a_balance,0) rg23a_balance ,NVL(rg23c_balance,0) rg23c_balance,NVL(pla_balance,0) pla_balance,
1646 NVL(basic_pla_balance,0) basic_pla_balance,
1647 NVL(additional_pla_balance,0) additional_pla_balance,
1648 NVL(other_pla_balance,0) other_pla_balance
1649 FROM JAI_CMN_RG_BALANCES
1650 WHERE organization_id = p_organization_id
1651 AND location_id = p_location_id ;
1652 CURSOR ssi_unit_flag_cur(p_organization_id NUMBER, p_location_id NUMBER) IS
1653 SELECT ssi_unit_flag
1654 FROM JAI_CMN_INVENTORY_ORGS
1655 WHERE organization_id = p_organization_id
1656 AND location_id = p_location_id;
1657 CURSOR register_balance_cur(p_organization_id NUMBER,p_location_id NUMBER) IS
1658 SELECT register_balance
1659 FROM JAI_OM_OE_BOND_TRXS
1660 WHERE transaction_id = (SELECT MAX(A.transaction_id)
1661 FROM JAI_OM_OE_BOND_TRXS A, JAI_OM_OE_BOND_REG_HDRS B
1662 WHERE A.register_id = B.register_id
1663 AND B.organization_id = p_organization_id
1664 AND B.location_id = p_location_id );
1665 CURSOR Get_Tax_Lines_Details_Cur1 IS
1666 SELECT A.Tax_Rate, NVL(b.Rounding_Factor,0) Rounding_Factor
1667 FROM JAI_OM_OE_SO_TAXES A, JAI_CMN_TAXES_ALL b
1668 WHERE Line_id = v_source_line_id
1669 AND A.Tax_Id = b.Tax_Id
1670 AND b.tax_type = 'Modvat Recovery'
1671 ORDER BY A.Tax_Line_No;
1672 CURSOR for_modvat_percentage(p_organization_id NUMBER, p_location_id NUMBER) IS
1673 SELECT MODVAT_REVERSE_PERCENT
1674 FROM JAI_CMN_INVENTORY_ORGS
1675 WHERE organization_id = p_organization_id
1676 AND ( location_id = p_location_id
1677 OR
1678 location_id is NULL AND p_location_id is NULL); /* Modified by Ramananda for removal of SQL LITERALs */
1679 --AND NVL(location_id,0) = NVL(p_location_id,0);
1680
1681 -- end of cursors for checking RG balances by subbu
1682 -- following cursors added by sriram bug# 2165355
1683
1684 CURSOR C_CHECK_LC_ORDER IS
1685 SELECT LC_FLAG
1686 FROM JAI_OM_OE_SO_LINES
1687 WHERE LC_FLAG = 'Y' AND
1688 HEADER_ID = pr_new.SOURCE_HEADER_ID;
1689
1690 CURSOR C_MATCHED_QTY_CUR IS
1691 SELECT SUM(QTY_MATCHED)
1692 FROM JAI_OM_LC_MATCHINGS
1693 WHERE ORDER_HEADER_ID = pr_new.SOURCE_HEADER_ID
1694 -- AND ORDER_LINE_ID = pr_new.SOURCE_LINE_ID --commented by csahoo for bug#5680459
1695 AND delivery_detail_id = pr_new.delivery_detail_id -- bug# 3541960
1696 AND RELEASE_FLAG IS NULL;
1697 v_check_lc_order VARCHAR2(1);
1698 v_lc_qty_matched NUMBER;
1699 v_lc_shipped_qty NUMBER; --File.Sql.35 Cbabu := pr_new.Shipped_quantity;
1700 -- ends here additions bug sriram bug# 2165355
1701
1702 /* Start, csahoo for bug#5680459
1703 following code is to correct the existing lc_matching order line to new split line id
1704 */
1705 cursor c_order_line is
1706 select split_from_line_id --, split_by
1707 from oe_order_lines_all
1708 where line_id = pr_new.source_line_id;
1709
1710 cursor c_lc_mtch_dlry_line is
1711 select order_line_id
1712 from JAI_OM_LC_MATCHINGS
1713 where delivery_detail_id = pr_new.delivery_detail_id;
1714 r_order_line c_order_line%rowtype;
1715 r_lc_mtch_dlry_line c_lc_mtch_dlry_line%rowtype;
1716 ln_lc_update_cnt number;
1717 /* end csahoo for bug#5680459 */
1718
1719
1720 -- start additions by sriram - bug # 3021588
1721 v_asst_register_id Number;
1722 v_reg_exp_date Date;
1723 v_lou_flag Varchar2(1);
1724
1725 CURSOR c_cess_amount (cp_delivery_id JAI_OM_WSH_LINES_ALL.DELIVERY_ID%TYPE) IS
1726 SELECT nvl(sum(jsptl.func_tax_amount),0) tax_amount
1727 FROM JAI_OM_WSH_LINE_TAXES jsptl ,
1728 JAI_CMN_TAXES_ALL jtc
1729 WHERE jtc.tax_id = jsptl.tax_id
1730 AND delivery_detail_id in
1731 (SELECT delivery_detail_id
1732 FROM JAI_OM_WSH_LINES_ALL
1733 WHERE delivery_id = cp_delivery_id
1734 )
1735 AND upper(jtc.tax_type) in (upper(jai_constants.tax_type_cvd_edu_cess), upper(jai_constants.tax_type_exc_edu_cess));
1736
1737
1738 -- start, Bgowrava for forward porting bug#5989740
1739
1740 ln_sh_cess_amount JAI_CMN_RG_OTHERS.DEBIT%TYPE;
1741 /* Cursor is responsible to get secondary and higher cess */
1742
1743 CURSOR c_sh_cess_amount (cp_delivery_id JAI_OM_WSH_LINES_ALL.DELIVERY_ID%TYPE) IS
1744 SELECT nvl(sum(jsptl.func_tax_amount),0) tax_amount
1745 FROM JAI_OM_WSH_LINE_TAXES jsptl ,
1746 JAI_CMN_TAXES_ALL jtc
1747 WHERE jtc.tax_id = jsptl.tax_id
1748 AND delivery_detail_id in
1749 (SELECT delivery_detail_id
1750 FROM JAI_OM_WSH_LINES_ALL
1751 WHERE delivery_id = cp_delivery_id
1752 )
1753 AND upper(jtc.tax_type) in (upper(jai_constants.tax_type_sh_exc_edu_cess),
1754 upper(jai_constants.tax_type_sh_cvd_edu_cess)
1755 );
1756 -- end, Bgowrava for forward porting bug#5989740
1757
1758
1759 ln_cess_amount JAI_CMN_RG_OTHERS.DEBIT%TYPE;
1760 lv_process_flag VARCHAR2(5);
1761 lv_process_message VARCHAR2(1996);
1762
1763
1764 -- Added by brathod for Bug#4215808
1765
1766 ln_vat_cnt NUMBER DEFAULT 0 ;
1767 ln_vat_proc_cnt NUMBER DEFAULT 0 ;
1768 ln_regime_id JAI_RGM_ORG_REGNS_V.REGIME_ID%TYPE;
1769 lv_regns_num JAI_RGM_ORG_REGNS_V.ATTRIBUTE_VALUE%TYPE;
1770
1771 CURSOR cur_chk_vat_exists (cp_del_det_id JAI_OM_WSH_LINES_ALL.DELIVERY_DETAIL_ID%TYPE)
1772 IS
1773 SELECT 1
1774 FROM JAI_OM_WSH_LINE_TAXES jsptl,
1775 JAI_CMN_TAXES_ALL jtc
1776 , jai_regime_tax_types_v tax_types
1777 WHERE jsptl.delivery_detail_id = cp_del_det_id
1778 AND jtc.tax_id = jsptl.tax_id
1779 AND jtc.tax_type = tax_types.tax_type
1780 AND tax_types.regime_code = jai_constants.vat_regime;
1781
1782 CURSOR cur_get_regime_info (cp_organization_id JAI_RGM_ORG_REGNS_V.ORGANIZATION_ID%TYPE ,
1783 cp_location_id JAI_RGM_ORG_REGNS_V.LOCATION_ID%TYPE
1784 )
1785 IS
1786 SELECT regime_id,
1787 attribute_value
1788 FROM JAI_RGM_ORG_REGNS_V orrg
1789 WHERE orrg.organization_id = cp_organization_id
1790 AND orrg.location_id = cp_location_id
1791 AND attribute_type_code = jai_constants.rgm_attr_type_code_primary
1792 AND attribute_code = jai_constants.attr_code_regn_no
1793 AND regime_code = jai_constants.vat_regime;
1794
1795 CURSOR cur_chk_vat_proc_entry (cp_delivery_id JAI_OM_WSH_LINES_ALL.DELIVERY_ID%TYPE)
1796 IS
1797 SELECT 1
1798 FROM JAI_RGM_INVOICE_GEN_T
1799 WHERE delivery_id = cp_delivery_id;
1800
1801 -- End of Bug#4215808
1802
1803 /*
1804 || Added by csahoo for bug#5680459
1805 || Check if only 'VAT REVERSAL' tax type is present in JAI_OM_WSH_LINE_TAXES
1806 */
1807 CURSOR c_chk_vat_reversal (cp_del_det_id JAI_OM_WSH_LINES_ALL.delivery_detail_id%TYPE,
1808 cp_tax_type JAI_CMN_TAXES_ALL.tax_type%TYPE )
1809 IS
1810 SELECT 1
1811 FROM JAI_OM_WSH_LINE_TAXES jsptl,
1812 JAI_CMN_TAXES_ALL jtc
1813 WHERE jsptl.delivery_detail_id = cp_del_det_id
1814 AND jtc.tax_id = jsptl.tax_id
1815 AND jtc.tax_type = cp_tax_type ;
1816
1817 ln_vat_reversal_exists NUMBER ;
1818 lv_vat_reversal VARCHAR2(100);
1819 lv_vat_invoice_no VARCHAR2(10);
1820 lv_vat_inv_gen_status VARCHAR2(10);
1821 --bug#5680459, ends
1822
1823
1824 /** Bgowrava for forward porting Bug#5631784 */
1825 ln_tcs_exists number;
1826 --lv_process_flag jai_constants.successful%type;
1827 ln_threshold_tax_cat_id jai_ap_tds_thhold_taxes.tax_category_id%type;
1828 ln_tcs_regime_id JAI_RGM_DEFINITIONS.regime_id%type;
1829 ln_threshold_slab_id jai_ap_tds_thhold_slabs.threshold_slab_id%type;
1830 ln_last_line_no number;
1831 ln_base_line_no number;
1832 lv_context varchar2(240);
1833 ln_reg_id number;
1834
1835 CURSOR C_GET_REGIME_ID (CP_REGIME_CODE JAI_RGM_DEFINITIONS.REGIME_CODE%TYPE)
1836 IS
1837 SELECT REGIME_ID
1838 FROM JAI_RGM_DEFINITIONS
1839 WHERE REGIME_CODE = CP_REGIME_CODE;
1840
1841 /** Check if taxes with taxType as defined in the regime setup exists for given regime code */
1842 CURSOR C_CHK_RGM_TAX_EXISTS ( CP_REGIME_CODE JAI_RGM_DEFINITIONS.REGIME_CODE%TYPE
1843 , CP_DELIVERY_DETAIL_ID JAI_OM_WSH_LINE_TAXES.DELIVERY_DETAIL_ID%TYPE
1844 )
1845 IS
1846 SELECT COUNT(1)
1847 FROM JAI_REGIME_TAX_TYPES_V JRTTV
1848 , JAI_OM_WSH_LINE_TAXES JSPT
1849 , JAI_CMN_TAXES_ALL JTC
1850 WHERE JTC.TAX_ID = JSPT.TAX_ID
1851 AND JTC.TAX_TYPE = JRTTV.TAX_TYPE
1852 AND REGIME_CODE = CP_REGIME_CODE
1853 AND JSPT.DELIVERY_DETAIL_ID = CP_DELIVERY_DETAIL_ID;
1854
1855 /** End of Bug#5631784 **/
1856
1857
1858
1859 /* Bug 5243532. Added by Lakshmi Gopalsami
1860 Implemented caching logic.
1861 */
1862 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
1863
1864
1865 BEGIN
1866 pv_return_code := jai_constants.successful ;
1867 /*------------------------------------------------------------------------------------------
1868 FILENAME: JA_IN_WSH_DLRY_DTLS_AU_TRG.sql CHANGE HISTORY:
1869 SlNo. DD/MM/YYYY Author and Details of Modifications
1870 ------------------------------------------------------------------------------------------
1871 1 19/12/2000 MANOHAR MISHRA
1872 nvl function added to v_location_id
1873
1874 2 31/05/2001 Anuradha Parthasarathy
1875 Code added to return in case of qty shipped is zero
1876
1877 3 08/06/2001 Anuradha Parthasarathy
1878 Code added for correct tax insertions in case of trading orders.
1879
1880 4 23/06/2001 Anuradha Parthasarathy
1881 nvl function to v_location_id commented,because this is a necessary setup.
1882
1883 5 10/07/2001 Anuradha Parthasarathy
1884 Taxes should be picked from ja_in_rg23d_shipping_taxes only when a trading item is transacted
1885 from a trading subinventory.
1886
1887 6 03/10/2001 Anuradha Parthasarathy
1888 Tax Calculation as per the Inventory Uom
1889
1890 7 08/05/2002 Sriram SJ Bug # 2330055
1891 Insert of non zero selling price and assessable value in JAI_OM_WSH_LINES_ALL even though
1892 tax lines are not there.
1893
1894 8 03/07/2002 Nagaraj.s - For Enh#2415656.
1895 Cursors pref_cur - Incorporated v_export_oriented_unit also in the select clause
1896 RG Bal Cur- Incorporated basic,additional,other pla balance also in the select clause.
1897 Functions jai_om_wsh_processing_pkg.excise_balance_check - for preference checks in case of EOU and Non-EOU for total excise amount
1898 jai_om_wsh_pkg.get_excise_register_with_bal - for preference checks in case of EOU and Non-EOU
1899 for exempted amount.
1900 Before sending this patch it has to be taken care that, the alter scripts,functions should also
1901 accompany the patch otherwise the patch would certainly fail.
1902
1903 9 24/08/2002 Sriram SJ bug # 2531013
1904 Made the changes , to take care as to when the backordering functionality should be allowed.
1905
1906 10 01/11/2002 Sriram - Bug # 2165355
1907 LC Functionality. Added the Lc checks.
1908
1909 11 13/12/2002 Sriram - Bug # 2689417 - File Version 615.3
1910 Changed the WHEN clause in the trigger because after the ONT Patchset 'G'
1911 OM interface executes first and then Inventory interface . If the inventory interface
1912 errors out due to some reason other than Localization issue , then the line information
1913 is carried over to AR , but the taxes are not present in Shiping Localization tables ,
1914 causing lot of Data fix requirements.
1915
1916 12 09/01/2003 cbabu for Bug# 2736191 - File Version# 615.4
1917 For trading functionality JAI_OM_WSH_LINES_ALL.excise_amount is getting populated NULL always, if one
1918 of 'Excise', 'Addl. Excise', 'Other Excise' tax components is missing. ED_CUR cursor is modified to fetch
1919 0 if query does not retreive any data.
1920
1921 13. 12/03/2003 Arun Iyer Bug # 2828927 615.5
1922 v_Debug_flag = 'Y' was causing problems owing to reasons such as /usr/tmp folder etc.
1923 Hence making it 'N' .
1924
1925 14. 24/07/2003 Aiyer Bug #3032569, File Version 616.1
1926
1927 Issue:-
1928 The trigger validates that the match receipt functionality is performed in scenario's where an order is associated
1929 to bond registers - 'Trading Domestic Without Excise' and 'Export Without Excise'.
1930 This check is not required. The match receipts only needs to be done for
1931 'Trading Domestic With Excise' and 'Export with Excise' type of Scenario's
1932
1933 Solution:-
1934 Modified the IF statment to raise an error only when trading register code is in 23D_DOMESTIC_EXCISE and
1935 23D_EXPORT_EXCISE.
1936 The other two trading register_codes '23D_DOM_WITHOUT_EXCISE' and '23D_EXPORT_WITHOUT_EXCISE' have
1937 been removed as matched receipts is not relevant in this case.
1938
1939 Fix of bug #2988829 along with the current bug
1940 ----------------------------------------------
1941 Issue:-
1942 Initial code used to check that if the organization is a Trading organization,item is tradable and excisable
1943 then used to assume that a match receipt has been done and get the sum (tax_amount), sum(base_tax_amount),
1944 sum(func_tax_amount) for a delivery_detail_id and tax_id from the JAI_CMN_MATCH_TAXES table and
1945 populate this into the tax_amount, base_tax_amount and func_tax_amount columns of the table
1946 JAI_OM_WSH_LINE_TAXES.
1947
1948 This approach used to fail, as many a times a record never used to exists in the JAI_CMN_MATCH_TAXES table
1949 for the delivery_detail_id and tax_id even though the organization is declared as a trading organization,
1950 Item is tradable and excisable.
1951
1952 This happens in scenario's where an order assigned to a Bond register_type is either 'Trading domestic without excise' or
1953 'Export Without Excise', the match receipt functionaity would not be performed by client and consequently no
1954 data gets populated in JAI_CMN_MATCH_TAXES.
1955
1956 Solution: -
1957 Added the additional check :-
1958 v_trad_register_code IN ( '23D_DOMESTIC_EXCISE','23D_EXPORT_EXCISE') .
1959 is all the above cases.
1960 With this the check becomes that if a organization is trdable, item is tradable , excisable and the order
1961 associated to the bond register is Trading Domestic With Excise and Export With Excise only then
1962 it can be assumed that there would be data in table JAI_CMN_MATCH_TAXES (Match receipts has been performed).
1963 In such a case take the tax_amoutn ,base_tax_amount and func_tax_amoutn for the above table.
1964 Else take it from JAI_OM_OE_SO_LINES and JAI_OM_OE_SO_TAXES table.
1965
1966
1967 Dependency Introduced Due to this Bug : -
1968 None
1969
1970 15. 22/08/2003 Bug # 3021588 (Bond Register Enhancement) Version 616.2
1971
1972 For Multiple Bond Register Enhancement,
1973 Instead of using the cursors for fetching the register associated with the order type , a call has been made to the procedures
1974 of the jai_cmn_bond_register_pkg package. There enhancement has created dependency because of the
1975 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.
1976
1977 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
1978
1979 Provision for letter of undertaking has been incorporated. Letter of undetaking is also a type of bond register but without validation for the balances.
1980 This has been done by checking if the LOU_FLAG is 'Y' in the JAI_OM_OE_BOND_REG_HDRS table for the
1981 associated register id , if yes , then validation is only based on bond expiry date .
1982
1983 This fix has introduced huge dependency . All future changes in this object should have this bug as a prereq
1984
1985
1986 16.17/09/2003 Bug #3148621 File Version : 616.3
1987
1988 For a trading organization , none of the taxes were proportioned based on the split quantity ,
1989 The tax amount in the sales order is applied as it is to each of the split portions causing these tax amounts to be duplicated many times.
1990
1991 As per the requirement , all taxes other than excisable taxes need to be proportioned based on split quantity , as excise taxes will be picked from receipt against which this shipment is done.
1992
1993 This issue has been resolved by adding a check that tax amount should be proportionally calculated based on quantity shipped for all non-excise taxes.
1994
1995
1996 17.24/09/2003 Aiyer Bug #3139718 File Version : 616.4
1997
1998 Added the cursor get_conv_detail_cur to get the actual_shipment_date from oe_order_lines_all instead of the
1999 conversion_date from oe_order_headers_all.
2000 As a sales order shipment date can be different from its creation date, hence the conversion rate
2001 applicable on the date of shipment should be considerd for all processing rather than the creation
2002 date of the Sales order.
2003 Added the procedure call jai_cmn_utils_pkg.currency_conversion to calculated the currency conversion rate.
2004 Also changed the population logic of v_func_tax_amt variable, such that
2005 during shipping the functional tax amount gets recalculated from the tax_amount column in JAI_OM_OE_SO_TAXES
2006 and hence logic is ->
2007 v_func_tax_amt = v_tax_amt * nvl((v_curr_conv_rate ,1)
2008
2009 Fix Of Bug#3158282:-
2010 Issue:-
2011 In case of non INR type of transactions with Excise type of tax the rounding precision should be maintained at 0
2012 and in all other cases the rounding factor should be picked up from JAI_CMN_TAXES_ALL.
2013
2014 Solution:-
2015 Modified the rounding factor to reflect the above scenario. variable v_func_tax_amount gets
2016 rounded of to zero in case of non INR type of transactions ( v_curr_conv_rate <> 1) with Excise type of tax
2017 and for all other cases rounding precession is picked up from JAI_CMN_TAXES_ALL table.
2018
2019 Dependency Introduced Due to this Bug : -
2020 None
2021
2022 18. 21/01/2004 ssumaith bug # 3390174 618.1
2023
2024 Issue :- In a trading organzation , when match receipts is done, taxes which are
2025 dependent on adhoc excise taxes was not getting recalculated based on the
2026 value of the excise tax retreived from matching.
2027
2028 This issue has been resolved by commenting out the condition which is
2029 documented by this bug number.
2030
2031 19.31/03/2004 ssumaith - bug# 3541960 file version 619.1
2032
2033 Issue :- when an lc enabled order is split and shipped , interface trip stop was going into error.
2034 The reason for this error is because for a delivery , the sum of matched quantity in the
2035 JAI_OM_LC_MATCHINGS table is compared to the quantity shipped for the delivery detail id being processed.
2036
2037 Solution :- This issue is solved by comparing the delivery_detail_id also when getting the matched quantity.
2038 By including the delivery detail in the where clause , ensuring that in case of split orders
2039 also the shipment can go through without any errors.
2040
2041 code change has been done in the cursor - c_matched_qty_cur
2042
2043
2044 20.04/05/2004 ssumaith - bug# 3609172 file version 619.2
2045
2046 issue :- In a trading scenario, when matching happens post split of a delivery either intentionally
2047 or due to lot controls , the non-excise taxes are getting incorrectly calculated.
2048 Analysis is that post split when matching happens, there are sets of tax records for each split
2049 in the delivery.When this trigger processess the delivery detail , it again apportions based
2050 on the quantity causing the taxes to be calculated as less than that of the actual value
2051
2052 Solution :- Issue has been resolved by doing the following :
2053 The apportion based on quantity shipped has been removed and instead the tax amount from
2054 the JAI_CMN_MATCH_TAXES table is used for population into JAI_OM_WSH_LINES_ALL
2055 and jain_so_picking_tax_lines table
2056
2057 Dependency : None
2058
2059 21.23/08/2004 Aiyer - bug# 3844145 file version 115.1
2060 Issue:-
2061 The inventory trip stop concurrent program errors out for a Internal sales order with qty being matched to two different subinventories.
2062
2063 Reason :-
2064 The existing code was considering matched quantites as per the subinventories. This should not be considered.
2065 Matched quantities should be considered irrespective of the subinventories.
2066
2067 Solution:-
2068 Modified the cursor matched_receipt_cur1 in the current trigger.Removed the group by subinventory clause from the query.
2069
2070 Dependency Introduced Due to this Bug : -
2071 None
2072
2073
2074 22. 29/Nov/2004 Aiyer for bug#4035566. Version#115.2
2075 Issue:-
2076 The trigger should not get fired when the non-INR based set of books is attached to the current operating unit
2077 where transaction is being done.
2078
2079 Fix:-
2080 Function jai_cmn_utils_pkg.check_jai_exists is being called which returns the TRUE if the currency is INR and FALSE if the currency is
2081 NON-INR
2082
2083 Dependency Due to this Bug:-
2084 The current trigger becomes dependent on the function jai_cmn_utils_pkg.check_jai_exists version 115.0 introduced through the bug 4033992
2085
2086
2087 23. 2005/02/11 ssumaith - bug# 4171272 - File version 115.3
2088
2089 Shipment needs to be stopped if education cess is not available.
2090
2091 The basic business logic validation is that both cess and excise should be available as
2092 part of the same register type and the precedence setup at the organization additional information
2093 needs to be considered for picking up the correct register order.
2094
2095 This code object calls the functions ja_in_exc_balance_amt_f and ja_in_exc_exempt_balance_amt_f
2096 which have had changes in their signature and hence the caller also needs to pass the correct
2097 parameters.
2098
2099 The change done in this object is to pass the additional parameters correctly to the functions.
2100
2101 Dependency Due to this Bug:-
2102 The current trigger becomes dependent on the functions jai_om_wsh_processing_pkg.excise_balance_check (version 115.1) and
2103 jai_om_wsh_pkg.get_excise_register_with_bal (version 115.1) also packaged as part of this bug.
2104
2105 23. 2005/03/15 brathod - Bug#4215808- File version 115.5
2106 Trigger modified for VAT Implementation.
2107 New VAT fields in JAI_OM_WSH_LINES_ALL are populated by fetching them
2108 from JAI_OM_OE_SO_LINES table. Also populated jai_vat_preocessing_t temporery
2109 table for VAT invoice number generation
2110
2111 24 26/04/2005 Brathod for Bug# 4299606 File Version 116.1
2112 Issue:-
2113 Item DFF Elimination
2114 Fix:-
2115 Changed the code that references attributeN (where N=1,2,3,4,5,15) of
2116 mtl_system_items to corrosponding columns in JAI_INV_ITM_SETUPS
2117
2118 Dependency :-
2119 IN60106 + 4239736 (Service Tax) + 4245089 (VAT)
2120
2121 25 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
2122 DB Entity as required for CASE COMPLAINCE. Version 116.1
2123
2124 26. 13-Jun-2005 File Version: 116.2
2125 Ramananda for bug#4428980. Removal of SQL LITERALs is done
2126
2127 27 30/Jan/2007 bgowrava, forward porting Bug#5631784 (4742259) - File Version 116.2
2128 Modified the trigger for TCS Enahancement.
2129 Changes are made to support following functionalities required for TCS
2130 1. Whenver a Sales Order has TCS type of tax, depending upon the setup done for threshold
2131 Surcharge type of taxes needs to be defaulted at the time of shipping
2132 The following are the logical steps
2133 a. Check if tcs type of taxes exists
2134 b. If yes, check the current threshold slab
2135 c. If threshold up (not null threshold_slab_id) then derrive the tax_category_id attached
2136 in the threshold setup for the slab
2137 d. Based on the tax category (p_threshold_tax_cat_id) defaul the additional taxes by calling
2138 JAI_RGM_THHOLD_PROC_PKG.DEFAULT_THHOLD_TAXES
2139 e. Added the call to jai_ar_tcs_rep_pkg.wsh_interim_accounting to do the interim accounting for TCS type of taxes.
2140
2141 28. 16/Apr/2007 Bgowrava for forward porting bug#5989740 11i Bug#5907436 - File version
2142 ENH : HANDLING SECONDARY AND HIGHER EDUCATION CESS
2143 additional cess of 1% on all taxes to be levied to fund secondary education and higher
2144 education .
2145 Changes - -
2146 Object Type Object Name Changes
2147 -----------------------------------------------------------------------
2148 Cursor c_sh_cess_amount Cursor is added to get cess amount for seconday and higher cess
2149
2150 Code is added to check balances for secondary and higher educat
2151
2152 29. 17/May/2007 CSahoo for bug 5680459, File Version 120.5
2153 Forward porting of 11i BUG#5645003
2154 modified the cursor c_matched_qty_cur to filter the data only by delivery_detail_id
2155 and order header_id and removed the filter by order line_id
2156 Added the cursors c_order_line and c_lc_mtch_dlry_line, to check if the order line attached
2157 the delivery detail in LC matching table is same or not. If not, then the related delivery_detail
2158 line is updated with the pr_new.source_line_id
2159 Added the cursors c_chk_vat_reversal.
2160 modified the code in ARU_T3
2161
2162 30 09/10/2007 ssumaith - bug#6487667 - File version - 120.6
2163
2164 When comparing the register balance, the amount in INR is not compared. Instead the amount in the Fc is compared.
2165 This has been corrected by multiplying the v_tot_excise_amt with the currency conversion factor.
2166
2167 31. 16/10/2007 CSahoo for bug#6498072, File Version 120.8
2168 Modified the p_assessable_value parameter during call to JAI_RGM_THHOLD_PROC_PKG.DEFAULT_THHOLD_TAXES
2169 p_assessable_value => nvl(v_assessable_value * v_shipped_quantity, 0)
2170 Moved cursor uom_code to the start of the loop to fetch v_order_quantity_uom
2171
2172 32. 16/10/2008 CSahoo for bug#5189432, File Version 120.9.12010000.2
2173 Assigned the sysdate to the variable v_creation_date instead of pr_new.creation_date
2174
2175 33. 13/11/2008 JMEENA for bug#6280735( FP6164922)
2176 Issue:WRONG EXCISE DUTY PASSED TO DELY WHILE MATCHING RG23D REGISTER.
2177 Fix: 1. The issue was beacuse a conversion factor was getting multiplied to the tax amount while matching.
2178 Hence the excise duty reflected was wrong. so while matching the receipts the conversion factor should be 1
2179 and for other cases it should be as it is. Thus modified the code for the same.
2180 assigned the v_conversion_rate to 1 in case of matching and for
2181 manufacturing organization Inv_Convert.inv_um_conversion should be executed. so included this logic in the else part.
2182 2. Modified the Get_Tax_Lines_Details_Cur cursor. changed the rounding factor to 2 when the
2183 rounding factor is null.
2184
2185 34 14-May-2009 CSahoo for bug#8500697, File Version 120.9.12010000.4, 120.9.12010000.5
2186 Forward ported the changes done for bug#5199329 in 11i.
2187 Added the code to populate the locattion_id in case it is null.
2188 Further modified the procedure ARU_T2. Added a check the if it a non inventory item, then
2189 the error should not be shown.
2190
2191 35 16-Apr-2010 modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement).
2192 modified population logic of table jai_om_wsh_lines_all to populate column
2193 shippable_flag as 'Y'.
2194 File Version: 120.9.12010000.12
2195
2196 36 28-Apr-2010 modified by Allen Yang for bug 9666476
2197 logic of shippable_flag population is changed back. For shippable lines,
2198 shippable_flag will be still populated with NULL.
2199
2200 37 07-Jun-2010 Modified by Jia for bug#9736876
2201 Issue: TAXES POSTED TO PLA IN FOREIGN CURRENCY
2202 Fix: If conversion rate is not defined on ship confirm, and when Entered Currency is not equal to Functional Currency
2203 giving an warning in the "Interface Trip Stop" concurrent saying "Currency Conversion on shipment date not setup".
2204
2205 38. 24-JUN-2010 vkaranam for bug#9839132,
2206 FOREIGN CURRENCY TAXES ARE NOT DISPLAYED CORRECTLY IN SHIP CONFIRM LOCALIZED FORm
2207 Issue:
2208 Issue is happening for the trading organization.
2209 When the sales order is created in Foreign currency and matched against the receipt with INR taxes,
2210 After matching the receipt and delivered ,
2211 Ship confirm localized window shows the excise type of taxes in functional currency i.e INR
2212 and the other taxes in the document currency "USD".
2213 This will effect the AR invoice generated.
2214 Fix:
2215 ---
2216 1.When the order is matched against the receipt,the taxes that flows from
2217 receipt to the sip confirm localized window should be shown in the document currency
2218 2.Whenever the Order is delivered,
2219 Taxes should be recalculated taking the exchange rate as on Shipment date.
2220 Excise taxes ,functional_tax_amount will be fixed INR from the receipt,
2221 Tax_amount column should change based on the conversion rate.
2222 whereas for other type of taxes func_tax_amount will be recalulated based on the conversion rate.
2223
2224 Changes are done in this file for point #2.
2225
2226 39. 15-Mar-2012 mmurtuza for bug 13793843
2227 Description: VAT AND TCS TAXES ARE APPLYING WRONGLY IN SHIPPING FORM
2228 Fix: ln_vat_assessable_value is calculated for the shipped quantity.
2229
2230 40. 05-Apr-2012 mmurtuza for bug 13885787
2231 Description: INTERFACE TRIP STOP PERFORMANCE
2232 Fix: Added delivery_id condrion in cursor Get_Delivery_Line_Count_Cur in procedure ARU_T3
2233
2234 41. 01-Mar-2013 mmurtuza for bug 16396199
2235 Description: INDIA: WRONG TAX AMOUNT FLOWS FROM SALES ORDER TO SHIPPING LOCALIZED FORM
2236 Fix: Commented the code to proption VAT assessable value and added it before checking for TCS taxes, as it should be proportioned
2237 for all tax types
2238
2239 42. 07-Apr-2013 mmurtuza for bug 16343067 / 16611281
2240 Description: EXCISE AMOUNT IS ROUNDED ON BOND REGISTER SUMMARY FORM
2241 Fix: Commented the logic of rounding the func_tax_amount to nearest rupee in case of foreign currency
2242
2243 Future Dependencies For the release Of this Object:-
2244 (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/
2245 A datamodel change )
2246
2247 ----------------------------------------------------------------------------------------------------------------------------------------------------
2248 Current Version Current Bug Dependent Files Version Author Date Remarks
2249 Of File On Bug/Patchset Dependent On
2250 ja_in_wsh_dlry_dtls_au_trg.sql
2251 ----------------------------------------------------------------------------------------------------------------------------------------------------
2252 616.2 3021588 IN60104D1 + ssumaith 22/08/2003 Bond Register Enhancement
2253 2801751 +
2254 2769440
2255
2256 115.2 4035566 IN60105D2 +
2257 4033992 ja_in_util_pkg_s.sql 115.0 Aiyer 29-Nov-2004 Call to this function.
2258 ja_in_util_pkg_b.sql 115.0
2259 115.3 4171272 IN60106 +
2260 4147608 ja_in_exc_exempt_balance_amt_f.sql 115.1 ssumaith 11/02/2005 New parameters added to function.
2261 ja_in_exc_balance_amt_f.sql 115.1 ssumaith 11/02/2005 New parameters added to function.
2262
2263 115.5 4215808 IN60106 All VAT Objects
2264 +4245089
2265
2266 ------------------------------------------------------------------------------------------------------------------------------------------------*/
2267
2268 --File.Sql.35 Cbabu
2269 v_creation_date := sysdate; -- replaced pr_new.Creation_Date by sysdate for bug#5189432
2270 v_created_by :=pr_new.Created_By;
2271 v_last_update_date :=pr_new.Last_Update_Date;
2272 v_last_updated_by :=pr_new.Last_Updated_By;
2273 v_last_update_login :=pr_new.Last_Update_Login;
2274 v_delivery_detail_id :=pr_new.Delivery_Detail_Id;
2275 v_source_header_id :=pr_new.Source_Header_Id;
2276 v_source_line_id :=pr_new.Source_Line_Id;
2277 v_Inventory_Item_Id :=pr_new.Inventory_Item_Id;
2278 v_Requested_Quantity_Uom :=pr_new.Requested_Quantity_Uom;
2279 v_org_id :=pr_new.ORG_ID;
2280 v_customer_id :=pr_new.CUSTOMER_ID;
2281 v_source_header_type_id :=pr_new.SOURCE_HEADER_TYPE_ID;
2282 v_subinventory :=pr_new.SUBINVENTORY;
2283 v_released_status :=pr_new.Released_Status;
2284 v_ordered_quantity := NVL(pr_new.Requested_Quantity,0);
2285 v_shipped_quantity := NVL(pr_new.Shipped_Quantity,0);
2286 v_Organization_Id :=pr_new.Organization_Id;
2287 v_basic_excise_duty_amount := 0;
2288 v_add_excise_duty_amount := 0;
2289 v_oth_excise_duty_amount := 0;
2290 v_excise_amount := 0;
2291 v_left_shipped_qty := 0;
2292 v_rg23d_tax_amount := 0;
2293 v_rg23d_base_tax_amount := 0;
2294 v_rg23d_func_tax_amount := 0;
2295 v_tax_amt := 0;
2296 v_base_tax_amt := 0;
2297 v_func_tax_amt := 0;
2298 counter :=0;
2299 v_debug_flag := jai_constants.no;
2300 v_conversion_rate := 0;
2301 v_matched_qty := 0;
2302 v_lc_shipped_qty := pr_new.Shipped_quantity;
2303
2304
2305 IF v_debug_flag ='Y' THEN
2306 BEGIN
2307 pv_return_code := jai_constants.successful ;
2308 SELECT DECODE(SUBSTR (value,1,INSTR(value,',') -1),NULL,
2309 Value,SUBSTR (value,1,INSTR(value,',') -1))
2310 INTO v_utl_location
2311 FROM v$parameter
2312 WHERE name = 'utl_file_dir';
2313 EXCEPTION
2314 WHEN OTHERS THEN
2315 v_debug_flag:='N';
2316 END;
2317 END IF;
2318
2319 IF v_debug_flag ='Y' THEN
2320 v_myfilehandle := UTL_FILE.FOPEN(v_utl_location,'ja_in_wsh_dlry_dtls_au_trg.LOG','A');
2321 UTL_FILE.PUT_LINE(v_myfilehandle,'************************START************************************');
2322 UTL_FILE.PUT_LINE(v_myfilehandle,'The TIME Stamp this ENTRY IS Created IS ' ||TO_CHAR(SYSDATE,'DD/MM/RRRR HH24:MI:SS'));
2323 END IF;
2324
2325 /*
2326 || Code added by aiyer for the bug 4035566
2327 || Call the function jai_cmn_utils_pkg.check_jai_exists to check the current set of books in INR/NON-INR based.
2328 */
2329 --IF jai_cmn_utils_pkg.check_jai_exists ( p_calling_object => 'JA_IN_WSH_DLRY_DTLS_AU_TRG' ,
2330 -- p_org_id => pr_new.org_id
2331 -- ) = FALSE
2332 --THEN
2333 /*
2334 || return as the current set of books is NON-INR based
2335 */
2336 -- RETURN;
2337 --END IF;
2338
2339 OPEN get_ship_to_org_id_cur( v_source_line_id);
2340 FETCH get_ship_to_org_id_cur INTO v_ship_to_org_id , v_bill_to_org_id; -- added v_bill_to_org_id for bug#8731696
2341 CLOSE get_ship_to_org_id_cur;
2342
2343 --added for bug#8731696,start
2344 OPEN cur_get_bill_to_cust_id(v_bill_to_org_id);
2345 FETCH cur_get_bill_to_cust_id INTO ln_bill_to_cust_id;
2346 CLOSE cur_get_bill_to_cust_id;
2347 --bug#8731696, end
2348
2349 /*
2350 This code is added by aiyer for the bug #3139718
2351 Added the check that the trigger should be bypassed in case the functional currency code is NON INR.
2352 */
2353 IF pr_new.org_id IS NOT NULL THEN
2354 /* Bug 5243532. Added by Lakshmi Gopalsami
2355 Removed the reference to cursor sob_cur and implemented using
2356 caching logic.
2357 */
2358
2359 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
2360 (p_org_id => pr_new.org_id);
2361 v_currency_code := l_func_curr_det.currency_code;
2362 v_set_of_books_id := l_func_curr_det.ledger_id;
2363 -- end for bug 5243532
2364 END IF;
2365
2366 -- Check for the Delivery Status
2367 OPEN Get_Status_Cur;
2368 FETCH Get_Status_Cur INTO v_delivery_id, v_date_confirmed, v_status_code;
2369 CLOSE Get_Status_Cur;
2370 IF NVL(v_status_code,'#') NOT IN ('CO', 'IT','CL') THEN
2371 RETURN;
2372 END IF; --1
2373 --2001/05/31 Anuradha Parthasarathy
2374 IF NVL(pr_new.shipped_quantity,0) = 0 THEN
2375 RETURN;
2376 END IF;
2377 -- Check whether Line Details exists in Localization table.
2378 OPEN Get_So_Lines_Count_Cur;
2379 FETCH Get_So_Lines_Count_Cur INTO v_so_lines_count;
2380 CLOSE Get_So_Lines_Count_Cur ;
2381 IF v_so_lines_count = 0 THEN --2
2382 RETURN;
2383 END IF; --2
2384 -- Fetch Lines Details from Localization Table
2385 OPEN Get_So_Lines_Details_Cur;
2386 FETCH Get_So_Lines_Details_Cur INTO
2387 v_selling_price,
2388 v_quantity,
2389 v_tax_category_id,
2390 v_assessable_value,
2391 ln_vat_assessable_value,
2392 v_excise_exempt_type,
2393 v_excise_exempt_refno,
2394 v_excise_exempt_date,
2395 -- Added by Brathod for Bug#4215808
2396 lv_vat_exemption_flag,
2397 lv_vat_exemption_type,
2398 ld_vat_exemption_date,
2399 lv_vat_exemption_refno;
2400 -- End of Bug#4215808
2401 CLOSE Get_So_Lines_Details_Cur;
2402 --Get The Location Id
2403 OPEN Location_Cursor;
2404 FETCH Location_Cursor INTO v_location_id, v_trading_flag;
2405 CLOSE Location_Cursor;
2406
2407 /*start. csahoo for bug#8500697*/
2408 IF v_location_id IS NULL THEN
2409 v_location_id := pr_new.ship_from_location_id ;
2410 END IF;
2411 /*end. rchandan for bug#8500697*/
2412
2413 OPEN Trading_register_code_cur(v_organization_id, v_location_id,v_delivery_detail_id, v_source_header_type_id);
2414 FETCH Trading_register_code_cur INTO v_trad_register_code;
2415 CLOSE Trading_register_code_cur;
2416
2417 --2001/07/10 Anuradha Parthasarathy
2418 OPEN item_trading_cur;
2419 FETCH item_trading_cur INTO v_item_trading_flag;
2420 CLOSE item_trading_cur;
2421 OPEN get_item_attributes;
2422 FETCH get_item_attributes INTO v_exe_flag,v_mod_flag;
2423 CLOSE get_item_attributes;
2424 /*
2425 Code modified by aiyer for the bug#3032569
2426 Modified the IF statment to raise an error only when trading register code is in 23D_DOMESTIC_EXCISE and 23D_EXPORT_EXCISE.
2427 The other two trading register_codes '23D_DOM_WITHOUT_EXCISE' and '23D_EXPORT_WITHOUT_EXCISE' have been removed from this if statement
2428 as matched receipts is not relevant in this case.
2429 */
2430
2431 -- Start of Bug #3032569
2432
2433 IF v_trad_register_code IN(
2434 '23D_DOMESTIC_EXCISE',
2435 '23D_EXPORT_EXCISE'
2436 )
2437 THEN
2438 -- End of Bug #3032569
2439
2440 IF NVL(v_trading_flag,'N') = 'Y' AND NVL(V_item_trading_flag,'N') = 'Y' AND NVL(v_exe_flag,'N')= 'Y' THEN
2441 OPEN matched_receipt_cur1;
2442 FETCH matched_receipt_cur1 INTO v_matched_qty;
2443 CLOSE matched_receipt_cur1;
2444 IF NVL(v_shipped_quantity,0) <> NVL(v_matched_qty,0) THEN
2445 /* RAISE_APPLICATION_ERROR(-20401, 'Matched Quantity -- ' || TO_CHAR(NVL(v_matched_qty,0)) ||
2446 ' should be equal to Shipped Quantity -- ' || TO_CHAR(NVL(v_shipped_quantity,0))); */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'Matched Quantity -- ' || TO_CHAR(NVL(v_matched_qty,0)) ||
2447 ' should be equal to Shipped Quantity -- ' || TO_CHAR(NVL(v_shipped_quantity,0)) ; return ;
2448
2449 END IF;
2450 END IF;
2451 END IF;
2452
2453 /*Commented below code by JMEENA for bug#6731913 as it already exists in ARU_T2.
2454
2455 --THE FOLLOWING LINES ADDED BY SRIRAM - LC FUNCTIONALITY - BUG# 2165355 - 19/09/2002
2456
2457 OPEN C_CHECK_LC_ORDER;
2458 FETCH C_CHECK_LC_ORDER INTO v_check_lc_order;
2459 CLOSE C_CHECK_LC_ORDER;
2460 IF NVL(v_check_lc_order,'N') = 'Y' THEN
2461 OPEN C_MATCHED_QTY_CUR;
2462 FETCH C_MATCHED_QTY_CUR INTO v_lc_qty_matched;
2463 CLOSE C_MATCHED_QTY_CUR;
2464 IF NVL(v_lc_qty_matched,-999) <> NVL(v_lc_shipped_qty,-888) THEN
2465 RAISE_APPLICATION_ERROR(-20120, 'LC Matched Quantity -- ' || TO_CHAR(NVL(v_lc_qty_matched,0)) ||
2466 ' should be equal to Shipped Quantity -- ' || TO_CHAR(NVL(v_lc_shipped_qty,0)) || ' for LC enabled Orders');
2467 pv_return_code := jai_constants.expected_error ; pv_return_message := 'LC Matched Quantity -- ' || TO_CHAR(NVL(v_lc_qty_matched,0)) ||
2468 ' should be equal to Shipped Quantity -- ' || TO_CHAR(NVL(v_lc_shipped_qty,0)) || ' for LC enabled Orders' ; return ;
2469 END IF;
2470
2471 --Start, bug#5680459 csahoo
2472 -- following code is to correct the existing lc_matching order line to new split line id
2473
2474 open c_order_line;
2475 fetch c_order_line into r_order_line;
2476 close c_order_line;
2477
2478 ln_lc_update_cnt := -1;
2479 if r_order_line.split_from_line_id is not null then
2480 open c_lc_mtch_dlry_line;
2481 fetch c_lc_mtch_dlry_line into r_lc_mtch_dlry_line;
2482 close c_lc_mtch_dlry_line;
2483 if pr_new.source_line_id <> r_lc_mtch_dlry_line.order_line_id then
2484
2485 update JAI_OM_LC_MATCHINGS
2486 set order_line_id = pr_new.source_line_id
2487 where delivery_detail_id = pr_new.Delivery_Detail_Id
2488 -- and order_line_id = r_order_line.split_from_line_id
2489 and release_flag is null;
2490 ln_lc_update_cnt := sql%rowcount;
2491 end if;
2492
2493 end if;
2494
2495 -- End, bug#5680459 csahoo
2496
2497
2498 UPDATE JAI_OM_LC_MATCHINGS
2499 SET RELEASE_FLAG = 'Y'
2500 WHERE DELIVERY_DETAIL_ID = pr_new.Delivery_Detail_id;
2501 END IF;
2502
2503 -- ENDS HERE - CHANGES BY SRIRAM FOR LC FUNCTIONALITY - BUG # 2165355 - 19/09/2002
2504
2505 End of bug#6731913 */
2506
2507 -- Start of code for bug #3139718
2508 OPEN get_conv_detail_cur;
2509 FETCH get_conv_detail_cur INTO v_currency_code, v_conv_type_code,v_conv_rate, v_conv_date;
2510
2511 IF get_conv_detail_cur%FOUND THEN
2512
2513 v_curr_conv_rate := jai_cmn_utils_pkg.currency_conversion (
2514 v_set_of_books_id ,
2515 v_currency_code ,
2516 v_conv_date ,
2517 v_conv_type_code ,
2518 v_conv_rate
2519 );
2520 -- Added by Jia for bug#9736876, Begin
2521 ----------------------------------------------
2522 IF v_curr_conv_rate IS NULL
2523 THEN
2524 lv_process_message := 'Currency Conversion on shipment date not setup.';
2525 app_exception.raise_exception
2526 (exception_type => 'APP'
2527 ,exception_code => -20275
2528 ,exception_text => lv_process_message
2529 );
2530 END IF;
2531 ----------------------------------------------
2532 -- Added by Jia for bug#9736876, End
2533 END IF;
2534 -- End of code for bug #3139718
2535
2536 CLOSE get_conv_detail_cur;
2537
2538 -- Start Inserting Tax Lines
2539 FOR Rec IN Get_Tax_Lines_Details_Cur
2540 LOOP
2541 counter:=counter+1;
2542
2543 /* Moved the code from below else part to here for bug# 6498072 */
2544 OPEN uom_code;
2545 FETCH uom_code INTO v_order_quantity_uom;
2546 CLOSE uom_code;
2547
2548 --2001/06/08 Anuradha Parthasarathy
2549 /*
2550 This if statement has been modified by aiyer for the bug #2988829.
2551 As the tax amount, base_tax_amount and func_tax_amount would be present in table JAI_CMN_MATCH_TAXES
2552 only when match receipt functionality has been done .
2553 Now in order to check that the match receipts functionality has been performed the following check has been added in additions
2554 to the other three checks (of organization being a trading organization, Item being tradable and excisable ):-
2555 The order attached to the bond register is one of 'Trading Domestic With Excise' or 'Export With Excise'
2556 hence applying the check that
2557 v_trad_register_code IN ( '23D_DOMESTIC_EXCISE','23D_EXPORT_EXCISE') .
2558 Before this fix, Null value used to get inserted into taxes in table JAI_OM_WSH_LINE_TAXES for Bond register with
2559 'Trading Domestic Without Excise' and 'Export Without Excise' ( problem stated in the bug )
2560 */
2561 IF NVL(v_trading_flag,'N') = 'Y' AND
2562 NVL(v_item_trading_flag,'N') = 'Y' AND
2563 NVL(v_exe_flag,'N') = 'Y' AND
2564 v_trad_register_code IN ( '23D_DOMESTIC_EXCISE','23D_EXPORT_EXCISE')
2565 THEN
2566
2567 OPEN rg23d_amount_cur(rec.tax_id);
2568 FETCH rg23d_amount_cur INTO v_tax_amt,v_base_tax_amt,v_func_tax_amt;
2569 CLOSE rg23d_amount_cur;
2570 /***start additions for bug#9839132**/
2571 IF v_debug_flag ='Y' THEN
2572 UTL_FILE.PUT_LINE(v_myfilehandle,'Delivery detail id '||v_delivery_Detail_id||
2573 ' Currency Conversion rate --'|| v_curr_conv_rate);--9839132
2574 UTL_FILE.PUT_LINE(v_myfilehandle,'Tax type --'|| rec.tax_type ||
2575 ' tax id '||rec.tax_id ||
2576 ' tax_amount '|| v_tax_amt ||
2577 ' functional tax amount '|| v_func_tax_amt||
2578 ' v_base_tax_amt '||v_base_tax_amt);--9839132
2579 end if;
2580 if upper(rec.tax_type) like '%EXCISE%'
2581 then
2582 /**for excise type of taxes func_tax_amt is in fixed INR and it wont change based on the exchage rate.
2583 Transaction amount will be changed based on the exchange rate**/
2584 v_tax_amt := (v_func_tax_amt * 1/ nvl(v_curr_conv_rate,1)) ;
2585 v_base_tax_amt := v_tax_amt;
2586 IF v_debug_flag ='Y' THEN
2587 UTL_FILE.PUT_LINE(v_myfilehandle,'After conversion excise transaction tax amount '||v_tax_amt);
2588 end if;
2589 else
2590 --for other type of taxes functional tax amounts will be changing
2591 v_func_tax_amt := (v_tax_amt * nvl(v_curr_conv_rate,1)) ;
2592 IF v_debug_flag ='Y' THEN
2593 UTL_FILE.PUT_LINE(v_myfilehandle,'After conversion other taxes functional tax amount '||v_func_tax_amt);
2594 end if;
2595 end if;
2596 /**end additions for bug#9839132***/
2597
2598
2599 v_conversion_rate := 1; -- conversion_rate should be 1 in case of trading, added by JMEENA for bug#6280735 (FP 6164922)
2600 ELSE
2601 /*
2602 this control comes here for manufacturing - all scenarios
2603 */
2604 v_tax_amt := (v_shipped_quantity * (rec.tax_amount/v_quantity)) ;
2605 v_base_tax_amt := (v_shipped_quantity * (rec.base_tax_amount/v_quantity)) ;
2606 /*
2607 Code modified by aiyer for the bug 3139718
2608 As the Conversion rate can be different while the sales order was booked and when the sales order would be shipped.
2609 So during shipping the functional tax amount needs to be recalculated from the tax_amount column in JAI_OM_OE_SO_TAXES
2610 , hence setting the v_func_tax_amt = v_tax_amt * nvl((v_curr_conv_rate ,1)
2611 */
2612 v_func_tax_amt := (v_tax_amt * nvl(v_curr_conv_rate,1)) ; -- added by ssumaith - bug#3609172
2613 --END IF; Commented by JMEENA for bug#6280735
2614
2615 -- Proportionate the Tax Amounts as per the New Shipped Quantity
2616 -- and round it off according to the Rounding Factor Defined.
2617 -- 2001/10/03 Anuradha Parthasarathy
2618
2619 --Moved the following code to the start of the loop for bug# 6498072
2620 --OPEN uom_code;
2621 --FETCH uom_code INTO v_order_quantity_uom;
2622 --CLOSE uom_code;
2623
2624 /*included the UOM conversion logic into the else part Earlier it was outside the end of If condition.
2625 added by JMEENA for bug#6280735 (FP 6164922) */
2626 Inv_Convert.inv_um_conversion(v_Requested_Quantity_Uom,
2627 v_order_quantity_uom,
2628 v_inventory_item_id,
2629 v_conversion_rate);
2630 IF NVL(v_conversion_rate, 0) <= 0 THEN
2631 Inv_Convert.inv_um_conversion(v_Requested_Quantity_Uom,
2632 v_order_quantity_uom,
2633 0,
2634 v_conversion_rate);
2635 IF NVL(v_conversion_rate, 0) <= 0 THEN
2636 v_conversion_rate := 0;
2637 END IF;
2638 END IF;
2639 END IF; --Added by JMEENA for bug#6280735
2640 -- the following section added by sriram on 24-aug-02 bug # 25310103
2641 -- this was done because - it will ensure that Line splitting \ Backordering is not supported only
2642 -- when the Organization as well as Item are both Trading which is a
2643 -- requirement when match receipt funtionality for RG23D is being used.
2644
2645 -- code reorg done by sriram - bug#3609172 - for old code refer to rcs version 619.1
2646
2647 v_tax_amount := ROUND((v_tax_amt) * v_conversion_rate,rec.rounding_factor);
2648 v_base_tax_amount := ROUND((v_base_tax_amt) * v_conversion_rate,rec.rounding_factor);
2649
2650 IF NVL(v_trading_flag,'N') = 'Y' AND
2651 NVL(v_item_trading_flag,'N') = 'Y' AND
2652 NVL(v_exe_flag,'N') = 'Y' AND
2653 v_trad_register_code IN ( '23D_DOMESTIC_EXCISE','23D_EXPORT_EXCISE')
2654 AND upper(Rec.Tax_type) like '%EXCISE%'--bug #9839132,added the upper clause such that 'cess' type of taxes will also be included
2655 -- Excise added into the if by Sriram for Bug# 3148621 17/09/2003
2656 THEN
2657 v_func_tax_amount := ROUND((v_func_tax_amt)* v_conversion_rate, rec.rounding_factor);
2658 ELSE
2659 -- IF v_curr_conv_rate <> 1 AND upper(rec.tax_type) like '%EXCISE%' THEN --bug#9839132 /*Commented if by mmurtuza for bug 16343067/16611281*/
2660 -- v_func_tax_amount := ROUND((v_func_tax_amt)* v_conversion_rate,0);
2661 -- ELSE
2662 v_func_tax_amount := ROUND((v_func_tax_amt)* v_conversion_rate, rec.rounding_factor);
2663 -- END IF;
2664 END IF;
2665
2666
2667 -- Accumulate the respective types of Excise Duties
2668 -- for inserting into JAI_OM_WSH_LINES_ALL Table.
2669 IF rec.tax_type = 'Excise' THEN --3
2670 v_basic_excise_duty_amount := NVL(v_basic_excise_duty_amount,0) + v_tax_amount ;
2671 ELSIF rec.tax_type = 'Addl. Excise' THEN --3
2672 v_add_excise_duty_amount := NVL(v_add_excise_duty_amount,0) + v_tax_amount ;
2673 ELSIF rec.tax_type = 'Other Excise' THEN --3
2674 v_oth_excise_duty_amount := NVL(v_oth_excise_duty_amount,0) + v_tax_amount ;
2675 END IF;
2676
2677 IF v_debug_flag ='Y' THEN
2678 UTL_FILE.PUT_LINE(v_myfilehandle,'1 v_basic_excise_duty_amount -> '||v_basic_excise_duty_amount
2679 ||', v_add_excise_duty_amount -> '|| v_add_excise_duty_amount
2680 ||', v_oth_excise_duty_amount -> '|| v_oth_excise_duty_amount
2681 );
2682 END IF; --3
2683 -- Check for the existence of Tax Lines in JAI_OM_WSH_LINE_TAXES
2684 OPEN Pick_Tax_Line_Count_Cur(rec.tax_id);
2685 FETCH Pick_Tax_Line_Count_Cur INTO v_picking_tax_lines_count;
2686 CLOSE Pick_Tax_Line_Count_Cur;
2687 IF v_picking_tax_lines_count = 0 THEN --4
2688 INSERT INTO JAI_OM_WSH_LINE_TAXES(Delivery_Detail_Id,
2689 Tax_Line_No,
2690 Precedence_1,
2691 Precedence_2,
2692 Precedence_3,
2693 Precedence_4,
2694 Precedence_5,
2695 Precedence_6, -- CHANGE ON 02/11/2006 BY SACSETHI FOR BUG 5228046
2696 Precedence_7,
2697 Precedence_8,
2698 Precedence_9,
2699 Precedence_10,
2700 Tax_Id,
2701 Tax_Rate,
2702 Qty_Rate,
2703 Uom,
2704 Tax_Amount,
2705 Base_Tax_Amount,
2706 Func_Tax_Amount,
2707 Creation_Date,
2708 Created_By,
2709 Last_Update_Date,
2710 Last_Updated_By,
2711 Last_Update_Login)
2712 VALUES (
2713 v_delivery_detail_id,
2714 rec.Tax_Line_No,
2715 rec.Precedence_1,
2716 rec.Precedence_2,
2717 rec.Precedence_3,
2718 rec.Precedence_4,
2719 rec.Precedence_5,
2720 rec.Precedence_6, -- CHANGE ON 02/11/2006 BY SACSETHI FOR BUG 5228046
2721 rec.Precedence_7,
2722 rec.Precedence_8,
2723 rec.Precedence_9,
2724 rec.Precedence_10,
2725 rec.Tax_id,
2726 rec.Tax_rate,
2727 rec.Qty_Rate,
2728 rec.Uom,
2729 v_tax_amount,
2730 v_base_tax_amount,
2731 v_func_tax_amount,
2732 v_creation_date,
2733 v_created_by,
2734 v_last_update_date,
2735 v_last_updated_by,
2736 v_last_update_login
2737 );
2738 ELSE --4
2739 UPDATE JAI_OM_WSH_LINE_TAXES
2740 SET Tax_Amount = v_tax_amount,
2741 base_tax_amount = v_base_tax_amount, /*12850620*/
2742 func_tax_amount = v_func_tax_amount, /*12850620*/
2743 Last_Update_Date = v_last_update_date,
2744 Last_Updated_By = v_last_updated_by,
2745 Last_Update_Login = v_last_update_login
2746 WHERE Delivery_Detail_Id = v_delivery_detail_id
2747 AND Tax_Id = rec.Tax_Id;
2748 END IF; --4
2749 END LOOP;
2750
2751 /* Added below select clause by mmurtuza for bug 16396199 to avoid divide by zero exception*/
2752 select nvl(decode(v_quantity, 0, 1, v_quantity), 1)
2753 into v_quantity
2754 from dual;
2755
2756 ln_vat_assessable_value := nvl(ln_vat_assessable_value * v_shipped_quantity / v_quantity, 0); /* By mmurtuza for bug 16396199 */
2757
2758
2759 /** Added by Bgowrava for forward porting Bug#5631784 , TCS Enh.*/
2760
2761 /**
2762 Aim: Populate TCS Surcharge and Surcharge cess type of taxes if threshold level is high.
2763
2764 Check if TCS type of taxes exists, If yes using the threshold API found out the slab and the tax category id
2765 and delegate the call tax defaultation API
2766 */
2767
2768 ln_tcs_exists := 0; --kunkumar for bug#5604375 and 6066750
2769 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Checking if TCS type of tax exists?'); */ --commented by bgowrava for bug#5631784
2770 open c_chk_rgm_tax_exists ( cp_regime_code => jai_constants.tcs_regime
2771 , cp_delivery_detail_id => v_delivery_detail_id
2772 );
2773 fetch c_chk_rgm_tax_exists into ln_tcs_exists;
2774 close c_chk_rgm_tax_exists ;
2775
2776 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'ln_tcs_exists='||ln_tcs_exists); */ --commented by bgowrava for bug#5631784
2777 if nvl(ln_tcs_exists,0) >0 then --kunkumar for bug#5604375 and 6066750
2778 /* TCS type of tax is present */
2779 fnd_file.put_line(FND_FILE.LOG,'Localization' );
2780
2781 open c_get_regime_id (cp_regime_code => jai_constants.tcs_regime);
2782 fetch c_get_regime_id into ln_tcs_regime_id;
2783 close c_get_regime_id;
2784 /* Find out what is the current slab */
2785 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Get threshold_slab_id. Before calling JAI_RGM_THHOLD_PROC_PKG.GET_THRESHOLD_SLAB_ID');*/ --commented by bgowrava for bug#5631784
2786 jai_rgm_thhold_proc_pkg.get_threshold_slab_id
2787 (
2788 p_regime_id => ln_tcs_regime_id
2789 , p_organization_id => v_organization_id
2790 , p_party_type => jai_constants.party_type_customer
2791 , p_party_id => v_customer_id
2792 , p_org_id => v_org_id
2793 , p_source_trx_date => v_date_confirmed
2794 , p_threshold_slab_id => ln_threshold_slab_id
2795 , p_process_flag => lv_process_flag
2796 , p_process_message => lv_process_message
2797 );
2798 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Process Result for JAI_RGM_THHOLD_PROC_PKG.GET_THRESHOLD_SLAB_ID');
2799 jai_cmn_debug_contexts_pkg.print (ln_reg_id,
2800 'lv_process_flag =' ||lv_process_flag||chr(10)
2801 ||'lv_process_message =' ||lv_process_message
2802 ); */ --commented by bgowrava for bug#5631784
2803
2804 if lv_process_flag <> jai_constants.successful then
2805 app_exception.raise_exception
2806 (exception_type => 'APP'
2807 ,exception_code => -20275
2808 ,exception_text => lv_process_message
2809 );
2810 end if;
2811
2812 if ln_threshold_slab_id is not null then
2813 /* Threshold level is up. Surcharge needs to be defaulted , so find out the tax category based on the threshold slab */
2814 jai_rgm_thhold_proc_pkg.get_threshold_tax_cat_id
2815 (
2816 p_threshold_slab_id => ln_threshold_slab_id
2817 , p_org_id => v_org_id
2818 , p_threshold_tax_cat_id => ln_threshold_tax_cat_id
2819 , p_process_flag => lv_process_flag
2820 , p_process_message => lv_process_message
2821 );
2822
2823 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Process Result for JAI_RGM_THHOLD_PROC_PKG.GET_THRESHOLD_TAX_CAT_ID');
2824 jai_cmn_debug_contexts_pkg.print (ln_reg_id
2825 ,'lv_process_flag =' ||lv_process_flag||chr(10)
2826 ||'lv_process_message =' ||lv_process_message
2827 ); */ --commented by bgowrava for bug#5631784
2828
2829
2830 if lv_process_flag <> jai_constants.successful then
2831 app_exception.raise_exception
2832 (exception_type => 'APP'
2833 ,exception_code => -20275
2834 ,exception_text => lv_process_message
2835 );
2836 end if;
2837
2838 /* Get line number after which threshold taxes needs to be defaulted */
2839 select max(tax_line_no)
2840 into ln_last_line_no
2841 from JAI_OM_WSH_LINE_TAXES
2842 where delivery_detail_id = v_delivery_detail_id;
2843
2844 /* Get line number of the base tax (tax_type=TCS) for calculating the surcharge basically to set a precedence */
2845 select max(tax_line_no)
2846 into ln_base_line_no
2847 from JAI_OM_WSH_LINE_TAXES jsptl
2848 , JAI_CMN_TAXES_ALL jtc
2849 where jsptl.delivery_detail_id = v_delivery_detail_id
2850 and jsptl.tax_id = jtc.tax_id
2851 and jtc.tax_type = jai_constants.tax_type_tcs;
2852
2853 /*
2854 ||Call the helper method to default surcharge taxes on top of the SO taxes using the tax category
2855 || The api jai_rgm_thhold_proc_pkg.default_thhold_taxes inserts lines as per the same specified in the TCS tax category
2856 || into the JAI_OM_WSH_LINE_TAXES table
2857 */
2858
2859 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Calling JAI_RGM_THHOLD_PROC_PKG.DEFAULT_THHOLD_TAXES');*/ --commented by bgowrava for bug#5631784
2860
2861 -- ln_vat_assessable_value := nvl(ln_vat_assessable_value * v_shipped_quantity / v_quantity, 0); /* By mmurtuza for bug 13793843 */
2862 /*Commented above by mmurtuza for bug 16396199 and added it before checking for TCS taxes, as VAT assessable value should be proportioned for all tax types*/
2863
2864 jai_rgm_thhold_proc_pkg.default_thhold_taxes
2865 (
2866 p_source_trx_id => ''
2867 , p_source_trx_line_id => v_delivery_detail_id
2868 , p_source_event => jai_constants.source_ttype_delivery
2869 , p_action => jai_constants.default_taxes
2870 , p_threshold_tax_cat_id => ln_threshold_tax_cat_id
2871 , p_tax_base_line_number => ln_base_line_no
2872 , p_last_line_number => ln_last_line_no
2873 , p_currency_code => v_currency_code
2874 , p_currency_conv_rate => v_conv_rate
2875 , p_quantity => nvl(v_shipped_quantity,0)
2876 , p_base_tax_amt => nvl((v_selling_price * v_conversion_rate) * v_shipped_quantity,0)
2877 , p_assessable_value => nvl(v_assessable_value * v_shipped_quantity, 0) /* Added v_shipped_quantity and nvl() for bug#6498072 */
2878 , p_inventory_item_id => v_inventory_item_id
2879 , p_uom_code => v_order_quantity_uom
2880 , p_vat_assessable_value => ln_vat_assessable_value
2881 , p_process_flag => lv_process_flag
2882 , p_process_message => lv_process_message
2883 );
2884
2885 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Process Result for JAI_RGM_THHOLD_PROC_PKG.DEFAULT_THHOLD_TAXES');
2886 jai_cmn_debug_contexts_pkg.print (ln_reg_id
2887 ,'lv_process_flag =' ||lv_process_flag||chr(10)
2888 ||'lv_process_message =' ||lv_process_message
2889 ); */ --commented by bgowrava for bug#5631784
2890
2891 if lv_process_flag <> jai_constants.successful then
2892 app_exception.raise_exception
2893 (exception_type => 'APP'
2894 ,exception_code => -20275
2895 ,exception_text => lv_process_message
2896 );
2897 end if;
2898
2899 end if; /* ln_threshold_slab_id is not null then */
2900
2901 end if; /** ln_tcs_exists is not null then */
2902
2903 /** End bug 5631784*/
2904
2905
2906
2907
2908
2909 -- THE FOLLOWING CODE ADDED BY SRIRAM - BUG # 2330055 - 08-may-2002
2910 -- This Code was Added because - in case there are items which do not have tax lines , the
2911 -- v_conversion_rate variable is not getting populated - so the selling price and the
2912 -- assessable value fields are being multiplied by the v_conversion_rate which is 0 initially.
2913 -- hence the addition of the following lines ensures the v_conversion_rate is calculated ,
2914 -- multiplied and correctly done with selling price and assessable value.
2915 OPEN uom_code;
2916 FETCH uom_code INTO v_order_quantity_uom;
2917 CLOSE uom_code;
2918 Inv_Convert.inv_um_conversion(v_Requested_Quantity_Uom,
2919 v_order_quantity_uom,
2920 v_inventory_item_id,
2921 v_conversion_rate);
2922 IF NVL(v_conversion_rate, 0) <= 0 THEN
2923 Inv_Convert.inv_um_conversion(v_Requested_Quantity_Uom,
2924 v_order_quantity_uom,
2925 0,
2926 v_conversion_rate);
2927 IF NVL(v_conversion_rate, 0) <= 0 THEN
2928 v_conversion_rate := 0;
2929 END IF;
2930 END IF;
2931 /*
2932 insert into debug_data (str) values ('AFTER - the value OF v_conversion_rate IS ' || to_char(v_conversion_rate));
2933 insert into debug_data (str) values ('AFTER - the value OF v_Requested_Quantity_Uom IS ' || v_Requested_Quantity_Uom);
2934 insert into debug_data (str) values ('AFTER - the value OF v_order_quantity_uom IS ' || v_order_quantity_uom);
2935 */
2936 -- debug code ends here - sriram
2937 /*
2938 code added ends here - 2330055 - 08-may-2002
2939 */
2940 OPEN get_item_dtls(v_organization_id,v_inventory_item_id);
2941 FETCH get_item_dtls INTO v_excise_flag,v_item_class;
2942 CLOSE get_item_dtls;
2943
2944 /*
2945 This if statement has been modified by aiyer for the bug #2988829.
2946 As the tax amount, base_tax_amount and func_tax_amount would be present in table JAI_CMN_MATCH_TAXES
2947 only when match receipt functionality has been done .
2948 Now in order to check that the match receipts functionality has been performed the following check has been added in additions
2949 to the other three checks (of organization being a trading organization, Item being tradable and excisable ):-
2950 The order attached to the bond register is one of 'Trading Domestic With Excise' or 'Export With Excise'
2951 hence applying the check that
2952 v_trad_register_code IN ( '23D_DOMESTIC_EXCISE','23D_EXPORT_EXCISE') .
2953 Before this fix, Null value used to get inserted into taxes in table JAI_OM_WSH_LINES_ALL for Bond register with
2954 'Trading Domestic Without Excise' and 'Export Without Excise' ( problem stated in the bug )
2955 */
2956
2957
2958 IF nvl(v_trading_flag,'N') = 'Y' AND
2959 nvl(v_item_trading_flag,'N') = 'Y' AND
2960 nvl(v_excise_flag,'N') = 'Y' AND
2961 v_trad_register_code IN ( '23D_DOMESTIC_EXCISE','23D_EXPORT_EXCISE')
2962 THEN
2963 OPEN ed_cur('Excise');
2964 FETCH ed_cur INTO v_basic_excise_duty_amount;
2965 CLOSE ed_cur ;
2966 OPEN ed_cur('Addl. Excise');
2967 FETCH ed_cur INTO v_add_excise_duty_amount;
2968 CLOSE ed_cur;
2969 OPEN ed_cur('Other Excise');
2970 FETCH ed_cur INTO v_oth_excise_duty_amount;
2971 CLOSE ed_cur;
2972 END IF;
2973 --Get Cumulative Excise amount
2974 --for inserting into JAI_OM_WSH_LINES_ALL Table.
2975 v_excise_amount := (v_basic_excise_duty_amount + v_add_excise_duty_amount +
2976 v_oth_excise_duty_amount);
2977 -- Get Total Tax Amount for the Line
2978 -- for Inserting into JAI_OM_WSH_LINES_ALL Table.
2979 OPEN Get_Tot_Tax_Amount_Cur;
2980 FETCH Get_Tot_Tax_Amount_Cur INTO v_tot_tax_amount;
2981 CLOSE Get_Tot_Tax_Amount_Cur;
2982 -- Check for Delivery lines existence in JAI_OM_WSH_LINES_ALL Table
2983 OPEN Get_Delivery_Line_Count_Cur ;
2984 FETCH Get_Delivery_Line_Count_Cur INTO v_delivery_line_count;
2985 CLOSE Get_Delivery_Line_Count_Cur ;
2986 IF v_delivery_line_count = 0 THEN
2987 --5
2988 INSERT INTO JAI_OM_WSH_LINES_ALL(
2989 Delivery_Detail_Id,
2990 Order_Header_Id,
2991 Order_Line_Id,
2992 split_from_delivery_detail_id,
2993 Selling_Price,
2994 Quantity,
2995 Assessable_value,
2996 vat_assessable_value,
2997 Tax_Category_Id,
2998 Tax_Amount,
2999 Inventory_Item_Id,
3000 Organization_Id,
3001 Location_Id,
3002 Unit_Code,
3003 Excise_Amount,
3004 Basic_Excise_Duty_Amount,
3005 Add_Excise_Duty_Amount,
3006 Oth_Excise_Duty_Amount,
3007 Excise_Exempt_Type,
3008 Excise_Exempt_Refno,
3009 Excise_Exempt_Date,
3010 Creation_Date,
3011 Created_By,
3012 Last_Update_Date,
3013 Last_Updated_By,
3014 Last_Update_Login,
3015 ORG_ID,
3016 CUSTOMER_ID,
3017 SHIP_TO_ORG_ID,
3018 ORDER_TYPE_ID,
3019 SUBINVENTORY,
3020 DELIVERY_ID,
3021 -- Added by Brathod for Bug#4215808
3022 VAT_EXEMPTION_FLAG,
3023 VAT_EXEMPTION_TYPE,
3024 VAT_EXEMPTION_DATE,
3025 VAT_EXEMPTION_REFNO
3026 -- End of Bug#4215808
3027 -- added by Allen Yang for bug 9485355 16-Apr-2010, begin
3028 , SHIPPABLE_FLAG
3029 -- added by Allen Yang for bug 9485355 16-Apr-2010, end
3030 )
3031 VALUES ( v_delivery_detail_id,
3032 v_source_header_id,
3033 v_source_line_id,
3034 pr_new.split_from_delivery_detail_id,
3035 v_selling_price * v_conversion_rate,
3036 v_shipped_quantity,
3037 v_assessable_value * v_conversion_rate,
3038 ln_vat_assessable_value * v_conversion_rate,
3039 v_tax_category_id,
3040 v_tot_tax_amount,
3041 v_Inventory_Item_Id,
3042 v_Organization_Id,
3043 v_location_id,-- 2001/06/23 Anuradha Parthasarathy
3044 v_Requested_Quantity_Uom,
3045 v_excise_amount,
3046 v_basic_excise_duty_amount,
3047 v_add_excise_duty_amount,
3048 v_oth_excise_duty_amount,
3049 v_excise_exempt_type,
3050 v_excise_exempt_refno,
3051 v_excise_exempt_date,
3052 v_creation_date,
3053 v_created_by,
3054 v_last_update_date,
3055 v_last_updated_by,
3056 v_last_update_login,
3057 v_org_Id,
3058 v_customer_id,
3059 v_ship_to_org_id,
3060 v_source_header_type_id,
3061 v_subinventory,
3062 v_DELIVERY_ID,
3063 -- Added by Brathod for Bug#4215808
3064 lv_vat_exemption_flag,
3065 lv_vat_exemption_type,
3066 ld_vat_exemption_date,
3067 lv_vat_exemption_refno
3068 -- End of Bug#4215808
3069 -- added by Allen Yang for bug 9485355 16-Apr-2010, begin
3070 --, 'Y' -- shippable_flag commented by allen yang for bug 9666476
3071 , NULL -- shippable_flag, added by Allen Yang for bug 9666476
3072 -- added by Allen Yang for bug 9485355 16-Apr-2010, end
3073 );
3074
3075 -- Insert the Data Required for RG entries into a Temporary Table
3076 IF NVL(v_excise_flag,'N') = 'Y' THEN
3077 INSERT INTO JAI_OM_OE_GEN_TAXINV_T(
3078 date_released,
3079 date_confirmed,
3080 delivery_detail_id,
3081 order_header_id,
3082 creation_date,
3083 created_by,
3084 last_update_date,
3085 last_updated_by,
3086 last_update_login,
3087 delivery_id
3088 )
3089 VALUES (
3090 SYSDATE,
3091 v_date_confirmed,
3092 v_delivery_detail_id,
3093 v_source_header_id,
3094 v_creation_date,
3095 v_created_by,
3096 v_last_update_date,
3097 v_last_updated_by,
3098 v_last_update_login,
3099 v_delivery_id
3100 );
3101 END IF;
3102
3103 -- Added by brathod for Bug#4215808
3104 /*
3105 || check if VAT type of tax exists
3106 */
3107
3108 OPEN cur_chk_vat_exists (cp_del_det_id => v_delivery_detail_id) ;
3109 FETCH cur_chk_vat_exists INTO ln_vat_cnt;
3110 CLOSE cur_chk_vat_exists ;
3111
3112 OPEN cur_chk_vat_proc_entry (cp_delivery_id => v_delivery_id);
3113 FETCH cur_chk_vat_proc_entry INTO ln_vat_proc_cnt ;
3114 CLOSE cur_chk_vat_proc_entry;
3115
3116
3117 /*
3118 || Added by csahoo for bug#5680459
3119 || Check if only 'VAT REVERSAL' tax type is present in JAI_OM_WSH_LINE_TAXES
3120 */
3121 IF nvl(ln_vat_cnt,0) = 0 THEN /* If taxes of type 'VAT' are not present */
3122 lv_vat_reversal := 'VAT REVERSAL' ;
3123 OPEN c_chk_vat_reversal(cp_del_det_id => v_delivery_detail_id,
3124 cp_tax_type => lv_vat_reversal) ;
3125 FETCH c_chk_vat_reversal INTO ln_vat_reversal_exists;
3126 CLOSE c_chk_vat_reversal ;
3127
3128 /*
3129 || VAT invoice number should be punched as 'NA' and accounting should happen
3130 || when 'VAT REVERSAL' type of tax exist and 'VAT' type of tax(es) doesn't exist
3131 */
3132 lv_vat_invoice_no := jai_constants.not_applicable ;
3133 lv_vat_inv_gen_status := 'C' ;
3134 END IF ;
3135
3136 /*
3137 || Added 'OR nvl(ln_vat_reversal_exists,0) = 1' for bug#5680459
3138 || If taxes of 'VAT' type (or) taxes of 'VAT REVERSAL' type exists
3139 */
3140
3141 IF (nvl(ln_vat_cnt,0) > 0 OR nvl(ln_vat_reversal_exists,0) = 1 ) AND nvl (ln_vat_proc_cnt,0) = 0 THEN
3142 /* VAT type of tax exists*/
3143 /* Get the regime id for these type of taxes */
3144 OPEN cur_get_regime_info (cp_organization_id => v_organization_id,
3145 cp_location_id => v_location_id
3146 );
3147 FETCH cur_get_regime_info INTO ln_regime_id,
3148 lv_regns_num;
3149 CLOSE cur_get_regime_info;
3150
3151 INSERT INTO JAI_RGM_INVOICE_GEN_T ( regime_id ,
3152 delivery_id ,
3153 delivery_date ,
3154 customer_trx_id ,
3155 organization_id ,
3156 location_id ,
3157 registration_num ,
3158 vat_invoice_no ,
3159 vat_inv_gen_status ,
3160 vat_inv_gen_err_message ,
3161 vat_acct_status ,
3162 vat_acct_err_message ,
3163 request_id ,
3164 program_application_id ,
3165 program_id ,
3166 program_update_date ,
3167 party_id ,
3168 party_site_id ,
3169 party_type ,
3170 creation_date ,
3171 created_by ,
3172 last_update_date ,
3173 last_update_login ,
3174 last_updated_by
3175 )
3176 VALUES (ln_regime_id ,
3177 v_delivery_id ,
3178 v_creation_date ,
3179 null , -- customer_trx_id
3180 v_organization_id ,
3181 v_location_id ,
3182 lv_regns_num ,
3183 lv_vat_invoice_no , -- vat_invoice_no --Replaced NULL with lv_vat_invoice_no for bug#5680459
3184 nvl(lv_vat_inv_gen_status, 'P'), -- vat_inv_gen_status --Added nvl() for bug#5680459
3185 null , -- vat_inv_gen_err_message
3186 'P' , -- vat_acct_status
3187 null , -- vat_acct_err_message
3188 null , -- request_id
3189 null , -- program_application_id
3190 null , -- program_id
3191 null , -- program_update_date
3192 ln_bill_to_cust_id , --added for bug#8731696
3193 v_bill_to_org_id , --added for bug#8731696
3194 jai_constants.party_type_customer,
3195 v_creation_date ,
3196 v_created_by ,
3197 v_last_update_date ,
3198 v_last_update_login ,
3199 v_last_updated_by
3200 );
3201
3202 END IF;
3203 -- End of Bug#4215808
3204
3205 ELSE
3206 UPDATE JAI_OM_WSH_LINES_ALL
3207 SET quantity = v_shipped_quantity,
3208 tax_amount = v_tot_tax_amount,
3209 order_line_id = v_source_line_id,
3210 excise_amount = v_excise_amount,
3211 basic_excise_duty_amount = v_basic_excise_duty_amount,
3212 add_excise_duty_amount = v_add_excise_duty_amount,
3213 oth_excise_duty_amount = v_oth_excise_duty_amount,
3214 last_update_date = v_last_update_date,
3215 last_updated_by = v_last_updated_by,
3216 last_update_login = v_last_update_login,
3217 -- Added by Brathod for Bug#4215808
3218 VAT_EXEMPTION_FLAG = lv_vat_exemption_flag,
3219 VAT_EXEMPTION_TYPE = lv_vat_exemption_type,
3220 VAT_EXEMPTION_DATE = ld_vat_exemption_date,
3221 VAT_EXEMPTION_REFNO = lv_vat_exemption_refno
3222 -- End of Bug#4215808
3223 WHERE Delivery_id = v_delivery_id
3224 AND Delivery_Detail_id = v_delivery_detail_id;
3225 END IF; --5
3226
3227
3228 /*
3229 || Start of bug #5631784
3230 || Code added by bgowrava for the forward porting bug
3231 */
3232 /* jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
3233 pv_log_msg => ' Before call to jai_ar_tcs_rep_pkg.wsh_interim_accounting '
3234 ); */ --commented by bgowrava for bug#5631784
3235
3236 /*
3237 ||Does interim TCS accounting for the TCS type of taxes
3238 */
3239 jai_ar_tcs_rep_pkg.wsh_interim_accounting ( p_delivery_id => v_delivery_id ,
3240 p_delivery_detail_id => v_delivery_detail_id ,
3241 p_order_header_id => v_source_header_id ,
3242 p_organization_id => v_organization_id ,
3243 p_location_id => v_location_id ,
3244 p_currency_code => v_currency_code ,
3245 p_process_flag => lv_process_flag ,
3246 p_process_message => lv_process_message
3247 );
3248 /* jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
3249 pv_log_msg => ' Returned from jai_ar_tcs_rep_pkg.wsh_interim_accounting '
3250 ); */ --commented by bgowrava for bug#5631784
3251
3252 IF lv_process_flag = jai_constants.expected_error OR ---------A2
3253 lv_process_flag = jai_constants.unexpected_error
3254 THEN
3255 /*
3256 || As Returned status is an error/not applicable hence:-
3257 || Set out variables p_process_flag and p_process_message accordingly
3258 */
3259 /* jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
3260 pv_log_msg => ' Error in processing jai_ar_tcs_rep_pkg.wsh_interim_accounting ' ||CHR(10)
3261 ||',lv_process_flag -> '||lv_process_flag ||CHR(10)
3262 ||',lv_process_message -> '||lv_process_message
3263 ); */ --commented by bgowrava for bug#5631784
3264 fnd_message.set_name ( application => 'JA',
3265 name => 'JAI_ERR_DESC'
3266 );
3267
3268 fnd_message.set_token ( token => 'JAI_ERROR_DESCRIPTION',
3269 value => lv_process_message
3270 );
3271
3272 app_exception.raise_exception;
3273
3274 END IF; ---------A2
3275
3276
3277 /*
3278 || End of bug #5631784
3279 */
3280
3281
3282 --------------------------------------------------------------------------------------------------------
3283 -- start
3284 OPEN bonded_cur(v_organization_id, v_subinventory);
3285 FETCH bonded_cur INTO v_bonded_flag;
3286 CLOSE bonded_cur;
3287 IF v_debug_flag ='Y' THEN
3288 UTL_FILE.PUT_LINE(v_myfilehandle,'2 BEFORE the assignment OF v_order_type_id');
3289 END IF;
3290 v_order_type_id := v_source_header_type_id;
3291 IF v_debug_flag ='Y' THEN
3292 UTL_FILE.PUT_LINE(v_myfilehandle,'3 v_order_type_id IS '|| v_order_type_id);
3293 END IF;
3294
3295 -- added by sriram - bug # 3021588
3296
3297 jai_cmn_bond_register_pkg.GET_REGISTER_ID(v_organization_id,
3298 v_location_id,
3299 v_order_type_id,
3300 'Y',
3301 v_asst_register_id,
3302 v_register_code
3303 );
3304
3305
3306 -- following cursor has been commented and instead call to the jai_cmn_bond_register_pkg package has been done
3307
3308 /*OPEN register_code_cur(v_organization_id, v_location_id,v_order_type_id);
3309 FETCH register_code_cur INTO v_register_code;
3310 CLOSE register_code_cur;
3311 */
3312
3313
3314 IF v_debug_flag ='Y' THEN
3315 UTL_FILE.PUT_LINE(v_myfilehandle,'4 AFTER the Register Code CURSOR');
3316 END IF;
3317 OPEN fin_year_cur(v_organization_id);
3318 FETCH fin_year_cur INTO v_fin_year;
3319 CLOSE fin_year_cur;
3320 IF v_debug_flag ='Y' THEN
3321 UTL_FILE.PUT_LINE(v_myfilehandle,'5 AFTER the Financial Year CURSOR');
3322 END IF;
3323 IF v_delivery_id <> -1 THEN
3324 v_old_register := NULL;
3325 v_old_excise_invoice_no := NULL;
3326 IF v_old_register IS NULL THEN
3327 v_reg_type := NULL;
3328 v_rg_type := NULL;
3329 v_exc_invoice_no := NULL;
3330 v_tot_basic_ed_amt := v_basic_excise_duty_amount ;
3331 v_tot_addl_ed_amt := v_add_excise_duty_amount;
3332 v_tot_oth_ed_amt := v_oth_excise_duty_amount;
3333 v_tot_excise_amt := v_excise_amount;
3334 IF NVL(v_bonded_flag,'Y') = 'Y'
3335 AND ( NVL(v_tot_excise_amt,0) > 0 OR v_excise_exempt_type IS NOT NULL ) THEN
3336 IF v_debug_flag ='Y' THEN
3337 UTL_FILE.PUT_LINE(v_myfilehandle,'6 BEFORE the Preference cursors');
3338 END IF;
3339 --Changed by Nagaraj.s for Enh#2415656
3340 OPEN pref_cur(v_organization_id, v_location_id);
3341 FETCH pref_cur INTO v_pref_rg23a, v_pref_rg23c, v_pref_pla,v_export_oriented_unit;
3342 CLOSE pref_cur;
3343 ----Changed by Nagaraj.s for Enh#2415656
3344 IF v_debug_flag ='Y' THEN
3345 UTL_FILE.PUT_LINE(v_myfilehandle,'7 BEFORE the RG Balance CURSOR');
3346 END IF;
3347 OPEN rg_bal_cur(v_organization_id, v_location_id);
3348 FETCH rg_bal_cur INTO v_rg23a_balance, v_rg23c_balance, v_pla_balance,
3349 v_basic_pla_balance,v_additional_pla_balance,v_other_pla_balance;
3350 CLOSE rg_bal_cur;
3351 IF v_debug_flag ='Y' THEN
3352 UTL_FILE.PUT_LINE(v_myfilehandle,'8 BEFORE the SSI Unit Flag CURSOR');
3353 END IF;
3354 OPEN ssi_unit_flag_cur(v_organization_id, v_location_id);
3355 FETCH ssi_unit_flag_cur INTO v_ssi_unit_flag;
3356 CLOSE ssi_unit_flag_cur;
3357 IF v_debug_flag ='Y' THEN
3358 UTL_FILE.PUT_LINE(v_myfilehandle,'9 BEFORE the Register Code CURSOR');
3359 END IF;
3360
3361
3362 -- added by sriram - bug # 3021588
3363
3364 jai_cmn_bond_register_pkg.GET_REGISTER_ID(v_organization_id,
3365 v_location_id,
3366 v_order_type_id,
3367 'Y',
3368 v_asst_register_id,
3369 v_register_code
3370 );
3371
3372 -- call to the jai_cmn_bond_register_pkg package has been included instead of using the cursors
3373 -- to get the bond register balance info and other bond register details
3374
3375 /*OPEN register_code_cur(v_organization_id, v_location_id, v_order_type_id);
3376 FETCH register_code_cur INTO v_register_code;
3377 CLOSE register_code_cur;
3378 */
3379
3380
3381 IF NVL(v_register_code,'N') IN ('DOMESTIC_EXCISE','EXPORT_EXCISE') THEN
3382 OPEN get_item_dtls(v_organization_id,v_inventory_item_id);
3383 FETCH get_item_dtls INTO v_excise_flag,v_item_class;
3384 CLOSE get_item_dtls;
3385 IF NVL(v_excise_flag,'N') = 'Y' THEN
3386 IF NVL(v_excise_exempt_type, '@@@') NOT IN ('CT2', 'EXCISE_EXEMPT_CERT',
3387 'CT2_OTH', 'EXCISE_EXEMPT_CERT_OTH','CT3' ) THEN
3388 --***************************************************************************************************
3389 --Calling the Function by Nagaraj.s for Enh#2415656............................
3390 IF v_debug_flag ='Y' THEN
3391 UTL_FILE.PUT_LINE(v_myfilehandle,'10 BEFORE the jai_om_wsh_processing_pkg.excise_balance_check FUNCTION');
3392 UTL_FILE.FCLOSE(v_myfilehandle);
3393 END IF;
3394
3395 open c_cess_amount(v_delivery_id);
3396 fetch c_cess_amount into ln_cess_amount;
3397 close c_cess_amount;
3398
3399 -- start Bgowrava for forward porting bug#5989740
3400 open c_sh_cess_amount(v_delivery_id);
3401 fetch c_sh_cess_amount into ln_sh_cess_amount;
3402 close c_sh_cess_amount;
3403
3404 -- end Bgowrava for forward porting bug#5989740
3405
3406
3407 v_reg_type:= jai_om_wsh_processing_pkg.excise_balance_check
3408 (v_pref_rg23a,
3409 v_pref_rg23c,
3410 v_pref_pla,
3411 NVL(v_ssi_unit_flag,'N'),
3412 v_tot_excise_amt,
3413 v_rg23a_balance,
3414 v_rg23c_balance,
3415 v_pla_balance,
3416 v_basic_pla_balance,
3417 v_additional_pla_balance,
3418 v_other_pla_balance,
3419 v_basic_excise_duty_amount,
3420 v_add_excise_duty_amount ,
3421 v_oth_excise_duty_amount,
3422 v_export_oriented_unit,
3423 v_register_code,
3424 v_delivery_id ,
3425 v_organization_id,
3426 v_location_id ,
3427 ln_cess_amount ,
3428 ln_sh_cess_amount , --Bgowrava for forward porting bug#5989740
3429 lv_process_flag ,
3430 lv_process_message
3431 );
3432 --Ends here......................................
3433 IF v_debug_flag ='Y' THEN
3434 v_myfilehandle := UTL_FILE.FOPEN(v_utl_location,'ja_in_wsh_dlry_dtls_au_trg.LOG','A');
3435 UTL_FILE.PUT_LINE(v_myfilehandle,'11 AFTER the jai_om_wsh_processing_pkg.excise_balance_check FUNCTION v_reg_type -> ' || v_reg_type);
3436 END IF;
3437 --***************************************************************************************************
3438 ELSE
3439 OPEN get_item_dtls(v_organization_id,v_inventory_item_id);
3440 FETCH get_item_dtls INTO v_excise_flag,v_item_class;
3441 CLOSE get_item_dtls;
3442 IF v_item_class NOT IN ('OTIN', 'OTEX') THEN
3443 IF v_excise_exempt_type IN ('CT2_OTH', 'EXCISE_EXEMPT_CERT_OTH' ) THEN
3444 OPEN Get_Tax_Lines_Details_Cur1;
3445 FETCH Get_Tax_Lines_Details_Cur1 INTO v_modvat_tax_rate,v_rounding_factor;
3446 CLOSE Get_Tax_Lines_Details_Cur1;
3447 ELSE
3448 OPEN for_modvat_percentage(v_organization_id, v_location_id);
3449 FETCH for_modvat_percentage INTO v_modvat_tax_rate;
3450 CLOSE for_modvat_percentage;
3451 END IF;
3452 v_exempt_bal := (NVL(v_exempt_bal, 0) + v_shipped_quantity * v_assessable_value * NVL(v_modvat_tax_rate,0))/100;
3453 --*********************************************************************************************************
3454 IF v_debug_flag ='Y' THEN
3455 UTL_FILE.PUT_LINE(v_myfilehandle,'12 BEFORE the jai_om_wsh_pkg.get_excise_register_with_bal FUNCTION v_exempt_bal -> '|| v_exempt_bal);
3456 END IF;
3457 --Calling the Function by Nagaraj.s for Enh#2415656............................
3458
3459 /*
3460 Following cursor added by ssumaith - bug#4171272
3461 */
3462
3463 open c_cess_amount(v_delivery_id);
3464 fetch c_cess_amount into ln_cess_amount;
3465 close c_cess_amount;
3466
3467
3468 -- start Bgowrava for forward porting bug#5989740
3469
3470 open c_sh_cess_amount(v_delivery_id);
3471 fetch c_sh_cess_amount into ln_sh_cess_amount;
3472 close c_sh_cess_amount;
3473 -- end Bgowrava for forward porting bug#5989740
3474
3475
3476 v_reg_type := jai_om_wsh_pkg.get_excise_register_with_bal
3477 (v_pref_rg23a,
3478 v_pref_rg23c,
3479 v_pref_pla,
3480 NVL(v_ssi_unit_flag,'N'),
3481 v_exempt_bal,
3482 v_rg23a_balance,
3483 v_rg23c_balance,
3484 v_pla_balance,
3485 v_basic_pla_balance,
3486 v_additional_pla_balance,
3487 v_other_pla_balance,
3488 v_basic_excise_duty_amount,
3489 v_add_excise_duty_amount ,
3490 v_oth_excise_duty_amount,
3491 v_export_oriented_unit,
3492 v_register_code,
3493 v_delivery_id,
3494 v_organization_id,
3495 v_location_id ,
3496 ln_cess_amount ,
3497 ln_sh_cess_amount , --Bgowrava for forward porting bug#5989740
3498 lv_process_flag ,
3499 lv_process_message
3500 );
3501 --Ends here......................................
3502 IF v_debug_flag ='Y' THEN
3503 UTL_FILE.PUT_LINE(v_myfilehandle,'13 AFTER the jai_om_wsh_pkg.get_excise_register_with_bal FUNCTION v_reg_type -> '|| v_reg_type);
3504 END IF;
3505 --*********************************************************************************************************
3506 v_basic_ed_amt := v_exempt_bal;
3507 v_tot_basic_ed_amt := NVL(v_tot_basic_ed_amt,0) + v_exempt_bal;
3508 v_remarks := 'Against Modvat Recovery'||'-'||v_excise_exempt_refno;
3509 IF v_debug_flag ='Y' THEN
3510 UTL_FILE.PUT_LINE(v_myfilehandle,'14 v_basic_ed_amt -> '|| v_basic_ed_amt||', v_tot_basic_ed_amt -> '|| v_tot_basic_ed_amt);
3511 UTL_FILE.FCLOSE(v_myfilehandle);
3512 END IF;
3513 END IF;
3514 END IF;
3515 END IF;
3516 ELSIF NVL(v_register_code,'N') IN ('BOND_REG') THEN
3517 v_bond_tax_amount := NVL(v_tot_excise_amt,0) + NVL(v_bond_tax_amount,0);
3518
3519 -- commenting the following cursor definition as balance already fetched using the call to bond register package
3520
3521 /*OPEN register_balance_cur(v_organization_id, v_location_id);
3522 FETCH register_balance_cur INTO v_register_balance;
3523 CLOSE register_balance_cur;
3524 */
3525
3526 jai_cmn_bond_register_pkg.GET_REGISTER_DETAILS(v_asst_register_id,
3527 v_register_balance,
3528 v_reg_exp_date ,
3529 v_lou_flag);
3530
3531
3532 -- added logic to check if the register validity is ok
3533 IF nvl(v_reg_exp_date,sysdate) < sysdate then
3534 /* RAISE_APPLICATION_ERROR(-20122, 'Bonded Register Validity has Expired on ' || v_reg_exp_date);
3535 */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'Bonded Register Validity has Expired on ' || v_reg_exp_date ; return ;
3536 end if;
3537 -- added the letter of undertaking comparison in the following if.
3538 -- logic is : to check if it is a letter of undertaking and only then if balance is a problem , raise an error
3539 v_tot_excise_amt := v_tot_excise_amt * v_curr_conv_rate; /* ssumaith - bug#6487667*/
3540 IF NVL(v_register_balance,0) < NVL(v_tot_excise_amt,0) and nvl(v_lou_flag,'N') = 'N' THEN
3541 /* RAISE_APPLICATION_ERROR(-20120, 'Bonded Register Has Balance -> '
3542 || TO_CHAR(v_register_balance) || ' ,which IS less than Excisable Amount -> '
3543 || TO_CHAR(v_tot_excise_amt)); */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'Bonded Register Has Balance -> '|| TO_CHAR(v_register_balance) || ' ,which IS less than Excisable Amount -> '
3544 || TO_CHAR(v_tot_excise_amt) ; return ;
3545 END IF;
3546 END IF;
3547 END IF;
3548 END IF;
3549 END IF;
3550 -- end
3551 ---------------------------------------------------------------------------------------
3552 Exception
3553 When Others then
3554 /* raise_application_error (-20001,substr(sqlerrm,1,200)); */ pv_return_code := jai_constants.expected_error ; pv_return_message := substr(sqlerrm,1,200) ; return ;
3555
3556 END ARU_T3 ;
3557
3558 PROCEDURE ARU_T4 ( pr_old t_rec%type , pr_new t_rec%type , pv_action varchar2 , pv_return_code out nocopy varchar2 , pv_return_message out nocopy varchar2 ) IS
3559
3560 CURSOR c_check_lc_order
3561 IS
3562 SELECT
3563 lc_flag
3564 FROM
3565 JAI_OM_OE_SO_LINES
3566 WHERE
3567 lc_flag = 'Y' AND
3568 header_id = pr_new.source_header_id;
3569
3570 v_check_lc_order VARCHAR2(1);
3571 ln_cnt NUMBER;
3572
3573
3574 BEGIN
3575 pv_return_code := jai_constants.successful ;
3576
3577
3578 OPEN c_check_lc_order;
3579 FETCH c_check_lc_order INTO v_check_lc_order;
3580 CLOSE c_check_lc_order;
3581
3582 IF NVL(v_check_lc_order,'N') = 'Y' THEN
3583
3584 IF nvl(pr_new.SHIPPED_QUANTITY,0) <> nvl(pr_old.SHIPPED_QUANTITY,0) THEN
3585
3586 Select count(delivery_detail_id) into ln_cnt
3587 from jai_wsh_del_details_gt
3588 Where delivery_detail_id = pr_new.delivery_detail_id;
3589
3590 IF nvl(ln_cnt,0) > 0 THEN
3591 Update jai_wsh_del_details_gt
3592 set shipped_quantity = pr_new.shipped_quantity
3593 where delivery_detail_id = pr_new.delivery_detail_id;
3594
3595 ELSE
3596 Insert into jai_wsh_del_details_gt(
3597 delivery_detail_id,
3598 organization_id ,
3599 inventory_item_id,
3600 source_header_type_id,
3601 shipped_quantity,
3602 source_header_id,
3603 source_line_id,
3604 SPLIT_FROM_DELIVERY_DETAIL_ID,
3605 processed_flag)
3606 Values
3607 (
3608 pr_new.delivery_detail_id,
3609 pr_new.organization_id ,
3610 pr_new.inventory_item_id,
3611 pr_new.source_header_type_id,
3612 pr_new.shipped_quantity,
3613 pr_new.source_header_id,
3614 pr_new.source_line_id,
3615 pr_new.SPLIT_FROM_DELIVERY_DETAIL_ID,
3616 'N'
3617 );
3618 END IF;
3619 END IF;
3620 END IF;
3621 END ARU_T4 ;
3622
3623 /* REM +======================================================================+
3624 REM NAME RG23D_REV_ACCOUNTING
3625 REM
3626 REM DESCRIPTION Called from trigger JAI_OM_WDD_ARIUD_T1
3627 REM
3628 REM NOTES Passing the accounting entries in the sub-ledger table
3629 REM jai_cmn_journal_entries.
3630 REM +======================================================================+
3631 */
3632
3633 /* Changes for Bug 10213327 by abezgam START*/
3634 /* Added the following procedure TO REVERSE THE RG 23 D ENTRY IN GL
3635 refer to bug 10213327 and 8280837 for additional details*/
3636
3637 PROCEDURE RG23D_REV_ACCOUNTING
3638 (
3639 pr_trig_row IN wsh_delivery_details%ROWTYPE,
3640 pv_return_code out nocopy VARCHAR2,
3641 pv_return_message out nocopy VARCHAR2
3642 )
3643 IS
3644 lv_return_code VARCHAR2(100);
3645 lv_return_message VARCHAR2(2000);
3646
3647 CURSOR c_get_il_setup IS
3648 SELECT trading, excise_rcvble_account, excise_paid_account, excise_23d_account, excise_in_rg23d, order_price_excise_inclusive,
3649 cess_paid_payable_account_id, sh_cess_paid_payable_acct_id
3650 FROM JAI_CMN_INVENTORY_ORGS
3651 WHERE organization_id = pr_trig_row.organization_id
3652 AND location_id = pr_trig_row.ship_from_location_id;
3653
3654 CURSOR c_get_cost_of_goods_ac IS
3655 SELECT cost_of_sales_account
3656 FROM mtl_parameters
3657 WHERE organization_id = pr_trig_row.organization_id;
3658
3659 CURSOR c_matched_receipts IS
3660 SELECT receipt_id,receipt_quantity_applied
3661 FROM JAI_CMN_MATCH_RECEIPTS
3662 WHERE ref_line_id = pr_trig_row.delivery_detail_id;
3663
3664 CURSOR c_rg23d_details(p_register_id NUMBER) IS
3665 SELECT closing_balance_qty - opening_balance_qty receipt_quantity,
3666 Nvl(basic_ed,0)+Nvl(additional_ed,0)+Nvl(other_ed,0) duty_amount,
3667 transaction_uom_code
3668 FROM JAI_CMN_RG_23D_TRXS
3669 WHERE register_id = p_register_id;
3670
3671 CURSOR c_cess_details(p_register_id NUMBER) IS
3672 SELECT credit
3673 FROM JAI_CMN_RG_OTHERS
3674 WHERE source_register='RG23D'
3675 AND source_register_id=p_register_id
3676 AND tax_type='EXCISE_EDUCATION_CESS';
3677
3678 CURSOR c_sh_cess_details(p_register_id NUMBER) IS
3679 SELECT credit
3680 FROM JAI_CMN_RG_OTHERS
3681 WHERE source_register='RG23D'
3682 AND source_register_id=p_register_id
3683 AND tax_type='EXCISE_SH_EDU_CESS';
3684
3685 CURSOR c_get_delivery_id IS
3686 SELECT To_Char(delivery_id)
3687 FROM wsh_delivery_assignments
3688 WHERE delivery_detail_id = pr_trig_row.delivery_detail_id;
3689
3690 /*
3691 Get the Currency code using the current org id from the table hr_operating_units and gl_sets_of_books
3692 */
3693
3694 CURSOR Sob_Cur
3695 IS
3696 SELECT gsb.currency_code
3697 FROM hr_operating_units hru ,
3698 gl_sets_of_books gsb
3699 WHERE gsb.set_of_books_id = hru.set_of_books_id AND
3700 hru.organization_id = pr_trig_row.org_id ;
3701
3702 CURSOR c_curr_precision (cp_curr_code VARCHAR2) IS
3703 SELECT Nvl(PRECISION,2)
3704 FROM gl_currencies
3705 WHERE currency_code=cp_curr_code;
3706
3707 /*Added following cursor for Bug#10213327 by abezgam*/
3708 CURSOR c_inclusive_flag (CP_DDID IN NUMBER )IS
3709 SELECT count(*)
3710 FROM jai_om_wsh_line_taxes a,jai_cmn_taxes_all b
3711 where a.delivery_Detail_id=cp_ddid
3712 and a.tax_id=b.tax_id
3713 and upper(b.tax_type) like '%EXCISE%'
3714 AND B.INCLUSIVE_TAX_FLAG='Y';
3715
3716 lv_inclusive_tax_flag NUMBER ; --Added by abezgam for bug#10213327
3717 ln_precision NUMBER;
3718
3719 v_currency_code GL_SETS_OF_BOOKS.CURRENCY_CODE%TYPE;
3720 v_set_of_books_id NUMBER;
3721 r_il_setup c_get_il_setup%ROWTYPE;
3722 r_rg23d_details c_rg23d_details%ROWTYPE;
3723 ln_cost_of_goods_sold_ac mtl_parameters.cost_of_sales_account%TYPE;
3724 ln_cost_of_goods_ac NUMBER;
3725 ln_excise_amt NUMBER;
3726 ln_temp_amt NUMBER;
3727 ln_cess_amt NUMBER;
3728 ln_sh_cess_amt NUMBER;
3729 ln_qty_ratio NUMBER;
3730 lv_source_name VARCHAR2(100) := 'Register India';
3731 lv_category_name VARCHAR2(100) := 'Register India';
3732 lv_reference_10 varchar2(100) := 'India Localization - RG23D Accounting Reversal on Shipment';
3733 lv_reference_23 varchar2(100) := 'jai_rg23d_shipment_acc_pkg.rg23d_rev_accounting';
3734 lv_reference_24 varchar2(100) := 'wsh_new_deliveries';
3735 lv_reference_26 varchar2(100) := To_Char(pr_trig_row.delivery_detail_id);
3736 lv_reference_25 varchar2(100) := 'delivery_id';
3737 lv_regime_code VARCHAR2(100) := 'EXCISE';
3738 lv_source_table VARCHAR2(100) := 'wsh_delivery_details';
3739
3740 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
3741
3742 BEGIN
3743
3744 OPEN c_get_il_setup;
3745 FETCH c_get_il_setup INTO r_il_setup;
3746 CLOSE c_get_il_setup;
3747
3748 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
3749 (p_org_id => pr_trig_row.organization_id);
3750 v_set_of_books_id := l_func_curr_det.ledger_id;
3751 v_currency_code := l_func_curr_det.currency_code;
3752 IF Nvl(r_il_setup.trading,'N')='Y' AND Nvl(r_il_setup.excise_in_rg23d,'N')='Y' /*reversing to be done only if this setup is present. not necessary otherwise*/
3753 THEN
3754 ln_excise_amt := 0;
3755 ln_cess_amt := 0;
3756 ln_sh_cess_amt := 0;
3757
3758 OPEN c_get_delivery_id;
3759 FETCH c_get_delivery_id INTO lv_reference_26;
3760 CLOSE c_get_delivery_id;
3761
3762 lv_reference_10 := lv_reference_10||' for delivery_id '||lv_reference_26;
3763 FOR match_rec IN c_matched_receipts
3764 LOOP
3765 OPEN c_rg23d_details(match_rec.receipt_id);
3766 FETCH c_rg23d_details INTO r_rg23d_details;
3767 CLOSE c_rg23d_details;
3768 ln_qty_ratio := Nvl(match_rec.receipt_quantity_applied,0) / Nvl(r_rg23d_details.receipt_quantity,1);
3769 --ln_qty_ratio := ln_qty_ratio * uom_convert(r_rg23d_details.transaction_uom_code);
3770 ln_excise_amt := ln_excise_amt + Nvl(r_rg23d_details.duty_amount * ln_qty_ratio,0);
3771
3772 OPEN c_cess_details(match_rec.receipt_id);
3773 FETCH c_cess_details INTO ln_temp_amt;
3774 CLOSE c_cess_details;
3775
3776
3777 ln_cess_amt := ln_cess_amt + Nvl(ln_temp_amt * ln_qty_ratio,0);
3778 ln_temp_amt := 0;
3779
3780 OPEN c_sh_cess_details(match_rec.receipt_id);
3781 FETCH c_sh_cess_details INTO ln_temp_amt;
3782 CLOSE c_sh_cess_details;
3783
3784 ln_sh_cess_amt := ln_sh_cess_amt + Nvl(ln_temp_amt * ln_qty_ratio,0);
3785 ln_temp_amt := 0;
3786
3787 END LOOP;
3788 /* rounding logic*/
3789
3790 OPEN Sob_Cur;
3791 FETCH Sob_Cur INTO v_currency_code;
3792 CLOSE Sob_Cur;
3793
3794 OPEN c_curr_precision(v_currency_code);
3795 FETCH c_curr_precision INTO ln_precision;
3796 CLOSE c_curr_precision;
3797
3798 ln_excise_amt := Round(ln_excise_amt, ln_precision);
3799 ln_cess_amt := Round(ln_cess_amt, ln_precision);
3800 ln_sh_cess_amt := Round(ln_sh_cess_amt, ln_precision);
3801
3802
3803 ln_temp_amt := ln_excise_amt + ln_cess_amt + ln_sh_cess_amt;
3804
3805
3806 OPEN c_get_cost_of_goods_ac;
3807 FETCH c_get_cost_of_goods_ac INTO ln_cost_of_goods_ac;
3808 CLOSE c_get_cost_of_goods_ac;
3809 -- pass a/c entry for 'debit cost of goods sold account'
3810
3811 OPEN c_inclusive_flag(pr_trig_row.delivery_detail_id);
3812 FETCH c_inclusive_flag INTO lv_inclusive_tax_flag ;
3813 CLOSE c_inclusive_flag;
3814
3815 IF lv_inclusive_tax_flag >0 --If condition Added for bug #10213327
3816 THEN
3817 jai_cmn_gl_pkg.create_gl_entry
3818 (p_organization_id => pr_trig_row.organization_id ,
3819 p_currency_code => v_currency_code ,
3820 p_credit_amount => NULL ,
3821 --p_debit_amount => ln_temp_amt ,
3822 --p_cc_id => ln_cost_of_goods_ac ,
3823 /*Commented aove two and below two by mmurtuza for bug 16022303*/
3824 p_debit_amount => ln_excise_amt ,
3825 p_cc_id => r_il_setup.excise_rcvble_account ,
3826 p_je_source_name => lv_source_name ,
3827 p_je_category_name => lv_category_name ,
3828 p_created_by => pr_trig_row.created_by ,
3829 p_accounting_date => trunc(sysdate) ,
3830 p_currency_conversion_date => NULL ,
3831 p_currency_conversion_type => NULL ,
3832 p_currency_conversion_rate => NULL ,
3833 p_reference_10 => lv_reference_10 ,
3834 p_reference_23 => lv_reference_23 ,
3835 p_reference_24 => lv_reference_24 ,
3836 p_reference_25 => lv_reference_25 ,
3837 p_reference_26 => lv_reference_26
3838 );
3839
3840 INSERT INTO JAI_CMN_JOURNAL_ENTRIES
3841 (JOURNAL_ENTRY_ID,
3842 regime_code,
3843 organization_id,
3844 set_of_books_id,
3845 tax_type,
3846 period_name,
3847 code_combination_id,
3848 accounted_dr,
3849 accounted_cr,
3850 transaction_date,
3851 source,
3852 source_table_name,
3853 source_trx_id,
3854 reference_name,
3855 reference_id,
3856 repository_id,/* added by ssawant for bug 5879769 */
3857 currency_code,
3858 curr_conv_rate,
3859 creation_date,
3860 created_by,
3861 last_update_date,
3862 last_updated_by,
3863 last_update_login
3864 )
3865 VALUES
3866 (JAI_CMN_JOURNAL_ENTRIES_S.nextval,
3867 lv_regime_code,
3868 pr_trig_row.organization_id,
3869 v_set_of_books_id,
3870 NULL,
3871 NULL,
3872 --ln_cost_of_goods_ac,
3873 --ln_temp_amt,
3874 /*Commented aove two and below two by mmurtuza for bug 16022303*/
3875 r_il_setup.excise_rcvble_account,
3876 ln_excise_amt,
3877 NULL,
3878 SYSDATE,
3879 lv_source_name ,
3880 lv_source_table,
3881 pr_trig_row.delivery_detail_id,
3882 lv_reference_25,
3883 lv_reference_26,
3884 NULL,/* added by ssawant for bug 5879769 */
3885 v_currency_code,
3886 NULL,
3887 sysdate,
3888 FND_GLOBAL.user_id,
3889 sysdate,
3890 fnd_global.user_id,
3891 fnd_global.login_id
3892 );
3893
3894 /*Start additions by mmurtuza for bug 16022303*/
3895
3896 -- passing a/c entry for 'debit cess paid/payable account with cess amount'
3897 jai_cmn_gl_pkg.create_gl_entry
3898 (p_organization_id => pr_trig_row.organization_id ,
3899 p_currency_code => v_currency_code ,
3900 p_credit_amount => NULL ,
3901 p_debit_amount => ln_cess_amt ,
3902 p_cc_id => r_il_setup.cess_paid_payable_account_id ,
3903 p_je_source_name => lv_source_name ,
3904 p_je_category_name => lv_category_name ,
3905 p_created_by => pr_trig_row.created_by ,
3906 p_accounting_date => trunc(sysdate) ,
3907 p_currency_conversion_date => NULL ,
3908 p_currency_conversion_type => NULL ,
3909 p_currency_conversion_rate => NULL ,
3910 p_reference_10 => lv_reference_10 ,
3911 p_reference_23 => lv_reference_23 ,
3912 p_reference_24 => lv_reference_24 ,
3913 p_reference_25 => lv_reference_25 ,
3914 p_reference_26 => lv_reference_26
3915 );
3916
3917 INSERT INTO JAI_CMN_JOURNAL_ENTRIES
3918 (JOURNAL_ENTRY_ID,
3919 regime_code,
3920 organization_id,
3921 set_of_books_id,
3922 tax_type,
3923 period_name,
3924 code_combination_id,
3925 accounted_dr,
3926 accounted_cr,
3927 transaction_date,
3928 source,
3929 source_table_name,
3930 source_trx_id,
3931 reference_name,
3932 reference_id,
3933 repository_id,/* added by ssawant for bug 5879769 */
3934 currency_code,
3935 curr_conv_rate,
3936 creation_date,
3937 created_by,
3938 last_update_date,
3939 last_updated_by,
3940 last_update_login
3941 )
3942 VALUES
3943 (JAI_CMN_JOURNAL_ENTRIES_S.nextval,
3944 lv_regime_code,
3945 pr_trig_row.organization_id,
3946 v_set_of_books_id,
3947 NULL,
3948 NULL,
3949 r_il_setup.cess_paid_payable_account_id,
3950 ln_cess_amt,
3951 NULL,
3952 SYSDATE,
3953 lv_source_name ,
3954 lv_source_table,
3955 pr_trig_row.delivery_detail_id,
3956 lv_reference_25,
3957 lv_reference_26,
3958 NULL,/* added by ssawant for bug 5879769 */
3959 v_currency_code,
3960 NULL,
3961 sysdate,
3962 FND_GLOBAL.user_id,
3963 sysdate,
3964 fnd_global.user_id,
3965 fnd_global.login_id
3966 );
3967
3968 -- passing a/c entry for 'debit she cess paid/payable account with she cess amount'
3969 jai_cmn_gl_pkg.create_gl_entry
3970 (p_organization_id => pr_trig_row.organization_id ,
3971 p_currency_code => v_currency_code ,
3972 p_credit_amount => NULL ,
3973 p_debit_amount => ln_sh_cess_amt ,
3974 p_cc_id => r_il_setup.sh_cess_paid_payable_acct_id ,
3975 p_je_source_name => lv_source_name ,
3976 p_je_category_name => lv_category_name ,
3977 p_created_by => pr_trig_row.created_by ,
3978 p_accounting_date => trunc(sysdate) ,
3979 p_currency_conversion_date => NULL ,
3980 p_currency_conversion_type => NULL ,
3981 p_currency_conversion_rate => NULL ,
3982 p_reference_10 => lv_reference_10 ,
3983 p_reference_23 => lv_reference_23 ,
3984 p_reference_24 => lv_reference_24 ,
3985 p_reference_25 => lv_reference_25 ,
3986 p_reference_26 => lv_reference_26
3987 );
3988
3989 INSERT INTO JAI_CMN_JOURNAL_ENTRIES
3990 (JOURNAL_ENTRY_ID,
3991 regime_code,
3992 organization_id,
3993 set_of_books_id,
3994 tax_type,
3995 period_name,
3996 code_combination_id,
3997 accounted_dr,
3998 accounted_cr,
3999 transaction_date,
4000 source,
4001 source_table_name,
4002 source_trx_id,
4003 reference_name,
4004 reference_id,
4005 repository_id,/* added by ssawant for bug 5879769 */
4006 currency_code,
4007 curr_conv_rate,
4008 creation_date,
4009 created_by,
4010 last_update_date,
4011 last_updated_by,
4012 last_update_login
4013 )
4014 VALUES
4015 (JAI_CMN_JOURNAL_ENTRIES_S.nextval,
4016 lv_regime_code,
4017 pr_trig_row.organization_id,
4018 v_set_of_books_id,
4019 NULL,
4020 NULL,
4021 r_il_setup.sh_cess_paid_payable_acct_id,
4022 ln_sh_cess_amt,
4023 NULL,
4024 SYSDATE,
4025 lv_source_name ,
4026 lv_source_table,
4027 pr_trig_row.delivery_detail_id,
4028 lv_reference_25,
4029 lv_reference_26,
4030 NULL,/* added by ssawant for bug 5879769 */
4031 v_currency_code,
4032 NULL,
4033 sysdate,
4034 FND_GLOBAL.user_id,
4035 sysdate,
4036 fnd_global.user_id,
4037 fnd_global.login_id
4038 );
4039
4040 /*End additions by mmurtuza for bug 16022303*/
4041
4042 -- pass a/c entry for 'credit rg23d account'
4043 jai_cmn_gl_pkg.create_gl_entry
4044 (p_organization_id => pr_trig_row.organization_id ,
4045 p_currency_code => v_currency_code ,
4046 p_credit_amount => ln_temp_amt ,
4047 p_debit_amount => NULL ,
4048 p_cc_id => r_il_setup.excise_23d_account ,
4049 p_je_source_name => lv_source_name ,
4050 p_je_category_name => lv_category_name ,
4051 p_created_by => pr_trig_row.created_by ,
4052 p_accounting_date => trunc(sysdate) ,
4053 p_currency_conversion_date => NULL ,
4054 p_currency_conversion_type => NULL ,
4055 p_currency_conversion_rate => NULL ,
4056 p_reference_10 => lv_reference_10 ,
4057 p_reference_23 => lv_reference_23 ,
4058 p_reference_24 => lv_reference_24 ,
4059 p_reference_25 => lv_reference_25 ,
4060 p_reference_26 => lv_reference_26
4061 );
4062
4063 INSERT INTO JAI_CMN_JOURNAL_ENTRIES
4064 (JOURNAL_ENTRY_ID,
4065 regime_code,
4066 organization_id,
4067 set_of_books_id,
4068 tax_type,
4069 period_name,
4070 code_combination_id,
4071 accounted_dr,
4072 accounted_cr,
4073 transaction_date,
4074 source,
4075 source_table_name,
4076 source_trx_id,
4077 reference_name,
4078 reference_id,
4079 repository_id,/* added by ssawant for bug 5879769 */
4080 currency_code,
4081 curr_conv_rate,
4082 creation_date,
4083 created_by,
4084 last_update_date,
4085 last_updated_by,
4086 last_update_login
4087 )
4088 VALUES
4089 (JAI_CMN_JOURNAL_ENTRIES_S.nextval,
4090 lv_regime_code,
4091 pr_trig_row.organization_id,
4092 v_set_of_books_id,
4093 NULL,
4094 NULL,
4095 r_il_setup.excise_23d_account,
4096 NULL,
4097 ln_temp_amt,
4098 SYSDATE,
4099 lv_source_name ,
4100 lv_source_table,
4101 pr_trig_row.delivery_detail_id,
4102 lv_reference_25,
4103 lv_reference_26,
4104 NULL,/* added by ssawant for bug 5879769 */
4105 v_currency_code,
4106 NULL,
4107 sysdate,
4108 FND_GLOBAL.user_id,
4109 sysdate,
4110 fnd_global.user_id,
4111 fnd_global.login_id
4112 );
4113
4114 ELSE
4115 -- pass a/c entry for 'debit excise paid/payable account'
4116 jai_cmn_gl_pkg.create_gl_entry
4117 (p_organization_id => pr_trig_row.organization_id ,
4118 p_currency_code => v_currency_code ,
4119 p_credit_amount => NULL ,
4120 p_debit_amount => ln_excise_amt ,
4121 -- p_cc_id => r_il_setup.excise_paid_account , /*Changed for Bug#10213327*/
4122 p_cc_id => r_il_setup.excise_rcvble_account , /*Changed for Bug#12807591*/
4123 p_je_source_name => lv_source_name ,
4124 p_je_category_name => lv_category_name ,
4125 p_created_by => pr_trig_row.created_by ,
4126 p_accounting_date => trunc(sysdate) ,
4127 p_currency_conversion_date => NULL ,
4128 p_currency_conversion_type => NULL ,
4129 p_currency_conversion_rate => NULL ,
4130 p_reference_10 => lv_reference_10 ,
4131 p_reference_23 => lv_reference_23 ,
4132 p_reference_24 => lv_reference_24 ,
4133 p_reference_25 => lv_reference_25 ,
4134 p_reference_26 => lv_reference_26
4135 );
4136
4137 INSERT INTO JAI_CMN_JOURNAL_ENTRIES
4138 (JOURNAL_ENTRY_ID,
4139 regime_code,
4140 organization_id,
4141 set_of_books_id,
4142 tax_type,
4143 period_name,
4144 code_combination_id,
4145 accounted_dr,
4146 accounted_cr,
4147 transaction_date,
4148 source,
4149 source_table_name,
4150 source_trx_id,
4151 reference_name,
4152 reference_id,
4153 repository_id,/* added by ssawant for bug 5879769 */
4154 currency_code,
4155 curr_conv_rate,
4156 creation_date,
4157 created_by,
4158 last_update_date,
4159 last_updated_by,
4160 last_update_login
4161 )
4162 VALUES
4163 (JAI_CMN_JOURNAL_ENTRIES_S.nextval,
4164 lv_regime_code,
4165 pr_trig_row.organization_id,
4166 v_set_of_books_id,
4167 NULL,
4168 NULL,
4169 r_il_setup.excise_rcvble_account, --bug#12807591
4170 -- r_il_setup.excise_paid_account, /*changed for Bug#10213327*/
4171 ln_excise_amt,
4172 NULL,
4173 SYSDATE,
4174 lv_source_name ,
4175 lv_source_table,
4176 pr_trig_row.delivery_detail_id,
4177 lv_reference_25,
4178 lv_reference_26,
4179 NULL,/* added by ssawant for bug 5879769 */
4180 v_currency_code,
4181 NULL,
4182 sysdate,
4183 FND_GLOBAL.user_id,
4184 sysdate,
4185 fnd_global.user_id,
4186 fnd_global.login_id
4187 );
4188
4189 -- pass a/c entry for 'debit cess paid/payable account'
4190 jai_cmn_gl_pkg.create_gl_entry
4191 (p_organization_id => pr_trig_row.organization_id ,
4192 p_currency_code => v_currency_code ,
4193 p_credit_amount => NULL ,
4194 p_debit_amount => ln_cess_amt ,
4195 p_cc_id => r_il_setup.cess_paid_payable_account_id ,
4196 p_je_source_name => lv_source_name ,
4197 p_je_category_name => lv_category_name ,
4198 p_created_by => pr_trig_row.created_by ,
4199 p_accounting_date => trunc(sysdate) ,
4200 p_currency_conversion_date => NULL ,
4201 p_currency_conversion_type => NULL ,
4202 p_currency_conversion_rate => NULL ,
4203 p_reference_10 => lv_reference_10 ,
4204 p_reference_23 => lv_reference_23 ,
4205 p_reference_24 => lv_reference_24 ,
4206 p_reference_25 => lv_reference_25 ,
4207 p_reference_26 => lv_reference_26
4208 );
4209
4210 INSERT INTO JAI_CMN_JOURNAL_ENTRIES
4211 (JOURNAL_ENTRY_ID,
4212 regime_code,
4213 organization_id,
4214 set_of_books_id,
4215 tax_type,
4216 period_name,
4217 code_combination_id,
4218 accounted_dr,
4219 accounted_cr,
4220 transaction_date,
4221 source,
4222 source_table_name,
4223 source_trx_id,
4224 reference_name,
4225 reference_id,
4226 repository_id,/* added by ssawant for bug 5879769 */
4227 currency_code,
4228 curr_conv_rate,
4229 creation_date,
4230 created_by,
4231 last_update_date,
4232 last_updated_by,
4233 last_update_login
4234 )
4235 VALUES
4236 (JAI_CMN_JOURNAL_ENTRIES_S.nextval,
4237 lv_regime_code,
4238 pr_trig_row.organization_id,
4239 v_set_of_books_id,
4240 NULL,
4241 NULL,
4242 r_il_setup.cess_paid_payable_account_id,
4243 ln_cess_amt,
4244 NULL,
4245 SYSDATE,
4246 lv_source_name ,
4247 lv_source_table,
4248 pr_trig_row.delivery_detail_id,
4249 lv_reference_25,
4250 lv_reference_26,
4251 NULL,/* added by ssawant for bug 5879769 */
4252 v_currency_code,
4253 NULL,
4254 sysdate,
4255 FND_GLOBAL.user_id,
4256 sysdate,
4257 fnd_global.user_id,
4258 fnd_global.login_id
4259 );
4260
4261 -- pass a/c entry for 'debit sh cess paid/payable account'
4262 jai_cmn_gl_pkg.create_gl_entry
4263 (p_organization_id => pr_trig_row.organization_id ,
4264 p_currency_code => v_currency_code ,
4265 p_credit_amount => NULL ,
4266 p_debit_amount => ln_sh_cess_amt ,
4267 p_cc_id => r_il_setup.sh_cess_paid_payable_acct_id ,
4268 p_je_source_name => lv_source_name ,
4269 p_je_category_name => lv_category_name ,
4270 p_created_by => pr_trig_row.created_by ,
4271 p_accounting_date => trunc(sysdate) ,
4272 p_currency_conversion_date => NULL ,
4273 p_currency_conversion_type => NULL ,
4274 p_currency_conversion_rate => NULL ,
4275 p_reference_10 => lv_reference_10 ,
4276 p_reference_23 => lv_reference_23 ,
4277 p_reference_24 => lv_reference_24 ,
4278 p_reference_25 => lv_reference_25 ,
4279 p_reference_26 => lv_reference_26
4280 );
4281
4282 INSERT INTO JAI_CMN_JOURNAL_ENTRIES
4283 (JOURNAL_ENTRY_ID,
4284 regime_code,
4285 organization_id,
4286 set_of_books_id,
4287 tax_type,
4288 period_name,
4289 code_combination_id,
4290 accounted_dr,
4291 accounted_cr,
4292 transaction_date,
4293 source,
4294 source_table_name,
4295 source_trx_id,
4296 reference_name,
4297 reference_id,
4298 repository_id,/* added by ssawant for bug 5879769 */
4299 currency_code,
4300 curr_conv_rate,
4301 creation_date,
4302 created_by,
4303 last_update_date,
4304 last_updated_by,
4305 last_update_login
4306 )
4307 VALUES
4308 (JAI_CMN_JOURNAL_ENTRIES_S.nextval,
4309 lv_regime_code,
4310 pr_trig_row.organization_id,
4311 v_set_of_books_id,
4312 NULL,
4313 NULL,
4314 r_il_setup.sh_cess_paid_payable_acct_id,
4315 ln_sh_cess_amt,
4316 NULL,
4317 SYSDATE,
4318 lv_source_name ,
4319 lv_source_table,
4320 pr_trig_row.delivery_detail_id,
4321 lv_reference_25,
4322 lv_reference_26,
4323 NULL,/* added by ssawant for bug 5879769 */
4324 v_currency_code,
4325 NULL,
4326 sysdate,
4327 FND_GLOBAL.user_id,
4328 sysdate,
4329 fnd_global.user_id,
4330 fnd_global.login_id
4331 );
4332
4333 -- pass a/c entry for 'credit rg23d account'
4334 jai_cmn_gl_pkg.create_gl_entry
4335 (p_organization_id => pr_trig_row.organization_id ,
4336 p_currency_code => v_currency_code ,
4337 p_credit_amount => ln_temp_amt ,
4338 p_debit_amount => NULL ,
4339 p_cc_id => r_il_setup.excise_23d_account ,
4340 p_je_source_name => lv_source_name ,
4341 p_je_category_name => lv_category_name ,
4342 p_created_by => pr_trig_row.created_by ,
4343 p_accounting_date => trunc(sysdate) ,
4344 p_currency_conversion_date => NULL ,
4345 p_currency_conversion_type => NULL ,
4346 p_currency_conversion_rate => NULL ,
4347 p_reference_10 => lv_reference_10 ,
4348 p_reference_23 => lv_reference_23 ,
4349 p_reference_24 => lv_reference_24 ,
4350 p_reference_25 => lv_reference_25 ,
4351 p_reference_26 => lv_reference_26
4352 );
4353
4354 INSERT INTO JAI_CMN_JOURNAL_ENTRIES
4355 (JOURNAL_ENTRY_ID,
4356 regime_code,
4357 organization_id,
4358 set_of_books_id,
4359 tax_type,
4360 period_name,
4361 code_combination_id,
4362 accounted_dr,
4363 accounted_cr,
4364 transaction_date,
4365 source,
4366 source_table_name,
4367 source_trx_id,
4368 reference_name,
4369 reference_id,
4370 repository_id,/* added by ssawant for bug 5879769 */
4371 currency_code,
4372 curr_conv_rate,
4373 creation_date,
4374 created_by,
4375 last_update_date,
4376 last_updated_by,
4377 last_update_login
4378 )
4379 VALUES
4380 (JAI_CMN_JOURNAL_ENTRIES_S.nextval,
4381 lv_regime_code,
4382 pr_trig_row.organization_id,
4383 v_set_of_books_id,
4384 NULL,
4385 NULL,
4386 r_il_setup.excise_23d_account,
4387 NULL,
4388 ln_temp_amt,
4389 SYSDATE,
4390 lv_source_name ,
4391 lv_source_table,
4392 pr_trig_row.delivery_detail_id,
4393 lv_reference_25,
4394 lv_reference_26,
4395 NULL,/* added by ssawant for bug 5879769 */
4396 v_currency_code,
4397 NULL,
4398 sysdate,
4399 FND_GLOBAL.user_id,
4400 sysdate,
4401 fnd_global.user_id,
4402 fnd_global.login_id
4403 );
4404
4405
4406 END IF;
4407 ELSE
4408 RETURN;
4409 END IF;
4410
4411 EXCEPTION
4412 WHEN OTHERS THEN
4413 pv_return_code := 'E';
4414 pv_return_message := 'An error occurred when passing RG23D accounting entries. Check the lines in GL Interface';
4415 END RG23D_REV_ACCOUNTING;
4416 /* Changes for Bug 10213327 by abezgam END*/
4417
4418 END JAI_OM_WDD_TRIGGER_PKG ;
[Home] [Help]