1 PACKAGE BODY JAI_OM_WDD_TRIGGER_PKG AS
2 /* $Header: jai_om_wdd_t.plb 120.9.12010000.3 2008/11/13 13:02:49 jmeena 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
55
56
57 Future Dependencies For the release Of this Object:-
58 (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/
59 A datamodel change )
60 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
61 Current Version Current Bug Dependent Files Version Author Date Remarks
62 Of File On Bug/Patchset Dependent On
63
64 ja_in_wsh_dlry_dtls_ad_trg.sql
65 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
66 115.1 4037690 IN60105D2 ja_in_util_pkg_s.sql 115.0 Ssumaith 29-Nov-2004 Call to this function.
67 ja_in_util_pkg_s.sql 115.0 Ssumaith
68
69 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
70 --------------------------------------------------------------------------------------------*/
71
72 /* following code added by ssumaith - bug# 4037690 */
73 -- if pr_new.org_id is not null then
74 -- 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
75 -- return;
76 -- end if;
77 -- end if;
78
79 /* ends here additions by ssumaith */
80
81 Open del_count;
82 Fetch del_count into x;
83 Close del_count;
84
85 If nvl(x,0) <> 1 then
86 Return;
87 Else
88 DELETE JAI_OM_WSH_LINES_ALL
89 WHERE delivery_detail_id = v_DELIVERY_DETAIL_ID;
90
91 DELETE JAI_OM_WSH_LINE_TAXES
92 WHERE delivery_detail_id = v_DELIVERY_DETAIL_ID;
93 End If;
94
95 END ARD_T1 ;
96
97 /*
98 REM +======================================================================+
99 REM NAME ARU_T1
100 REM
101 REM DESCRIPTION Called from trigger JAI_OM_WDD_ARIUD_T1
102 REM
103 REM NOTES Refers to old trigger JAI_OM_WDD_ARU_T1
104 REM
105 REM +======================================================================+
106 */
107 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
108 BEGIN
109 pv_return_code := jai_constants.successful ;
110 /*------------------------------------------------------------------------------------------
111 FILENAME: ja_in_receipts_match_trg.sql
112
113 CHANGE HISTORY:
114 S.No Date Author and Details
115 1. 2002/03/11 Vijay
116 Trigger written to delete data from tables
117 JAI_CMN_MATCH_RECEIPTS, JAI_CMN_MATCH_TAXES.
118 After Match Receipt is done, when back order is done,
119 this trigger enables to match again.
120
121 2. 29/11/2005 Aparajita for bug#4036241. Version#115.1
122
123 Introduced the call to centralized packaged procedure,
124 jai_cmn_utils_pkg.check_jai_exists to check if localization has been installed.
125
126 3. 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
127 DB Entity as required for CASE COMPLAINCE. Version 116.1
128
129 4. 13-Jun-2005 Ramananda for bug#4428980. File Version: 116.2
130 Removal of SQL LITERALs is done
131 Dependency:
132 ----------
133
134 Sl No. Bug Dependent on
135 Bug/Patch set Details
136 -------------------------------------------------------------------------------------------------
137 1 4036241 4033992 Call to jai_cmn_utils_pkg.check_jai_exists, whcih was created thru bug
138 4033992.
139 ja_in_util_pkg_s.sql 115.0
140 ja_in_util_pkg_b.sql 115.0
141
142 --------------------------------------------------------------------------------------------------*/
143 --if
144 -- jai_cmn_utils_pkg.check_jai_exists (p_calling_object => 'JA_IN_WSH_BACKORDER_AU_TRG', p_inventory_orgn_id => pr_new.organization_id)
145 -- =
146 -- FALSE
147 --then
148 /* India Localization funtionality is not required */
149 -- return;
150 --end if;
151
152
153 Delete from JAI_CMN_MATCH_RECEIPTS
154 Where ref_line_id = pr_new.delivery_detail_id;
155
156 Delete from JAI_CMN_MATCH_TAXES
157 Where ref_line_id = pr_new.delivery_detail_id;
158 END ARU_T1 ;
159
160 /*
161 REM +======================================================================+
162 REM NAME ARU_T2
163 REM
164 REM DESCRIPTION Called from trigger JAI_OM_WDD_ARIUD_T1
165 REM
166 REM NOTES Refers to old trigger JAI_OM_WDD_ARU_T2
167 REM
168 REM +======================================================================+
169 */
170 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
171 v_inventory_item_id NUMBER; --File.Sql.35 Cbabu :=pr_new.inventory_item_id ;
172 v_organization_id NUMBER ; --File.Sql.35 Cbabu :=pr_new.organization_id ;
173 v_subinventory VARCHAR2(10); --File.Sql.35 Cbabu :=pr_new.subinventory ;
174 v_delivery_detail_id NUMBER; --File.Sql.35 Cbabu :=pr_new.delivery_detail_id ;
175 v_source_header_type_id NUMBER ; --File.Sql.35 Cbabu :=pr_new.source_header_type_id ;
176 v_shipped_quantity NUMBER ; --File.Sql.35 Cbabu := nvl(pr_new.shipped_quantity,0) ;
177 v_matched_qty NUMBER ; --File.Sql.35 Cbabu := 0 ;
178 v_trading_flag VARCHAR2(1) ;
179 v_trad_register_code VARCHAR2(30) ;
180 v_item_trading_flag VARCHAR2(1) ;
181 v_location_id NUMBER ;
182 v_exe_flag VARCHAR2(150) ;
183 v_mod_flag VARCHAR2(150) ;
184 v_container_item_flag mtl_system_items.container_item_flag%type ;
185
186
187 /*
188 ||Added by bgowrava for the forward porting bug 5631784 (TCS enhancement)
189 */
190
191 /* variables used in debug package */
192 lv_object_name jai_cmn_debug_contexts.LOG_CONTEXT%TYPE ;
193 lv_member_name jai_cmn_debug_contexts.LOG_CONTEXT%TYPE;
194 lv_context jai_cmn_debug_contexts.LOG_CONTEXT%TYPE;
195 ln_reg_id NUMBER ;
196 le_error EXCEPTION ;
197
198 CURSOR Location_Cursor
199 IS
200 SELECT
201 NVL(Location_id,0),
202 trading
203 FROM
204 JAI_INV_SUBINV_DTLS
205 WHERE
206 Sub_Inventory_Name = v_subinventory AND
207 organization_id = v_organization_id ;
208
209
210 CURSOR item_trading_cur
211 IS
212 SELECT
213 Item_Trading_Flag
214 FROM
215 JAI_INV_ITM_SETUPS
216 WHERE
217 organization_id = v_organization_id AND
218 inventory_item_id = v_inventory_item_id ;
219
220 CURSOR Trading_register_code_cur(
221 p_organization_id NUMBER ,
222 p_location_id NUMBER ,
223 p_delivery_detail_id NUMBER ,
224 p_order_type_id NUMBER
225 )
226 IS
227 SELECT
228 A.register_code
229 FROM
230 JAI_OM_OE_BOND_REG_HDRS A, JAI_OM_OE_BOND_REG_DTLS b
231 WHERE
232 a.organization_id = p_organization_id AND
233 a.location_id = p_location_id AND
234 a.register_id = b.register_id AND
235 b.order_flag = 'Y' AND
236 b.order_type_id = p_order_type_id AND
237 a.register_code LIKE '23D%';
238
239 /*
240 Code added by aiyer for the bug 3844145.
241 Removed the group by subinventory clause from the query. The matched qty should be considered irrespective of the
242 subinventory
243 */
244
245 CURSOR matched_receipt_cur1
246 IS
247 SELECT
248 sum(a.quantity_applied) quantity_applied
249 FROM
250 JAI_CMN_MATCH_RECEIPTS a
251 WHERE
252 a.ref_line_id = v_delivery_detail_id
253 AND a.order_invoice = 'O' ;
254
255 CURSOR get_item_attributes
256 IS
257 SELECT
258 excise_flag ,-- Commented attribute1 by Brathod for Bug# 4299606 (DFF Elimination)
259 modvat_flag ,-- Commented attribute2 by Brathod for Bug# 4299606 (DFF Elimination)
260 nvl(container_item_flag,'N') --Added by Nagaraj.s for Bug3123613.
261 FROM
262 mtl_system_items msi,
263 JAI_INV_ITM_SETUPS jmsi -- Added by Brathod for Bug# 4299606 (DFF Elimination)
264 WHERE msi.organization_id = jmsi.organization_id
265 AND msi.inventory_item_id = jmsi.inventory_item_id
266 AND jmsi.inventory_item_id = v_Inventory_Item_Id -- Added by Brathod for Bug# 4299606 (DFF Elimination)
267 AND jmsi.organization_id = v_organization_id; -- Added by Brathod for Bug# 4299606 (DFF Elimination)
268
269
270 -- Following cursors added by sriram bug# 2165355
271 CURSOR c_check_lc_order
272 IS
273 SELECT
274 lc_flag
275 FROM
276 JAI_OM_OE_SO_LINES
277 WHERE
278 lc_flag = 'Y' AND
279 header_id = pr_new.source_header_id;
280
281 /*
282 This query has been modified aiyer for the bug #3039521.
283 Add the delivery_detail_id in the where clause so that the lc matched quantity for a particular delivery_detail_id is checked.
284 This would ensure that even though a order line has been split before release ,every split order Line
285 (based on the delivery detail id ) needs to be lc matched for the shipped quantity.
286 */
287 CURSOR c_matched_qty_cur
288 IS
289 SELECT
290 sum(qty_matched)
291 FROM
292 JAI_OM_LC_MATCHINGS
293 WHERE
294 order_header_id = pr_new.source_header_id AND
295 -- order_line_id = pr_new.source_line_id AND --commented by csahoo for bug#5686360
296 delivery_detail_id = v_delivery_detail_id AND
297 release_flag is null;
298
299 v_check_lc_order VARCHAR2(1);
300 v_lc_qty_matched NUMBER;
301 v_lc_shipped_qty NUMBER; --File.Sql.35 Cbabu := pr_new.Shipped_quantity;
302
303 -- ends here additions bug sriram bug# 2165355
304
305 /* Start, bug#5686360
306 following code is to correct the existing lc_matching order line to new split line id
307 */
308 cursor c_order_line is
309 select split_from_line_id --, split_by
310 from oe_order_lines_all
311 where line_id = pr_new.source_line_id;
312
313 cursor c_lc_mtch_dlry_line is
314 select order_line_id
315 from JAI_OM_LC_MATCHINGS
316 where delivery_detail_id = v_delivery_detail_id;
317 r_order_line c_order_line%rowtype;
318 r_lc_mtch_dlry_line c_lc_mtch_dlry_line%rowtype;
319 ln_lc_update_cnt number;
320 /* end bug#5686360 */
321
322
323 -- Following cursors added by sriram - bug # 2689417
324 -- The following cursor gets the tax amount for the line_id from JAI_OM_OE_SO_LINES table
325 CURSOR c_ja_in_so_lines_tax_amt
326 IS
327 SELECT
328 tax_amount
329 FROM
330 JAI_OM_OE_SO_LINES
331 WHERE
332 line_id = pr_new.source_line_id AND
333 header_id = pr_new.source_header_id;
334
335
336 -- The following cursor gets the sum of tax amount for the line_id from JAI_OM_OE_SO_TAXES table
337 CURSOR c_ja_in_so_tax_lines_tax_amt
338 IS
339 SELECT
340 nvl(sum(so_tax.tax_amount),0)
341 FROM
342 JAI_OM_OE_SO_TAXES so_tax
343 , jai_cmn_taxes_all tax -- Added by Jia Li for inclusive tax on 2008/01/07
344 WHERE
345 line_id = pr_new.source_line_id AND
346 header_id = pr_new.source_header_id
347 AND so_tax.tax_id = tax.tax_id -- Added by Jia Li for inclusive tax on 2008/01/07
348 AND NVL(tax.inclusive_tax_flag,'N') = 'N' ; -- Added by Jia Li for inclusive tax on 2008/01/07
349
350
351
352 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
353 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
354 -- ends here -- cursors added by sriram - bug # 2689417
355
356
357 /*
358 The following cursor has been added by aiyer for the bug #3039521.
359 Get the Currency code using the current org id from the table hr_operating_units and gl_sets_of_books
360 */
361 -- Start of cursor added by aiyer for the bug #3039521
362 /* bug 5243532. Added by Lakshmi Gopalsami
363 Removed the reference to cursor sob_cur
364 as this is not used anywhere.
365 */
366
367 v_currency_code GL_SETS_OF_BOOKS.CURRENCY_CODE%TYPE ;
368
369 -- End of cursor Sob_Cur added for the bug #3039521.
370 -- Start Of Bug #
371 CURSOR c_chk_exc_exmpt_rule
372 IS
373 SELECT
374 a.excise_exempt_type ,
375 a.line_number ,
376 a.shipment_line_number ,
377 quantity -- added by sriram bug# 3441684
378 FROM
379 JAI_OM_OE_SO_LINES a
380 WHERE
381 a.line_id = pr_new.source_line_id AND
382 a.header_id = pr_new.source_header_id ;
383
384 lv_excise_exempt_type JAI_OM_OE_SO_LINES.EXCISE_EXEMPT_TYPE%TYPE ;
385 ln_line_number JAI_OM_OE_SO_LINES.LINE_NUMBER%TYPE ;
386 ln_shipment_line_number JAI_OM_OE_SO_LINES.SHIPMENT_LINE_NUMBER%TYPE ;
387 lv_ret_flag VARCHAR2(10) ;
388 lv_error_msg VARCHAR2(1996) ;
389 v_quantity number;
390 -- End Of Bug #
391
392 -- added by sriram - bug#3441684
393 cursor c_orgn_info is
394 select trading
395 from JAI_CMN_INVENTORY_ORGS
396 where organization_id = pr_new.organization_id
397 and location_id = pr_new.ship_from_location_id ;
398 --ends here additions by sriram - bug#3441684
399
400 /*
401 || Begin Bug#4245073
402 || Author : Brathod
403 || Date : 17-Mar-2005
404 */
405 ln_vat_cnt NUMBER DEFAULT 0 ;
406 ln_cnt_org_loc_setup NUMBER DEFAULT 0 ;
407 lv_applicable JAI_RGM_ITM_TMPL_ATTRS.ATTRIBUTE_CODE%TYPE;
408 lv_process_flag VARCHAR2 (2);
409 lv_process_message VARCHAR2 (1000);
410
411 CURSOR cur_chk_vat_exists (cp_line_id JAI_OM_OE_SO_TAXES.LINE_ID%TYPE,
412 cp_header_id JAI_OM_OE_SO_TAXES.HEADER_ID%TYPE
413 )
414 IS
415 SELECT 1
416 FROM JAI_OM_OE_SO_TAXES jstl ,
417 JAI_CMN_TAXES_ALL jtc ,
418 jai_regime_tax_types_v tax_types
419 WHERE jstl.line_id = cp_line_id
420 AND jstl.header_id = cp_header_id
421 AND jtc.tax_id = jstl.tax_id
422 AND jtc.tax_type = tax_types.tax_type
423 AND tax_types.regime_code = jai_constants.vat_regime;
424
425 CURSOR cur_chk_org_loc_setup (cp_organization_id JAI_OM_WSH_LINES_ALL.LOCATION_ID%TYPE,
426 cp_location_id JAI_OM_WSH_LINES_ALL.ORGANIZATION_ID%TYPE
427 )
428 IS
429 SELECT 1
430 FROM jai_rgm_parties rgmpt,
431 JAI_RGM_DEFINITIONS rgms
432 WHERE rgmpt.regime_id = rgms.regime_id
433 AND rgmpt.location_id = cp_location_id
434 AND rgmpt.organization_id = cp_organization_id
435 AND rgms.regime_code = jai_constants.vat_regime;
436
437 /*
438 ||End of bug 4245073
439 */
440
441
442 /*
443 || Added by bgowrava for the forward porting bug 5631784
444 */
445 tab_ooh OE_ORDER_HEADERS_ALL%ROWTYPE ;
446 CURSOR cur_get_org_hdr (cp_header_id OE_ORDER_HEADERS_ALL.HEADER_ID%TYPE)
447 IS
448 SELECT
449 *
450 FROM
451 oe_order_headers_all
452 WHERE
453 header_id = cp_header_id ;
454
455
456 PROCEDURE set_debug_context
457 IS
458 BEGIN
459 lv_context := rtrim(lv_object_name || '.'||lv_member_name,'.');
460 END set_debug_context;
461
462 /* End of bug 5631784*/
463
464 BEGIN
465 pv_return_code := jai_constants.successful ;
466 /***************************************************************************************************************************************************
467
468 Change History :
469
470 1. Sriram - Bug # 2645439. File Version 615.1
471 Created the trigger.This trigger was created because , when the
472 interface trip stop runs into an error in the case of a trading organization , because the
473 Shipment is not matched against any receipt , it shows a form level error message and does
474 not allow shipment to continue.
475
476 2. Sriram - Bug # 2689417 File Version 615.2
477 Added another condition in the WHEN Clause of the trigger to check that the trigger
478 fires only when the Released_status field is set to 'C' and does not fire
479 on updates of other fields at which point the releaed_status is 'C'.
480
481 Also the update statement , which updates the released_flag in the JAI_OM_LC_MATCHINGS table
482 has been commented , because the update should happen after the shipping has completed.
483
484 Also an error message has to be thrown , when shipping is done , for an order
485 where the tax amounts in the JAI_OM_OE_SO_TAXES and JAI_OM_OE_SO_LINES do not tally.
486
487 3. Avishek - Bug # 2928261
488 Added a RAISE_APPLICATION_ERROR to give an error message when a sub-inventory is not
489 associated properly with a location ID.
490
491 4. Aiyer - Bug # 3039521, File Version 616.1 Date 09-Jul-2003
492 Issue:-
493 1. Orders which are partially matched and shipped post splitting of lines without full matching get shipped and are not stopped by
494 this trigger as it does when the lines are not split.
495
496 2. Another issue reported with this trigger was that it used to get fired even in case for non Indian Shipments.
497 These shipments need not have the Localization sub-inventory setup - Mandatory for all Indian Shipments
498 (using localization). However as this trigger used to get fired in those cases also hence the error
499 "Mandatory Localization sub-inventory setup is not made" used to get invoked and the execution used
500 to stop.
501
502 Solution:-
503 1. While checking that for a line being shipped, if the LC flag is enabled , then the amount being shipped should be
504 lc matched. However it was not being checked that, in case of a split line also the amount pertaining to the
505 same delivery_detail_id should be lc matched.
506 Added a where clause in the cursor c_matched_qty_cur to also include the check for the delivery_detail_id.
507
508 2.Put a check in the beginning of the trigger that if the functional currency is NOT 'INR' then
509 return from the trigger, i.e this trigger should get bypassed in case of Non Indian Shipments
510 (Global Scenario).
511
512 5. Aiyer - Bug #3032569, File Version 616.2 Date 24-Jul-2003
513 Issue:-
514 The trigger checks that the match receipt functionality is performed in scenario's of
515 Trading Domestic Without Excise and Export Without excise.
516 This check is not required. The match receipts only needs to be done for
517 'Trading Domestic With Excise' and 'Export with Excise' type of Scenario's
518
519 Solution:-
520 Modified the IF statment to raise an error only when trading register code is in 23D_DOMESTIC_EXCISE and 23D_EXPORT_EXCISE.
521 The other two trading register_codes '23D_DOM_WITHOUT_EXCISE' and '23D_EXPORT_WITHOUT_EXCISE' have been removed as matched receipts
522 is not relevant in this case.
523 Dependency Introduced Due to this Bug : -
524 None
525
526 6. Nagaraj.s -Bug#3123613, File Version : 616.3 Date:04-Sep-2003
527 Added the container_item_flag also in the cursor get_item_attributes and the
528 check for Mandatory Location is added with one more condition of :
529 v_container_item_flag ='N' , so that in case of containerization, the check
530 does not hold good.
531
532 7. Aiyer -Bug# 3392528 , File Version : 618.1 Date:11-Feb-2004
533 Issue
534 =====
535 An sales order In India Localization Order Management should not be allowed to be shipped on the following
536 conditions : -
537 1. A Sales order with excise exemption types like EXCISE_EXEMPT_CERT,
538 CT2,CT3 has modvat type of taxes attached
539
540 2. A Sales order with excise exemption types like EXCISE_EXEMPT_CERT_OTH,
541 CT2_OTH does not have modvat recovery type of tax attached
542
543 3. A sales order does not have any excise exemptions specified and still
544 has Modvat Recovery type of taxes.
545
546 These check also needs to be implemented at shipping level
547
548 Solution:-
549 Created the procedure jai_om_utils_pkg.validate_excise_exemption to validate all the above conditions and called the same
550 with the relevent parameters. This has resolved the issue
551
552 Dependency Due To The Current Bug :
553 1. This trigger ja_in_wsh_dlry_au_rel_stat_trg.sql (618.1) call the
554 function ->jai_om_utils_pkg.validate_excise_exemption(618.1) and hence has dependencies
555
556 8. ssumaith - Bug#3441684 file version 618.2 : 20-feb-04
557
558 After match receipts is done for a delivery , when back-ordering is performed, the delivery
559 is back-ordered , but the matched info is not removed from the match receipts table.
560
561 The requirement is to raise an error when backordering is done without unmatching the delivery.
562
563 Dependency Due to this bug:
564 None
565
566 9. Aiyer 23/08/2004 Bug# 3844145 file version 115.2
567 Issue:-
568 The inventory trip stop concurrent program errors out for a Internal sales order with qty being matched to two different subinventories.
569
570 Reason :-
571 The existing code was considering matched quantites as per the subinventories. This should not be considered.
572 Matched quantities should be considered irrespective of the subinventories.
573
574 Solution:-
575 Modified the cursor matched_receipt_cur1 in the current trigger.Removed the group by subinventory clause from the query.
576
577 Dependency Due to this bug:
578 None
579
580
581 10. Aparajita 30/11/2004. Bug#4036241. Version#115.3
582
583 Introduced the call to centralized packaged procedure,
584 jai_cmn_utils_pkg.check_jai_exists to check if localization has been installed.
585
586
587 11. Brathod 21/03/2005. Bug#4245073. Version# 115.4, 115.5
588 Trigger modified to check whether vat type of tax exists or not.
589 If vat type of tax exits and organization, location setup does not
590 exits for vat rigme, trigger will throw an exeception.
591 Also if vat type of tax exits but the item is not vatable trigger
592 will throw an exception
593
594 12 Brathod 26/04/2005for Bug# 4299606 File Version 116.1
595 Issue:-
596 Item DFF Elimination
597 Fix:-
598 Changed the code that references attributeN (where N=1,2,3,4,5,15) of
599 mtl_system_items to corrosponding columns in JAI_INV_ITM_SETUPS
600
601 Dependency :-
602 IN60106 + 4239736 (Service Tax) + 4245089 (VAT)
603
604 13 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
605 DB Entity as required for CASE COMPLAINCE. Version 116.1
606
607 14. 13-Jun-2005 File Version: 116.2
608 Ramananda for bug#4428980. Removal of SQL LITERALs is done
609
610 15. Aiyer 22/08/2005. Bug#4566002(Forward Porting for bug 4426615), File Version# 120.3
611 Issue : -
612 After LC Matching, the user is not being allowed to Backorder the quantity.
613
614 Fix:-
615 The matched quantity = shipped quantity should be checked only in case of ship confirm release_status = 'C'.
616 This check should not happen on Backordering release_status = 'B'.
617
618 16. 30-Jan-2007 bgowrava for forward porting bug#5631784(4742259), File Version 120.4
619 Added the call to jai_ar_tcs_rep_pkg.process_transaction. for TCS related validations.
620 Dependencies Due to this bug:-
621 This bug has lots of datamodel abd specification changes.
622
623 17. 17-May-2007 CSahoo for bug#5686360 File Version 120.5
624 Forward porting of 11i BUG#5665937
625 modified the cursor c_matched_qty_cur to filter the data only by delivery_detail_id
626 and order header_id and removed the filter by order line_id
627
628 Added the cursors c_order_line and c_lc_mtch_dlry_line, to check if the order line attached
629 the delivery detail in LC matching table is same or not. If not, then the related delivery_detail
630 line is updated with the :new.source_line_id
631
632 Appended SQLERRM to the fnd_message.set_token value parameter to display the error
633
634
635
636 Future Dependencies For the release Of this Object:-
637 (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/
638 A datamodel change )
639
640 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
641 Current Version Current Bug Dependent Dependency On Files Version Author Date Remarks
642 Of File On Bug/Patchset
643 ja_in_wsh_dlry_au_rel_stat_trg.sql
644 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
645 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
646
647 115.3 4036241 4033992 ja_in_util_pkg_s.sql 115.0 115.0 Apdas 30-nov-04
648 ja_in_util_pkg_b.sql 115.0 115.0 apdas 30-11-2004
649
650 115.5 4245073 4245089 ALL VAT Objects
651
652 115.6 4299606 IN60106
653 + 4239736 (Service Tax)
654 + 4245089 (VAT)
655
656 18. 01-JAN-2008 Added by Jia Li
657 for Inclusive tax Computation
658 -------------------------------------------------------------------------------------------------------------------*/
659
660 --File.Sql.35 Cbabu
661 v_inventory_item_id :=pr_new.inventory_item_id ;
662 v_organization_id :=pr_new.organization_id ;
663 v_subinventory :=pr_new.subinventory ;
664 v_delivery_detail_id :=pr_new.delivery_detail_id ;
665 v_source_header_type_id :=pr_new.source_header_type_id ;
666 v_shipped_quantity := nvl(pr_new.shipped_quantity,0) ;
667 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
668 v_matched_qty := 0 ;
669 v_lc_shipped_qty := pr_new.Shipped_quantity;
670 v_line_tax_amount :=0;
671 v_sum_tax_amount :=0;
672
673 --if
674 -- 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)
675 -- =
676 -- FALSE
677 --then
678 -- /* India Localization funtionality is not required */
679 -- return;
680 --end if;
681
682 -- Following statements added by sriram - bug # 2689417
683 OPEN c_ja_in_so_lines_tax_amt;
684 FETCH c_ja_in_so_lines_tax_amt INTO v_line_tax_amount;
685 CLOSE c_ja_in_so_lines_tax_amt;
686
687 OPEN c_ja_in_so_tax_lines_tax_amt;
688 FETCH c_ja_in_so_tax_lines_tax_amt INTO v_sum_tax_amount;
689 CLOSE c_ja_in_so_tax_lines_tax_amt;
690
691 IF NVL(v_line_tax_amount,0) <> NVL(v_sum_tax_amount,0) THEN
692 /* 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);
693 */ 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 ;
694 END IF;
695 -- ends here bug # 2689417
696
697 -- Start Of Bug #3392528,
698 /*
699 This code has been added by aiyer for the bug 3392528.
700 Call the function jai_om_utils_pkg.validate_excise_exemption to validate the different valid combination of values
701 that can exists between JAI_OM_OE_SO_LINES.excise_exempt_type and tax types associated with the
702 table JAI_OM_OE_SO_TAXES
703 */
704
705 OPEN c_chk_exc_exmpt_rule;
706 FETCH c_chk_exc_exmpt_rule INTO lv_excise_exempt_type,ln_line_number,ln_shipment_line_number,v_quantity;
707 CLOSE c_chk_exc_exmpt_rule ;
708
709 lv_ret_flag := jai_om_utils_pkg.validate_excise_exemption (
710 p_line_id => pr_new.source_line_id ,
711 p_excise_exempt_type => lv_excise_exempt_type ,
712 p_line_number => ln_line_number ,
713 p_shipment_line_number => ln_shipment_line_number ,
714 p_error_msg => lv_error_msg
715 ) ;
716 IF nvl(lv_ret_flag,'S') = 'EE' THEN
717 /* Handle all expected errors in this section. */
718 /* raise_application_error(-20406, lv_error_msg ); */ pv_return_code := jai_constants.expected_error ; pv_return_message := lv_error_msg ; return ;
719
720 ELSIF nvl(lv_ret_flag,'S') = 'UE' THEN
721 /* Handle all unexpected errors in this section. */
722 /* raise_application_error(-20406, lv_error_msg );
723 */ pv_return_code := jai_constants.expected_error ; pv_return_message := lv_error_msg ; return ;
724 END IF ;
725 -- End Of Bug #3392528
726
727 OPEN location_cursor;
728 FETCH location_cursor INTO v_location_id, v_trading_flag;
729 CLOSE location_cursor;
730
731 /*
732 || Begin Bug#4245073
733 || Author : Brathod
734 || Date : 17-Mar-2005
735 */
736
737 IF nvl(pr_new.Released_status,'N') = 'C' THEN
738 OPEN cur_chk_vat_exists ( cp_line_id => pr_new.source_line_id,
739 cp_header_id => pr_new.source_header_id
740 );
741 FETCH cur_chk_vat_exists INTO ln_vat_cnt;
742 CLOSE cur_chk_vat_exists ;
743
744 IF nvl (ln_vat_cnt,0) > 0 THEN
745
746 OPEN cur_chk_org_loc_setup (cp_organization_id => v_organization_id ,
747 cp_location_id => v_location_id
748 );
749 FETCH cur_chk_org_loc_setup INTO ln_cnt_org_loc_setup;
750 CLOSE cur_chk_org_loc_setup ;
751
752 IF nvl(ln_cnt_org_loc_setup,0) = 0 THEN
753 /*
754 || For vat regime organization-location specific setup does not exist in
755 || jai_rgm_parties (Regime Organization Registration)
756 */
757 app_exception.raise_exception( EXCEPTION_TYPE => 'APP'
758 ,EXCEPTION_CODE => NULL
759 ,EXCEPTION_TEXT => 'Organization-Location setup does not exist at regime level'
760 );
761 END IF;
762
763 jai_inv_items_pkg.jai_get_attrib ( p_regime_code => jai_constants.vat_regime
764 , p_organization_id => pr_new.organization_id
765 , p_inventory_item_id => pr_new.inventory_item_id
766 , p_attribute_code => jai_constants.rgm_attr_item_applicable
767 , p_attribute_value => lv_applicable
768 , p_process_flag => lv_process_flag
769 , p_process_msg => lv_process_message
770 );
771 IF lv_process_flag = jai_constants.successful
772 AND nvl(lv_applicable,'N') = 'N' THEN
773 /*
774 ||item is not vatable
775 */
776 app_exception.raise_exception( EXCEPTION_TYPE => 'APP'
777 ,EXCEPTION_CODE => NULL
778 ,EXCEPTION_TEXT => 'An item which is not Vatable has VAT Taxes attached.
779 Please correct the item attribute or remove the VAT type of taxes'
780 );
781 ELSIF lv_process_flag <> jai_constants.successful THEN
782 app_exception.raise_exception( EXCEPTION_TYPE => 'APP'
783 ,EXCEPTION_CODE => NULL
784 ,EXCEPTION_TEXT => substr (lv_process_message,1,999)
785 );
786
787
788 END IF;
789 END IF;
790 END IF;
791
792 /*
793 || End Bug#4245073
794 */
795
796 --Moved this cursor here by Nagaraj.s for Bug3123613
797 OPEN get_item_attributes;
798 FETCH get_item_attributes INTO v_exe_flag,v_mod_flag,v_container_item_flag;
799 CLOSE get_item_attributes;
800
801 -- SJS
802 open c_orgn_info;
803 fetch c_orgn_info into v_trading_flag;
804 close c_orgn_info;
805 -- SJS
806 -- code to be added by AVIKUMAR Bug # 2928261
807
808 if nvl(pr_new.Released_status,'N') = 'C' then
809
810 IF v_location_id IS NULL and v_container_item_flag ='N' then
811 /* raise_application_error(-20406, 'Mandatory India Localization Sub-inventory Setup not done for this Location from where shipment is made');
812 */ 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 ;
813 end if ;
814 end if;
815 -- ends here - Bug # 2928261
816
817 -- starts here additions by sriram - bug#3441684
818 if v_location_id is null then
819 v_location_id := pr_new.ship_from_location_id;
820 end if;
821 -- ends here additions by sriram - bug# 3441684
822
823 OPEN trading_register_code_cur( v_organization_id ,
824 v_location_id ,
825 v_delivery_detail_id ,
826 v_source_header_type_id
827 );
828 FETCH trading_register_code_cur INTO v_trad_register_code;
829 CLOSE trading_register_code_cur;
830
831 OPEN item_trading_cur;
832 FETCH item_trading_cur INTO v_item_trading_flag;
833 CLOSE item_trading_cur;
834
835 /*
836 Code modified by aiyer for the bug 3032569
837 Modified the IF statment to raise an error only when trading register code is in 23D_DOMESTIC_EXCISE and 23D_EXPORT_EXCISE.
838 The other two trading register_codes '23D_DOM_WITHOUT_EXCISE' and '23D_EXPORT_WITHOUT_EXCISE' have been removed from this if statement
839 as matched receipts is not relevant in this case.
840 */
841 -- Start of Bug #3032569
842
843 IF v_trad_register_code IN(
844 '23D_DOMESTIC_EXCISE' ,
845 '23D_EXPORT_EXCISE'
846 )
847 THEN
848 -- End of Bug #3032569
849
850 IF nvl(v_trading_flag,'N') = 'Y' AND nvl(V_item_trading_flag,'N') = 'Y' AND NVL(v_exe_flag,'N')= 'Y' THEN
851
852 OPEN matched_receipt_cur1;
853 FETCH matched_receipt_cur1 INTO v_matched_qty/*,v_matched_subinv*/;
854 CLOSE matched_receipt_cur1;
855 IF nvl(pr_new.Released_status,'N') = 'C' THEN
856 IF nvl(v_shipped_quantity,0) <> nvl(v_matched_qty,0) THEN
857 /* raise_application_error(-20401, 'Matched Quantity -- ' || TO_CHAR(NVL(v_matched_qty,0)) ||
858 ' should be equal to Shipped Quantity -- ' || TO_CHAR(NVL(v_shipped_quantity,0))); */
859 pv_return_code := jai_constants.expected_error ; pv_return_message := 'Matched Quantity -- ' || TO_CHAR(NVL(v_matched_qty,0)) ||
860 ' should be equal to Shipped Quantity -- ' || TO_CHAR(NVL(v_shipped_quantity,0)); return ;
861
862 END IF;
863 END IF;
864
865 -- starts additions by sriram - bug# 3441684
866 if nvl(pr_new.Released_status,'N') = 'B' then
867 -- if v_matched_qty = v_quantity and v_matched_qty is not null then
868 if nvl(v_matched_qty,0) > 0 then
869 /* raise_application_error (-20402, 'Please Unmatch the Delivery prior to backordering ');
870 */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'Please Unmatch the Delivery prior to backordering ' ; return ;
871 end if ;
872 end if;
873 -- ends here additions by sriram - bug# 3441684
874 END IF;
875 END IF;
876
877 /*
878 || Start of bug 4566002
879 || Code added by aiyer for the bug 4566002
880 || Added the IF statment to make sure that The matched quantity = shipped quantity condition should be checked only
881 || in case of ship confirm release_status = 'C'.
882 || This check should not happen on Backordering release_status = 'B'.
883 */
884 IF nvl(pr_new.Released_status,'N') = 'C' THEN
885 /*
886 the following lines added by sriram - lc functionality - bug# 2165355 - 19/09/2002
887 moved into the trigger on 27th november 2002.
888 */
889 OPEN c_check_lc_order;
890 FETCH c_check_lc_order INTO v_check_lc_order;
891 CLOSE c_check_lc_order;
892
893 IF NVL(v_check_lc_order,'N') = 'Y' THEN
894 OPEN c_matched_qty_cur;
895 FETCH c_matched_qty_cur INTO v_lc_qty_matched;
896 CLOSE c_matched_qty_cur;
897
898 IF NVL(v_lc_qty_matched,-999) <> NVL(v_lc_shipped_qty,-888) THEN
899 /* raise_application_error(-20401, 'LC Matched Quantity -- ' || TO_CHAR(NVL(v_lc_qty_matched,0)) ||
900 ' should be equal to Shipped Quantity -- ' || TO_CHAR(NVL(v_lc_shipped_qty,0)) || ' for LC enabled Orders'
901 ); */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'LC Matched Quantity -- ' || TO_CHAR(NVL(v_lc_qty_matched,0)) ||
902 ' should be equal to Shipped Quantity -- ' || TO_CHAR(NVL(v_lc_shipped_qty,0)) || ' for LC enabled Orders'
903 ; return ;
904 END IF;
905
906 /* Start, bug#5686360 csahoo
907 following code is to correct the existing lc_matching order line to new split line id
908 */
909 open c_order_line;
910 fetch c_order_line into r_order_line;
911 close c_order_line;
912
913 ln_lc_update_cnt := -1;
914 if r_order_line.split_from_line_id is not null then
915 open c_lc_mtch_dlry_line;
916 fetch c_lc_mtch_dlry_line into r_lc_mtch_dlry_line;
917 close c_lc_mtch_dlry_line;
918 if pr_new.source_line_id <> r_lc_mtch_dlry_line.order_line_id then
919
920 update JAI_OM_LC_MATCHINGS
921 set order_line_id = pr_new.source_line_id
922 where delivery_detail_id = v_delivery_detail_id
923 -- and order_line_id = r_order_line.split_from_line_id
924 and release_flag is null;
925 ln_lc_update_cnt := sql%rowcount;
926 end if;
927
928 end if;
929
930
931 /* End, bug#5686360 csahoo*/
932
933 END IF;
934
935 /*
936 ENDS HERE - CHANGES BY SRIRAM FOR LC FUNCTIONALITY - BUG # 2165355 - 19/09/2002
937 */
938 END IF;
939 /*
940 || End of bug 4566002
941 */
942
943 /****************************************************
944 || Added by bgowrava for forward porting bug#5631784(4742259)
945 ||TCS Validation
946 ****************************************************/
947 OPEN cur_get_org_hdr (cp_header_id => pr_new.source_header_id);
948 FETCH cur_get_org_hdr INTO tab_ooh ;
949 CLOSE cur_get_org_hdr ;
950
951 /*jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
952 pv_log_msg => ' Before call to jai_ar_tcs_rep_pkg.process_transactions .'
953 );*/ --commented by bgowrava for Bug #5631784
954
955 jai_ar_tcs_rep_pkg.process_transactions ( p_ooh => tab_ooh ,
956 p_event => jai_constants.wsh_ship_confirm ,
957 p_process_flag => lv_process_flag ,
958 p_process_message => lv_process_message
959 );
960
961 /*jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
962 pv_log_msg => ' returned from jai_ar_tcs_rep_pkg.process_transactions .'
963 );*/ --commented by bgowrava for Bug #5631784
964
965 IF lv_process_flag = jai_constants.expected_error OR ---------A2
966 lv_process_flag = jai_constants.unexpected_error
967 THEN
968 /*
969 || As Returned status is an error hence:-
970 || Set out variables p_process_flag and p_process_message accordingly
971 */
972 --call to debug package
973 /*jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
974 pv_log_msg => ' Error : - lv_process_flag -> '||lv_process_flag||' - '||lv_process_message
975 ); */ --commented by bgowrava for Bug #5631784
976
977 raise le_error;
978 END IF; ---------A2
979
980
981 /* jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
982 pv_log_msg => ' TRIGGER ja_in_wsh_dlry_au_rel_stat_trg COMPLETED SUCCESSFUL'
983 ); */ --commented by bgowrava for Bug #5631784
984 jai_cmn_debug_contexts_pkg.deregister (pn_reg_id => ln_reg_id);
985
986
987 EXCEPTION
988 WHEN le_error THEN
989 IF lv_process_flag = jai_constants.unexpected_error THEN
990 lv_process_message := substr (lv_process_message || ' Object = ja_in_wsh_dlry_au_rel_stat_trg ', 1,1999) ;
991 END IF;
992
993
994 fnd_message.set_name (application => 'JA',
995 name => 'JAI_ERR_DESC'
996 );
997
998 fnd_message.set_token ( token => 'JAI_ERROR_DESCRIPTION',
999 value => lv_process_message
1000 );
1001
1002
1003 app_exception.raise_exception;
1004
1005 WHEN others THEN
1006 fnd_message.set_name ( application => 'JA',
1007 name => 'JAI_ERR_DESC'
1008 );
1009
1010 fnd_message.set_token ( token => 'JAI_ERROR_DESCRIPTION',
1011 value => 'Exception Occured in ' || ' Object = ja_in_wsh_dlry_au_rel_stat_trg'||fnd_global.local_chr(10)||SQLERRM /* added SQLERRM for bug#5686360*/
1012 );
1013
1014 app_exception.raise_exception;
1015 /* end of Bug#5631784 */
1016
1017 END ARU_T2 ;
1018
1019 /*
1020 REM +======================================================================+
1021 REM NAME ARU_T3
1022 REM
1023 REM DESCRIPTION Called from trigger JAI_OM_WDD_ARIUD_T1
1024 REM
1025 REM NOTES Refers to old trigger JAI_OM_WDD_ARU_T4
1026 REM
1027 REM 02/11/2006 for Bug 5228046 by SACSETHI, File version 120.2
1028 REM Forward porting the change in 11i bug 5365523 (Additional CVD Enhancement).
1029 REM This bug has datamodel and spec changes.
1030 REM
1031 REM
1032 REM +======================================================================+
1033 */
1034 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
1035 v_creation_date DATE; --File.Sql.35 Cbabu :=pr_new.Creation_Date;
1036 v_created_by NUMBER; --File.Sql.35 Cbabu :=pr_new.Created_By;
1037 v_last_update_date DATE ; --File.Sql.35 Cbabu :=pr_new.Last_Update_Date;
1038 v_last_updated_by NUMBER; --File.Sql.35 Cbabu :=pr_new.Last_Updated_By;
1039 v_last_update_login NUMBER; --File.Sql.35 Cbabu :=pr_new.Last_Update_Login;
1040 v_delivery_detail_id NUMBER; --File.Sql.35 Cbabu :=pr_new.Delivery_Detail_Id;
1041 v_source_header_id NUMBER; --File.Sql.35 Cbabu :=pr_new.Source_Header_Id;
1042 v_source_line_id NUMBER; --File.Sql.35 Cbabu :=pr_new.Source_Line_Id;
1043 v_Inventory_Item_Id NUMBER; --File.Sql.35 Cbabu :=pr_new.Inventory_Item_Id;
1044 v_Requested_Quantity_Uom VARCHAR2(3); --File.Sql.35 Cbabu :=pr_new.Requested_Quantity_Uom;
1045 v_org_id NUMBER; --File.Sql.35 Cbabu :=pr_new.ORG_ID;
1046 v_customer_id NUMBER; --File.Sql.35 Cbabu :=pr_new.CUSTOMER_ID;
1047 v_source_header_type_id NUMBER ; --File.Sql.35 Cbabu :=pr_new.SOURCE_HEADER_TYPE_ID;
1048 v_subinventory VARCHAR2(10); --File.Sql.35 Cbabu :=pr_new.SUBINVENTORY;
1049 v_released_status VARCHAR2(1); --File.Sql.35 Cbabu :=pr_new.Released_Status;
1050 v_ordered_quantity NUMBER; --File.Sql.35 Cbabu := NVL(pr_new.Requested_Quantity,0);
1051 v_shipped_quantity NUMBER; --File.Sql.35 Cbabu := NVL(pr_new.Shipped_Quantity,0);
1052 v_Organization_Id NUMBER ; --File.Sql.35 Cbabu :=pr_new.Organization_Id;
1053 v_trading_flag VARCHAR2(1);
1054 v_status_code VARCHAR2(2);
1055 v_so_lines_count NUMBER;
1056 v_selling_price NUMBER;
1057 v_tax_category_id NUMBER(15);
1058 v_assessable_value NUMBER;
1059 v_excise_exempt_type VARCHAR2(60);
1060 v_excise_exempt_refno VARCHAR2(30);
1061 v_excise_exempt_date DATE;
1062 v_quantity NUMBER;
1063 v_picking_tax_lines_count NUMBER;
1064 v_tax_amount NUMBER;
1065 v_base_tax_amount NUMBER;
1066 v_func_tax_amount NUMBER;
1067 v_basic_excise_duty_amount NUMBER; --File.Sql.35 Cbabu := 0;
1068 v_add_excise_duty_amount NUMBER; --File.Sql.35 Cbabu := 0;
1069 v_oth_excise_duty_amount NUMBER; --File.Sql.35 Cbabu := 0;
1070 v_excise_amount NUMBER; --File.Sql.35 Cbabu := 0;
1071 v_left_shipped_qty NUMBER; --File.Sql.35 Cbabu := 0;
1072 v_rg23d_tax_amount NUMBER; --File.Sql.35 Cbabu := 0;
1073 v_rg23d_base_tax_amount NUMBER; --File.Sql.35 Cbabu := 0;
1074 v_rg23d_func_tax_amount NUMBER; --File.Sql.35 Cbabu := 0;
1075 v_tax_amt NUMBER; --File.Sql.35 Cbabu := 0;
1076 v_base_tax_amt NUMBER; --File.Sql.35 Cbabu := 0;
1077 v_func_tax_amt NUMBER; --File.Sql.35 Cbabu := 0;
1078 v_tot_tax_amount NUMBER;
1079 v_delivery_line_count NUMBER;
1080 v_location_id NUMBER;
1081 v_sqlerrm VARCHAR2(500);
1082 v_delivery_id NUMBER;
1083 v_ship_to_org_id NUMBER;
1084 v_date_confirmed DATE;
1085 counter NUMBER; --File.Sql.35 Cbabu :=0;
1086 -- Added by subbu
1087 v_raise_error_flag VARCHAR2(1);
1088 v_bonded_flag VARCHAR2(1);
1089 v_register_code VARCHAR2(30);
1090 v_fin_year NUMBER;
1091 v_old_register JAI_OM_WSH_LINES_ALL.register%TYPE;
1092 v_old_excise_invoice_no VARCHAR2(200);
1093 v_reg_type VARCHAR2(10);
1094 v_rg_type VARCHAR2(1);
1095 v_exc_invoice_no JAI_OM_WSH_LINES_ALL.excise_invoice_no%TYPE;
1096 v_tot_excise_amt NUMBER;
1097 v_tot_basic_ed_amt NUMBER;
1098 v_tot_addl_ed_amt NUMBER;
1099 v_tot_oth_ed_amt NUMBER;
1100 v_pref_rg23a NUMBER;
1101 v_pref_rg23c NUMBER;
1102 v_pref_pla NUMBER;
1103 v_ssi_unit_flag VARCHAR2(1);
1104 v_rg23a_balance NUMBER;
1105 v_rg23c_balance NUMBER;
1106 v_pla_balance NUMBER;
1107 v_order_type_id NUMBER;
1108 v_excise_flag VARCHAR2(1);
1109 v_item_class JAI_INV_ITM_SETUPS.item_class%TYPE;
1110 v_modvat_tax_rate NUMBER;
1111 v_rounding_factor NUMBER;
1112 v_exempt_bal NUMBER;
1113 v_basic_ed_amt NUMBER;
1114 v_remarks VARCHAR2(60);
1115 v_register_balance NUMBER;
1116 v_bond_tax_amount NUMBER;
1117 v_raise_exempt_flag VARCHAR2(1);
1118 v_exe_flag VARCHAR2(150);
1119 v_mod_flag VARCHAR2(150);
1120 --New Variables Declared by Nagaraj.s for Enh2415656
1121 v_output NUMBER ;-- By Nagaraj.s to get the output of the function jai_om_wsh_processing_pkg.excise_balance_check
1122 v_export_oriented_unit JAI_CMN_INVENTORY_ORGS.export_oriented_unit%TYPE;
1123 v_basic_pla_balance NUMBER;
1124 v_additional_pla_balance NUMBER;
1125 v_other_pla_balance NUMBER;
1126 v_error_message NUMBER; --This is for Capturing the Error Message
1127 v_myfilehandle UTL_FILE.FILE_TYPE; -- This is for File handling
1128 v_utl_location VARCHAR2(512);
1129 v_trip_id NUMBER;
1130 v_debug_flag VARCHAR2(1); --File.Sql.35 Cbabu := 'N'; -- Debug flag made to 'N' by arun iyer 12/03/2003 -- bug # 2828927
1131 --Ends here for Enh2415656
1132 -- end of addition by subbu
1133
1134 -- Added by Brathod for Bug#4215808
1135 ln_vat_assessable_value JAI_OM_OE_SO_LINES.VAT_ASSESSABLE_VALUE%TYPE;
1136 lv_vat_exemption_flag JAI_OM_WSH_LINES_ALL.VAT_EXEMPTION_FLAG%TYPE;
1137 lv_vat_exemption_type JAI_OM_WSH_LINES_ALL.VAT_EXEMPTION_TYPE%TYPE;
1138 ld_vat_exemption_date JAI_OM_WSH_LINES_ALL.VAT_EXEMPTION_DATE%TYPE;
1139 lv_vat_exemption_refno JAI_OM_WSH_LINES_ALL.VAT_EXEMPTION_REFNO%TYPE;
1140 -- End of Bug#4215808
1141
1142
1143 CURSOR Get_Status_Cur IS
1144 SELECT A.delivery_id,
1145 A.confirm_date,
1146 A.status_code
1147 FROM Wsh_Delivery_Assignments B,
1148 Wsh_New_deliveries A
1149 WHERE B.Delivery_Id = A.Delivery_Id
1150 AND B.Delivery_Detail_id = v_delivery_detail_id;
1151 --Added by GSRI for BUG 2283066
1152 /* Commented by Brathod for Bug# 4299606 (DFF Elimination)*/
1153 /* CURSOR get_item_attributes IS
1154 SELECT attribute1 ,attribute2
1155 FROM mtl_system_items
1156 WHERE inventory_item_id = v_Inventory_Item_Id
1157 AND organization_id = v_organization_id;
1158 End of Bug# 4299606 */
1159 /* Added by Brathod for Bug# 4299606 */
1160 CURSOR get_item_attributes IS
1161 SELECT excise_flag, modvat_flag
1162 FROM JAI_INV_ITM_SETUPS
1163 WHERE inventory_item_id = v_Inventory_Item_Id
1164 AND organization_id = v_organization_id;
1165 /* End of Bug# 4299606 */
1166
1167 -- End of Addition
1168 CURSOR Get_So_Lines_Count_Cur IS
1169 SELECT COUNT(*)
1170 FROM JAI_OM_OE_SO_LINES
1171 WHERE Line_id = v_source_line_id;
1172
1173 CURSOR Get_So_Lines_Details_Cur IS
1174 SELECT NVL(Selling_Price,0),
1175 NVL(Quantity,0),
1176 NVL(Tax_Category_Id,0),
1177 NVL(Assessable_Value,0),
1178 NVL(vat_assessable_value,0),
1179 Excise_Exempt_Type,
1180 Excise_Exempt_Refno,
1181 Excise_Exempt_Date,
1182 -- Added by Brathod for Bug#4215808
1183 vat_exemption_flag,
1184 vat_exemption_type,
1185 vat_exemption_date,
1186 vat_exemption_refno
1187 -- End of Bug#4215808
1188 FROM JAI_OM_OE_SO_LINES
1189 WHERE Line_id = v_source_line_id;
1190 /*
1191 Code changed by aiyer for the bug #3139718.
1192 Added the cursor to details required for currency conversion.
1193 */
1194
1195 --Start of #3139718
1196 /* bug 5243532. Added by Lakshmi Gopalsami
1197 Removed the reference to cursor sob_cur
1198 as this is not used anywhere.
1199 */
1200
1201
1202 CURSOR get_conv_detail_cur
1203 IS
1204 SELECT
1205 transactional_curr_code ,
1206 conversion_type_code ,
1207 conversion_rate ,
1208 nvl(b.actual_shipment_date,sysdate) actual_shipment_date
1209 FROM
1210 oe_order_headers_all a ,
1211 oe_order_lines_all b
1212 WHERE
1213 a.header_id = b.header_id AND
1214 b.line_id = v_source_line_id AND
1215 a.header_id = v_source_header_id ;
1216
1217 v_currency_code GL_SETS_OF_BOOKS.CURRENCY_CODE%TYPE ;
1218 v_set_of_books_id HR_OPERATING_UNITS.SET_OF_BOOKS_ID%TYPE ;
1219 v_conv_type_code oe_order_headers_all.conversion_type_code%TYPE;
1220 v_conv_rate NUMBER;
1221 v_conv_date DATE;
1222
1223 v_curr_conv_rate NUMBER;
1224
1225 --End of #3139718
1226
1227 CURSOR Get_Tax_Lines_Details_Cur IS
1228 SELECT b.Tax_Type,
1229 NVL(b.Rounding_Factor,2) Rounding_Factor, --changed the rounding factor to 2 if it is null, JMEENA bug#6280735 (FP 6164922)
1230 A.Tax_Line_No,
1231 A.Precedence_1,
1232 A.Precedence_2,
1233 A.Precedence_3,
1234 A.Precedence_4,
1235 A.Precedence_5,
1236 A.Precedence_6, -- CHANGE ON 02/11/2006 BY SACSETHI FOR BUG 5228046
1237 A.Precedence_7,
1238 A.Precedence_8,
1239 A.Precedence_9,
1240 A.Precedence_10,
1241 A.Tax_Id,
1242 A.Tax_Rate,
1243 A.Qty_Rate,
1244 A.Uom,
1245 A.Tax_Amount,
1246 A.Base_Tax_Amount,
1247 A.Func_Tax_Amount
1248 FROM JAI_OM_OE_SO_TAXES A,
1249 JAI_CMN_TAXES_ALL b
1250 WHERE Line_id = v_source_line_id
1251 AND A.Tax_Id = b.Tax_Id
1252 ORDER BY A.Tax_Line_No;
1253 CURSOR Pick_Tax_Line_Count_Cur(P_Tax_Id NUMBER) IS
1254 SELECT COUNT(*)
1255 FROM JAI_OM_WSH_LINE_TAXES
1256 WHERE Delivery_Detail_Id = v_delivery_detail_id
1257 AND Tax_Id = P_Tax_Id;
1258 CURSOR Get_Tot_Tax_Amount_Cur IS
1259 SELECT SUM(A.Tax_Amount)
1260 FROM JAI_OM_WSH_LINE_TAXES A,
1261 JAI_CMN_TAXES_ALL b
1262 WHERE A.Delivery_Detail_Id = v_delivery_detail_id
1263 AND b.Tax_Id = A.Tax_Id
1264 AND b.Tax_Type <> 'TDS';
1265 CURSOR Get_Delivery_Line_Count_Cur IS
1266 SELECT COUNT(*)
1267 FROM JAI_OM_WSH_LINES_ALL
1268 WHERE Delivery_Detail_Id = v_delivery_detail_id;
1269 CURSOR Location_Cursor IS
1270 SELECT NVL(Location_id,0),
1271 trading
1272 FROM JAI_INV_SUBINV_DTLS
1273 WHERE Sub_Inventory_Name = v_subinventory
1274 AND organization_id = v_organization_id;
1275 CURSOR get_ship_to_org_id_cur( p_line_id NUMBER) IS
1276 SELECT ship_to_org_id
1277 FROM Oe_order_lines_all
1278 WHERE line_id = p_line_id;
1279
1280 CURSOR rg23d_amount_cur(p_tax_id NUMBER) IS
1281 SELECT nvl(sum(tax_amount),0) ,
1282 nvl(sum(base_tax_amount),0),
1283 nvl(sum(func_tax_amount),0)
1284 FROM
1285 JAI_CMN_MATCH_TAXES
1286 WHERE
1287 ref_line_id = v_delivery_detail_id AND
1288 receipt_id IS NOT NULL AND
1289 tax_id = p_tax_id;
1290
1291 CURSOR ed_cur (p_tax_type VARCHAR2)IS
1292 -- SELECT SUM(NVL(A.func_tax_amount,0))
1293 SELECT
1294 nvl(sum(a.func_tax_amount),0) -- cbabu for Bug# 2736191
1295 FROM
1296 JAI_CMN_MATCH_TAXES a,
1297 JAI_CMN_TAXES_ALL b
1298 WHERE
1299 a.tax_id = b.tax_id AND
1300 b.tax_type = p_tax_type AND
1301 A.ref_line_id = v_delivery_detail_id AND
1302 A.receipt_id IS NOT NULL;
1303
1304 CURSOR ja_in_so_picking_exc_check(p_delivery_id NUMBER) IS
1305 SELECT DISTINCT register
1306 FROM JAI_OM_WSH_LINES_ALL
1307 WHERE delivery_id = p_delivery_id;
1308
1309 --2001/07/10 Anuradha Parthasarathy
1310 CURSOR item_trading_cur IS
1311 SELECT
1312 item_trading_flag
1313 FROM
1314 JAI_INV_ITM_SETUPS
1315 WHERE
1316 organization_id = v_organization_id AND
1317 inventory_item_id = v_inventory_item_id;
1318
1319 v_item_trading_flag VARCHAR2(1);
1320
1321 --2001/10/03 Anuradha Parthasarathy
1322 CURSOR uom_code IS
1323 SELECT order_quantity_uom
1324 FROM oe_order_lines_all
1325 WHERE line_id = v_source_line_id;
1326 v_order_quantity_uom VARCHAR2(3);
1327 v_conversion_rate NUMBER; --File.Sql.35 Cbabu := 0;
1328 --2001/12/20
1329 CURSOR Trading_register_code_cur(
1330 p_organization_id NUMBER,
1331 p_location_id NUMBER,
1332 p_delivery_detail_id NUMBER,
1333 p_order_type_id NUMBER
1334 ) IS
1335 SELECT
1336 a.register_code
1337 FROM
1338 JAI_OM_OE_BOND_REG_HDRS a,
1339 JAI_OM_OE_BOND_REG_DTLS b
1340 WHERE
1341 A.organization_id = p_organization_id AND
1342 A.location_id = p_location_id AND
1343 A.register_id = b.register_id AND
1344 b.order_flag = 'Y' AND
1345 b.order_type_id = p_order_type_id AND
1346 A.register_code LIKE '23D%';
1347
1348 v_trad_register_code VARCHAR2(30);
1349
1350 /*
1351 Code added by aiyer for the bug 3844145.
1352 Removed the group by subinventory clause from the query. The matched qty should be considered irrespective of the
1353 subinventory
1354 */
1355
1356 CURSOR matched_receipt_cur1 IS
1357 SELECT
1358
1359 sum(a.quantity_applied) quantity_applied
1360 FROM
1361 JAI_CMN_MATCH_RECEIPTS a
1362 WHERE
1363 a.ref_line_id = v_delivery_detail_id;
1364
1365 v_matched_qty NUMBER; --File.Sql.35 Cbabu := 0;
1366 --------------------------------------------------------------
1367 -- start of cursors for checking RG balances by subbu
1368 CURSOR get_item_dtls (p_organization_id NUMBER,p_item_id NUMBER) IS
1369 SELECT
1370 excise_flag,
1371 item_class
1372 FROM
1373 JAI_INV_ITM_SETUPS
1374 WHERE
1375 organization_id = p_organization_id AND
1376 inventory_item_id = p_item_id;
1377
1378 CURSOR bonded_cur(p_organization_id NUMBER, p_subinventory VARCHAR2) IS
1379 SELECT NVL(A.bonded,'Y') bonded
1380 FROM JAI_INV_SUBINV_DTLS A
1381 WHERE A.sub_inventory_name = p_subinventory
1382 AND A.organization_id = p_organization_id;
1383 CURSOR register_code_cur(p_organization_id NUMBER, p_location_id NUMBER, p_order_type_id NUMBER) IS
1384 SELECT A.register_code
1385 FROM JAI_OM_OE_BOND_REG_HDRS A, JAI_OM_OE_BOND_REG_DTLS b
1386 WHERE A.organization_id = p_organization_id
1387 AND A.location_id = p_location_id
1388 AND A.register_id = b.register_id
1389 AND b.order_flag = 'Y'
1390 AND b.order_type_id = p_order_type_id ;
1391 CURSOR fin_year_cur(p_organization_id IN NUMBER) IS
1392 SELECT MAX(A.fin_year)
1393 FROM JAI_CMN_FIN_YEARS A
1394 WHERE organization_id = p_organization_id
1395 AND fin_active_flag = 'Y';
1396 CURSOR pref_cur(p_organization_id NUMBER, p_location_id NUMBER)IS
1397 --This is included in the select by Nagaraj.s for Enh2415656
1398 SELECT pref_rg23a, pref_rg23c, pref_pla,
1399 NVL(Export_oriented_unit ,'N')
1400 FROM JAI_CMN_INVENTORY_ORGS
1401 WHERE organization_id = p_organization_id
1402 AND location_id = p_location_id ;
1403 --This is included in the select by Nagaraj.s for Enh2415656
1404 CURSOR rg_bal_cur(p_organization_id NUMBER, p_location_id NUMBER)IS
1405 SELECT NVL(rg23a_balance,0) rg23a_balance ,NVL(rg23c_balance,0) rg23c_balance,NVL(pla_balance,0) pla_balance,
1406 NVL(basic_pla_balance,0) basic_pla_balance,
1407 NVL(additional_pla_balance,0) additional_pla_balance,
1408 NVL(other_pla_balance,0) other_pla_balance
1409 FROM JAI_CMN_RG_BALANCES
1410 WHERE organization_id = p_organization_id
1411 AND location_id = p_location_id ;
1412 CURSOR ssi_unit_flag_cur(p_organization_id NUMBER, p_location_id NUMBER) IS
1413 SELECT ssi_unit_flag
1414 FROM JAI_CMN_INVENTORY_ORGS
1415 WHERE organization_id = p_organization_id
1416 AND location_id = p_location_id;
1417 CURSOR register_balance_cur(p_organization_id NUMBER,p_location_id NUMBER) IS
1418 SELECT register_balance
1419 FROM JAI_OM_OE_BOND_TRXS
1420 WHERE transaction_id = (SELECT MAX(A.transaction_id)
1421 FROM JAI_OM_OE_BOND_TRXS A, JAI_OM_OE_BOND_REG_HDRS B
1422 WHERE A.register_id = B.register_id
1423 AND B.organization_id = p_organization_id
1424 AND B.location_id = p_location_id );
1425 CURSOR Get_Tax_Lines_Details_Cur1 IS
1426 SELECT A.Tax_Rate, NVL(b.Rounding_Factor,0) Rounding_Factor
1427 FROM JAI_OM_OE_SO_TAXES A, JAI_CMN_TAXES_ALL b
1428 WHERE Line_id = v_source_line_id
1429 AND A.Tax_Id = b.Tax_Id
1430 AND b.tax_type = 'Modvat Recovery'
1431 ORDER BY A.Tax_Line_No;
1432 CURSOR for_modvat_percentage(p_organization_id NUMBER, p_location_id NUMBER) IS
1433 SELECT MODVAT_REVERSE_PERCENT
1434 FROM JAI_CMN_INVENTORY_ORGS
1435 WHERE organization_id = p_organization_id
1436 AND ( location_id = p_location_id
1437 OR
1438 location_id is NULL AND p_location_id is NULL); /* Modified by Ramananda for removal of SQL LITERALs */
1439 --AND NVL(location_id,0) = NVL(p_location_id,0);
1440
1441 -- end of cursors for checking RG balances by subbu
1442 -- following cursors added by sriram bug# 2165355
1443
1444 CURSOR C_CHECK_LC_ORDER IS
1445 SELECT LC_FLAG
1446 FROM JAI_OM_OE_SO_LINES
1447 WHERE LC_FLAG = 'Y' AND
1448 HEADER_ID = pr_new.SOURCE_HEADER_ID;
1449
1450 CURSOR C_MATCHED_QTY_CUR IS
1451 SELECT SUM(QTY_MATCHED)
1452 FROM JAI_OM_LC_MATCHINGS
1453 WHERE ORDER_HEADER_ID = pr_new.SOURCE_HEADER_ID
1454 -- AND ORDER_LINE_ID = pr_new.SOURCE_LINE_ID --commented by csahoo for bug#5680459
1455 AND delivery_detail_id = pr_new.delivery_detail_id -- bug# 3541960
1456 AND RELEASE_FLAG IS NULL;
1457 v_check_lc_order VARCHAR2(1);
1458 v_lc_qty_matched NUMBER;
1459 v_lc_shipped_qty NUMBER; --File.Sql.35 Cbabu := pr_new.Shipped_quantity;
1460 -- ends here additions bug sriram bug# 2165355
1461
1462 /* Start, csahoo for bug#5680459
1463 following code is to correct the existing lc_matching order line to new split line id
1464 */
1465 cursor c_order_line is
1466 select split_from_line_id --, split_by
1467 from oe_order_lines_all
1468 where line_id = pr_new.source_line_id;
1469
1470 cursor c_lc_mtch_dlry_line is
1471 select order_line_id
1472 from JAI_OM_LC_MATCHINGS
1473 where delivery_detail_id = pr_new.delivery_detail_id;
1474 r_order_line c_order_line%rowtype;
1475 r_lc_mtch_dlry_line c_lc_mtch_dlry_line%rowtype;
1476 ln_lc_update_cnt number;
1477 /* end csahoo for bug#5680459 */
1478
1479
1480 -- start additions by sriram - bug # 3021588
1481 v_asst_register_id Number;
1482 v_reg_exp_date Date;
1483 v_lou_flag Varchar2(1);
1484
1485 CURSOR c_cess_amount (cp_delivery_id JAI_OM_WSH_LINES_ALL.DELIVERY_ID%TYPE) IS
1486 SELECT nvl(sum(jsptl.func_tax_amount),0) tax_amount
1487 FROM JAI_OM_WSH_LINE_TAXES jsptl ,
1488 JAI_CMN_TAXES_ALL jtc
1489 WHERE jtc.tax_id = jsptl.tax_id
1490 AND delivery_detail_id in
1491 (SELECT delivery_detail_id
1492 FROM JAI_OM_WSH_LINES_ALL
1493 WHERE delivery_id = cp_delivery_id
1494 )
1495 AND upper(jtc.tax_type) in (upper(jai_constants.tax_type_cvd_edu_cess), upper(jai_constants.tax_type_exc_edu_cess));
1496
1497
1498 -- start, Bgowrava for forward porting bug#5989740
1499
1500 ln_sh_cess_amount JAI_CMN_RG_OTHERS.DEBIT%TYPE;
1501 /* Cursor is responsible to get secondary and higher cess */
1502
1503 CURSOR c_sh_cess_amount (cp_delivery_id JAI_OM_WSH_LINES_ALL.DELIVERY_ID%TYPE) IS
1504 SELECT nvl(sum(jsptl.func_tax_amount),0) tax_amount
1505 FROM JAI_OM_WSH_LINE_TAXES jsptl ,
1506 JAI_CMN_TAXES_ALL jtc
1507 WHERE jtc.tax_id = jsptl.tax_id
1508 AND delivery_detail_id in
1509 (SELECT delivery_detail_id
1510 FROM JAI_OM_WSH_LINES_ALL
1511 WHERE delivery_id = cp_delivery_id
1512 )
1513 AND upper(jtc.tax_type) in (upper(jai_constants.tax_type_sh_exc_edu_cess),
1514 upper(jai_constants.tax_type_sh_cvd_edu_cess)
1515 );
1516 -- end, Bgowrava for forward porting bug#5989740
1517
1518
1519 ln_cess_amount JAI_CMN_RG_OTHERS.DEBIT%TYPE;
1520 lv_process_flag VARCHAR2(5);
1521 lv_process_message VARCHAR2(1996);
1522
1523
1524 -- Added by brathod for Bug#4215808
1525
1526 ln_vat_cnt NUMBER DEFAULT 0 ;
1527 ln_vat_proc_cnt NUMBER DEFAULT 0 ;
1528 ln_regime_id JAI_RGM_ORG_REGNS_V.REGIME_ID%TYPE;
1529 lv_regns_num JAI_RGM_ORG_REGNS_V.ATTRIBUTE_VALUE%TYPE;
1530
1531 CURSOR cur_chk_vat_exists (cp_del_det_id JAI_OM_WSH_LINES_ALL.DELIVERY_DETAIL_ID%TYPE)
1532 IS
1533 SELECT 1
1534 FROM JAI_OM_WSH_LINE_TAXES jsptl,
1535 JAI_CMN_TAXES_ALL jtc
1536 , jai_regime_tax_types_v tax_types
1537 WHERE jsptl.delivery_detail_id = cp_del_det_id
1538 AND jtc.tax_id = jsptl.tax_id
1539 AND jtc.tax_type = tax_types.tax_type
1540 AND tax_types.regime_code = jai_constants.vat_regime;
1541
1542 CURSOR cur_get_regime_info (cp_organization_id JAI_RGM_ORG_REGNS_V.ORGANIZATION_ID%TYPE ,
1543 cp_location_id JAI_RGM_ORG_REGNS_V.LOCATION_ID%TYPE
1544 )
1545 IS
1546 SELECT regime_id,
1547 attribute_value
1548 FROM JAI_RGM_ORG_REGNS_V orrg
1549 WHERE orrg.organization_id = cp_organization_id
1550 AND orrg.location_id = cp_location_id
1551 AND attribute_type_code = jai_constants.rgm_attr_type_code_primary
1552 AND attribute_code = jai_constants.attr_code_regn_no
1553 AND regime_code = jai_constants.vat_regime;
1554
1555 CURSOR cur_chk_vat_proc_entry (cp_delivery_id JAI_OM_WSH_LINES_ALL.DELIVERY_ID%TYPE)
1556 IS
1557 SELECT 1
1558 FROM JAI_RGM_INVOICE_GEN_T
1559 WHERE delivery_id = cp_delivery_id;
1560
1561 -- End of Bug#4215808
1562
1563 /*
1564 || Added by csahoo for bug#5680459
1565 || Check if only 'VAT REVERSAL' tax type is present in JAI_OM_WSH_LINE_TAXES
1566 */
1567 CURSOR c_chk_vat_reversal (cp_del_det_id JAI_OM_WSH_LINES_ALL.delivery_detail_id%TYPE,
1568 cp_tax_type JAI_CMN_TAXES_ALL.tax_type%TYPE )
1569 IS
1570 SELECT 1
1571 FROM JAI_OM_WSH_LINE_TAXES jsptl,
1572 JAI_CMN_TAXES_ALL jtc
1573 WHERE jsptl.delivery_detail_id = cp_del_det_id
1574 AND jtc.tax_id = jsptl.tax_id
1575 AND jtc.tax_type = cp_tax_type ;
1576
1577 ln_vat_reversal_exists NUMBER ;
1578 lv_vat_reversal VARCHAR2(100);
1579 lv_vat_invoice_no VARCHAR2(10);
1580 lv_vat_inv_gen_status VARCHAR2(10);
1581 --bug#5680459, ends
1582
1583
1584 /** Bgowrava for forward porting Bug#5631784 */
1585 ln_tcs_exists number;
1586 --lv_process_flag jai_constants.successful%type;
1587 ln_threshold_tax_cat_id jai_ap_tds_thhold_taxes.tax_category_id%type;
1588 ln_tcs_regime_id JAI_RGM_DEFINITIONS.regime_id%type;
1589 ln_threshold_slab_id jai_ap_tds_thhold_slabs.threshold_slab_id%type;
1590 ln_last_line_no number;
1591 ln_base_line_no number;
1592 lv_context varchar2(240);
1593 ln_reg_id number;
1594
1595 CURSOR C_GET_REGIME_ID (CP_REGIME_CODE JAI_RGM_DEFINITIONS.REGIME_CODE%TYPE)
1596 IS
1597 SELECT REGIME_ID
1598 FROM JAI_RGM_DEFINITIONS
1599 WHERE REGIME_CODE = CP_REGIME_CODE;
1600
1601 /** Check if taxes with taxType as defined in the regime setup exists for given regime code */
1602 CURSOR C_CHK_RGM_TAX_EXISTS ( CP_REGIME_CODE JAI_RGM_DEFINITIONS.REGIME_CODE%TYPE
1603 , CP_DELIVERY_DETAIL_ID JAI_OM_WSH_LINE_TAXES.DELIVERY_DETAIL_ID%TYPE
1604 )
1605 IS
1606 SELECT COUNT(1)
1607 FROM JAI_REGIME_TAX_TYPES_V JRTTV
1608 , JAI_OM_WSH_LINE_TAXES JSPT
1609 , JAI_CMN_TAXES_ALL JTC
1610 WHERE JTC.TAX_ID = JSPT.TAX_ID
1611 AND JTC.TAX_TYPE = JRTTV.TAX_TYPE
1612 AND REGIME_CODE = CP_REGIME_CODE
1613 AND JSPT.DELIVERY_DETAIL_ID = CP_DELIVERY_DETAIL_ID;
1614
1615 /** End of Bug#5631784 **/
1616
1617
1618
1619 /* Bug 5243532. Added by Lakshmi Gopalsami
1620 Implemented caching logic.
1621 */
1622 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
1623
1624
1625 BEGIN
1626 pv_return_code := jai_constants.successful ;
1627 /*------------------------------------------------------------------------------------------
1628 FILENAME: JA_IN_WSH_DLRY_DTLS_AU_TRG.sql CHANGE HISTORY:
1629 SlNo. DD/MM/YYYY Author and Details of Modifications
1630 ------------------------------------------------------------------------------------------
1631 1 19/12/2000 MANOHAR MISHRA
1632 nvl function added to v_location_id
1633
1634 2 31/05/2001 Anuradha Parthasarathy
1635 Code added to return in case of qty shipped is zero
1636
1637 3 08/06/2001 Anuradha Parthasarathy
1638 Code added for correct tax insertions in case of trading orders.
1639
1640 4 23/06/2001 Anuradha Parthasarathy
1641 nvl function to v_location_id commented,because this is a necessary setup.
1642
1643 5 10/07/2001 Anuradha Parthasarathy
1644 Taxes should be picked from ja_in_rg23d_shipping_taxes only when a trading item is transacted
1645 from a trading subinventory.
1646
1647 6 03/10/2001 Anuradha Parthasarathy
1648 Tax Calculation as per the Inventory Uom
1649
1650 7 08/05/2002 Sriram SJ Bug # 2330055
1651 Insert of non zero selling price and assessable value in JAI_OM_WSH_LINES_ALL even though
1652 tax lines are not there.
1653
1654 8 03/07/2002 Nagaraj.s - For Enh#2415656.
1655 Cursors pref_cur - Incorporated v_export_oriented_unit also in the select clause
1656 RG Bal Cur- Incorporated basic,additional,other pla balance also in the select clause.
1657 Functions jai_om_wsh_processing_pkg.excise_balance_check - for preference checks in case of EOU and Non-EOU for total excise amount
1658 jai_om_wsh_pkg.get_excise_register_with_bal - for preference checks in case of EOU and Non-EOU
1659 for exempted amount.
1660 Before sending this patch it has to be taken care that, the alter scripts,functions should also
1661 accompany the patch otherwise the patch would certainly fail.
1662
1663 9 24/08/2002 Sriram SJ bug # 2531013
1664 Made the changes , to take care as to when the backordering functionality should be allowed.
1665
1666 10 01/11/2002 Sriram - Bug # 2165355
1667 LC Functionality. Added the Lc checks.
1668
1669 11 13/12/2002 Sriram - Bug # 2689417 - File Version 615.3
1670 Changed the WHEN clause in the trigger because after the ONT Patchset 'G'
1671 OM interface executes first and then Inventory interface . If the inventory interface
1672 errors out due to some reason other than Localization issue , then the line information
1673 is carried over to AR , but the taxes are not present in Shiping Localization tables ,
1674 causing lot of Data fix requirements.
1675
1676 12 09/01/2003 cbabu for Bug# 2736191 - File Version# 615.4
1677 For trading functionality JAI_OM_WSH_LINES_ALL.excise_amount is getting populated NULL always, if one
1678 of 'Excise', 'Addl. Excise', 'Other Excise' tax components is missing. ED_CUR cursor is modified to fetch
1679 0 if query does not retreive any data.
1680
1681 13. 12/03/2003 Arun Iyer Bug # 2828927 615.5
1682 v_Debug_flag = 'Y' was causing problems owing to reasons such as /usr/tmp folder etc.
1683 Hence making it 'N' .
1684
1685 14. 24/07/2003 Aiyer Bug #3032569, File Version 616.1
1686
1687 Issue:-
1688 The trigger validates that the match receipt functionality is performed in scenario's where an order is associated
1689 to bond registers - 'Trading Domestic Without Excise' and 'Export Without Excise'.
1690 This check is not required. The match receipts only needs to be done for
1691 'Trading Domestic With Excise' and 'Export with Excise' type of Scenario's
1692
1693 Solution:-
1694 Modified the IF statment to raise an error only when trading register code is in 23D_DOMESTIC_EXCISE and
1695 23D_EXPORT_EXCISE.
1696 The other two trading register_codes '23D_DOM_WITHOUT_EXCISE' and '23D_EXPORT_WITHOUT_EXCISE' have
1697 been removed as matched receipts is not relevant in this case.
1698
1699 Fix of bug #2988829 along with the current bug
1700 ----------------------------------------------
1701 Issue:-
1702 Initial code used to check that if the organization is a Trading organization,item is tradable and excisable
1703 then used to assume that a match receipt has been done and get the sum (tax_amount), sum(base_tax_amount),
1704 sum(func_tax_amount) for a delivery_detail_id and tax_id from the JAI_CMN_MATCH_TAXES table and
1705 populate this into the tax_amount, base_tax_amount and func_tax_amount columns of the table
1706 JAI_OM_WSH_LINE_TAXES.
1707
1708 This approach used to fail, as many a times a record never used to exists in the JAI_CMN_MATCH_TAXES table
1709 for the delivery_detail_id and tax_id even though the organization is declared as a trading organization,
1710 Item is tradable and excisable.
1711
1712 This happens in scenario's where an order assigned to a Bond register_type is either 'Trading domestic without excise' or
1713 'Export Without Excise', the match receipt functionaity would not be performed by client and consequently no
1714 data gets populated in JAI_CMN_MATCH_TAXES.
1715
1716 Solution: -
1717 Added the additional check :-
1718 v_trad_register_code IN ( '23D_DOMESTIC_EXCISE','23D_EXPORT_EXCISE') .
1719 is all the above cases.
1720 With this the check becomes that if a organization is trdable, item is tradable , excisable and the order
1721 associated to the bond register is Trading Domestic With Excise and Export With Excise only then
1722 it can be assumed that there would be data in table JAI_CMN_MATCH_TAXES (Match receipts has been performed).
1723 In such a case take the tax_amoutn ,base_tax_amount and func_tax_amoutn for the above table.
1724 Else take it from JAI_OM_OE_SO_LINES and JAI_OM_OE_SO_TAXES table.
1725
1726
1727 Dependency Introduced Due to this Bug : -
1728 None
1729
1730 15. 22/08/2003 Bug # 3021588 (Bond Register Enhancement) Version 616.2
1731
1732 For Multiple Bond Register Enhancement,
1733 Instead of using the cursors for fetching the register associated with the order type , a call has been made to the procedures
1734 of the jai_cmn_bond_register_pkg package. There enhancement has created dependency because of the
1735 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.
1736
1737 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
1738
1739 Provision for letter of undertaking has been incorporated. Letter of undetaking is also a type of bond register but without validation for the balances.
1740 This has been done by checking if the LOU_FLAG is 'Y' in the JAI_OM_OE_BOND_REG_HDRS table for the
1741 associated register id , if yes , then validation is only based on bond expiry date .
1742
1743 This fix has introduced huge dependency . All future changes in this object should have this bug as a prereq
1744
1745
1746 16.17/09/2003 Bug #3148621 File Version : 616.3
1747
1748 For a trading organization , none of the taxes were proportioned based on the split quantity ,
1749 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.
1750
1751 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.
1752
1753 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.
1754
1755
1756 17.24/09/2003 Aiyer Bug #3139718 File Version : 616.4
1757
1758 Added the cursor get_conv_detail_cur to get the actual_shipment_date from oe_order_lines_all instead of the
1759 conversion_date from oe_order_headers_all.
1760 As a sales order shipment date can be different from its creation date, hence the conversion rate
1761 applicable on the date of shipment should be considerd for all processing rather than the creation
1762 date of the Sales order.
1763 Added the procedure call jai_cmn_utils_pkg.currency_conversion to calculated the currency conversion rate.
1764 Also changed the population logic of v_func_tax_amt variable, such that
1765 during shipping the functional tax amount gets recalculated from the tax_amount column in JAI_OM_OE_SO_TAXES
1766 and hence logic is ->
1767 v_func_tax_amt = v_tax_amt * nvl((v_curr_conv_rate ,1)
1768
1769 Fix Of Bug#3158282:-
1770 Issue:-
1771 In case of non INR type of transactions with Excise type of tax the rounding precision should be maintained at 0
1772 and in all other cases the rounding factor should be picked up from JAI_CMN_TAXES_ALL.
1773
1774 Solution:-
1775 Modified the rounding factor to reflect the above scenario. variable v_func_tax_amount gets
1776 rounded of to zero in case of non INR type of transactions ( v_curr_conv_rate <> 1) with Excise type of tax
1777 and for all other cases rounding precession is picked up from JAI_CMN_TAXES_ALL table.
1778
1779 Dependency Introduced Due to this Bug : -
1780 None
1781
1782 18. 21/01/2004 ssumaith bug # 3390174 618.1
1783
1784 Issue :- In a trading organzation , when match receipts is done, taxes which are
1785 dependent on adhoc excise taxes was not getting recalculated based on the
1786 value of the excise tax retreived from matching.
1787
1788 This issue has been resolved by commenting out the condition which is
1789 documented by this bug number.
1790
1791 19.31/03/2004 ssumaith - bug# 3541960 file version 619.1
1792
1793 Issue :- when an lc enabled order is split and shipped , interface trip stop was going into error.
1794 The reason for this error is because for a delivery , the sum of matched quantity in the
1795 JAI_OM_LC_MATCHINGS table is compared to the quantity shipped for the delivery detail id being processed.
1796
1797 Solution :- This issue is solved by comparing the delivery_detail_id also when getting the matched quantity.
1798 By including the delivery detail in the where clause , ensuring that in case of split orders
1799 also the shipment can go through without any errors.
1800
1801 code change has been done in the cursor - c_matched_qty_cur
1802
1803
1804 20.04/05/2004 ssumaith - bug# 3609172 file version 619.2
1805
1806 issue :- In a trading scenario, when matching happens post split of a delivery either intentionally
1807 or due to lot controls , the non-excise taxes are getting incorrectly calculated.
1808 Analysis is that post split when matching happens, there are sets of tax records for each split
1809 in the delivery.When this trigger processess the delivery detail , it again apportions based
1810 on the quantity causing the taxes to be calculated as less than that of the actual value
1811
1812 Solution :- Issue has been resolved by doing the following :
1813 The apportion based on quantity shipped has been removed and instead the tax amount from
1814 the JAI_CMN_MATCH_TAXES table is used for population into JAI_OM_WSH_LINES_ALL
1815 and jain_so_picking_tax_lines table
1816
1817 Dependency : None
1818
1819 21.23/08/2004 Aiyer - bug# 3844145 file version 115.1
1820 Issue:-
1821 The inventory trip stop concurrent program errors out for a Internal sales order with qty being matched to two different subinventories.
1822
1823 Reason :-
1824 The existing code was considering matched quantites as per the subinventories. This should not be considered.
1825 Matched quantities should be considered irrespective of the subinventories.
1826
1827 Solution:-
1828 Modified the cursor matched_receipt_cur1 in the current trigger.Removed the group by subinventory clause from the query.
1829
1830 Dependency Introduced Due to this Bug : -
1831 None
1832
1833
1834 22. 29/Nov/2004 Aiyer for bug#4035566. Version#115.2
1835 Issue:-
1836 The trigger should not get fired when the non-INR based set of books is attached to the current operating unit
1837 where transaction is being done.
1838
1839 Fix:-
1840 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
1841 NON-INR
1842
1843 Dependency Due to this Bug:-
1844 The current trigger becomes dependent on the function jai_cmn_utils_pkg.check_jai_exists version 115.0 introduced through the bug 4033992
1845
1846
1847 23. 2005/02/11 ssumaith - bug# 4171272 - File version 115.3
1848
1849 Shipment needs to be stopped if education cess is not available.
1850
1851 The basic business logic validation is that both cess and excise should be available as
1852 part of the same register type and the precedence setup at the organization additional information
1853 needs to be considered for picking up the correct register order.
1854
1855 This code object calls the functions ja_in_exc_balance_amt_f and ja_in_exc_exempt_balance_amt_f
1856 which have had changes in their signature and hence the caller also needs to pass the correct
1857 parameters.
1858
1859 The change done in this object is to pass the additional parameters correctly to the functions.
1860
1861 Dependency Due to this Bug:-
1862 The current trigger becomes dependent on the functions jai_om_wsh_processing_pkg.excise_balance_check (version 115.1) and
1863 jai_om_wsh_pkg.get_excise_register_with_bal (version 115.1) also packaged as part of this bug.
1864
1865 23. 2005/03/15 brathod - Bug#4215808- File version 115.5
1866 Trigger modified for VAT Implementation.
1867 New VAT fields in JAI_OM_WSH_LINES_ALL are populated by fetching them
1868 from JAI_OM_OE_SO_LINES table. Also populated jai_vat_preocessing_t temporery
1869 table for VAT invoice number generation
1870
1871 24 26/04/2005 Brathod for Bug# 4299606 File Version 116.1
1872 Issue:-
1873 Item DFF Elimination
1874 Fix:-
1875 Changed the code that references attributeN (where N=1,2,3,4,5,15) of
1876 mtl_system_items to corrosponding columns in JAI_INV_ITM_SETUPS
1877
1878 Dependency :-
1879 IN60106 + 4239736 (Service Tax) + 4245089 (VAT)
1880
1881 25 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
1882 DB Entity as required for CASE COMPLAINCE. Version 116.1
1883
1884 26. 13-Jun-2005 File Version: 116.2
1885 Ramananda for bug#4428980. Removal of SQL LITERALs is done
1886
1887 27 30/Jan/2007 bgowrava, forward porting Bug#5631784 (4742259) - File Version 116.2
1888 Modified the trigger for TCS Enahancement.
1889 Changes are made to support following functionalities required for TCS
1890 1. Whenver a Sales Order has TCS type of tax, depending upon the setup done for threshold
1891 Surcharge type of taxes needs to be defaulted at the time of shipping
1892 The following are the logical steps
1893 a. Check if tcs type of taxes exists
1894 b. If yes, check the current threshold slab
1895 c. If threshold up (not null threshold_slab_id) then derrive the tax_category_id attached
1896 in the threshold setup for the slab
1897 d. Based on the tax category (p_threshold_tax_cat_id) defaul the additional taxes by calling
1898 JAI_RGM_THHOLD_PROC_PKG.DEFAULT_THHOLD_TAXES
1899 e. Added the call to jai_ar_tcs_rep_pkg.wsh_interim_accounting to do the interim accounting for TCS type of taxes.
1900
1901 28. 16/Apr/2007 Bgowrava for forward porting bug#5989740 11i Bug#5907436 - File version
1902 ENH : HANDLING SECONDARY AND HIGHER EDUCATION CESS
1903 additional cess of 1% on all taxes to be levied to fund secondary education and higher
1904 education .
1905 Changes - -
1906 Object Type Object Name Changes
1907 -----------------------------------------------------------------------
1908 Cursor c_sh_cess_amount Cursor is added to get cess amount for seconday and higher cess
1909
1910 Code is added to check balances for secondary and higher educat
1911
1912 29. 17/May/2007 CSahoo for bug 5680459, File Version 120.5
1913 Forward porting of 11i BUG#5645003
1914 modified the cursor c_matched_qty_cur to filter the data only by delivery_detail_id
1915 and order header_id and removed the filter by order line_id
1916 Added the cursors c_order_line and c_lc_mtch_dlry_line, to check if the order line attached
1917 the delivery detail in LC matching table is same or not. If not, then the related delivery_detail
1918 line is updated with the pr_new.source_line_id
1919 Added the cursors c_chk_vat_reversal.
1920 modified the code in ARU_T3
1921
1922 30 09/10/2007 ssumaith - bug#6487667 - File version - 120.6
1923
1924 When comparing the register balance, the amount in INR is not compared. Instead the amount in the Fc is compared.
1925 This has been corrected by multiplying the v_tot_excise_amt with the currency conversion factor.
1926
1927 31. 16/10/2007 CSahoo for bug#6498072, File Version 120.8
1928 Modified the p_assessable_value parameter during call to JAI_RGM_THHOLD_PROC_PKG.DEFAULT_THHOLD_TAXES
1929 p_assessable_value => nvl(v_assessable_value * v_shipped_quantity, 0)
1930 Moved cursor uom_code to the start of the loop to fetch v_order_quantity_uom
1931
1932 32. 16/10/2008 CSahoo for bug#5189432, File Version 120.9.12010000.2
1933 Assigned the sysdate to the variable v_creation_date instead of pr_new.creation_date
1934
1935 33. 13/11/2008 JMEENA for bug#6280735( FP6164922)
1936 Issue:WRONG EXCISE DUTY PASSED TO DELY WHILE MATCHING RG23D REGISTER.
1937 Fix: 1. The issue was beacuse a conversion factor was getting multiplied to the tax amount while matching.
1938 Hence the excise duty reflected was wrong. so while matching the receipts the conversion factor should be 1
1939 and for other cases it should be as it is. Thus modified the code for the same.
1940 assigned the v_conversion_rate to 1 in case of matching and for
1941 manufacturing organization Inv_Convert.inv_um_conversion should be executed. so included this logic in the else part.
1942 2. Modified the Get_Tax_Lines_Details_Cur cursor. changed the rounding factor to 2 when the
1943 rounding factor is null.
1944
1945
1946 Future Dependencies For the release Of this Object:-
1947 (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/
1948 A datamodel change )
1949
1950 ----------------------------------------------------------------------------------------------------------------------------------------------------
1951 Current Version Current Bug Dependent Files Version Author Date Remarks
1952 Of File On Bug/Patchset Dependent On
1953 ja_in_wsh_dlry_dtls_au_trg.sql
1954 ----------------------------------------------------------------------------------------------------------------------------------------------------
1955 616.2 3021588 IN60104D1 + ssumaith 22/08/2003 Bond Register Enhancement
1956 2801751 +
1957 2769440
1958
1959 115.2 4035566 IN60105D2 +
1960 4033992 ja_in_util_pkg_s.sql 115.0 Aiyer 29-Nov-2004 Call to this function.
1961 ja_in_util_pkg_b.sql 115.0
1962 115.3 4171272 IN60106 +
1963 4147608 ja_in_exc_exempt_balance_amt_f.sql 115.1 ssumaith 11/02/2005 New parameters added to function.
1964 ja_in_exc_balance_amt_f.sql 115.1 ssumaith 11/02/2005 New parameters added to function.
1965
1966 115.5 4215808 IN60106 All VAT Objects
1967 +4245089
1968
1969 ------------------------------------------------------------------------------------------------------------------------------------------------*/
1970
1971 --File.Sql.35 Cbabu
1972 v_creation_date := sysdate; -- replaced pr_new.Creation_Date by sysdate for bug#5189432
1973 v_created_by :=pr_new.Created_By;
1974 v_last_update_date :=pr_new.Last_Update_Date;
1975 v_last_updated_by :=pr_new.Last_Updated_By;
1976 v_last_update_login :=pr_new.Last_Update_Login;
1977 v_delivery_detail_id :=pr_new.Delivery_Detail_Id;
1978 v_source_header_id :=pr_new.Source_Header_Id;
1979 v_source_line_id :=pr_new.Source_Line_Id;
1980 v_Inventory_Item_Id :=pr_new.Inventory_Item_Id;
1981 v_Requested_Quantity_Uom :=pr_new.Requested_Quantity_Uom;
1982 v_org_id :=pr_new.ORG_ID;
1983 v_customer_id :=pr_new.CUSTOMER_ID;
1984 v_source_header_type_id :=pr_new.SOURCE_HEADER_TYPE_ID;
1985 v_subinventory :=pr_new.SUBINVENTORY;
1986 v_released_status :=pr_new.Released_Status;
1987 v_ordered_quantity := NVL(pr_new.Requested_Quantity,0);
1988 v_shipped_quantity := NVL(pr_new.Shipped_Quantity,0);
1989 v_Organization_Id :=pr_new.Organization_Id;
1990 v_basic_excise_duty_amount := 0;
1991 v_add_excise_duty_amount := 0;
1992 v_oth_excise_duty_amount := 0;
1993 v_excise_amount := 0;
1994 v_left_shipped_qty := 0;
1995 v_rg23d_tax_amount := 0;
1996 v_rg23d_base_tax_amount := 0;
1997 v_rg23d_func_tax_amount := 0;
1998 v_tax_amt := 0;
1999 v_base_tax_amt := 0;
2000 v_func_tax_amt := 0;
2001 counter :=0;
2002 v_debug_flag := jai_constants.no;
2003 v_conversion_rate := 0;
2004 v_matched_qty := 0;
2005 v_lc_shipped_qty := pr_new.Shipped_quantity;
2006
2007
2008 IF v_debug_flag ='Y' THEN
2009 BEGIN
2010 pv_return_code := jai_constants.successful ;
2011 SELECT DECODE(SUBSTR (value,1,INSTR(value,',') -1),NULL,
2012 Value,SUBSTR (value,1,INSTR(value,',') -1))
2013 INTO v_utl_location
2014 FROM v$parameter
2015 WHERE name = 'utl_file_dir';
2016 EXCEPTION
2017 WHEN OTHERS THEN
2018 v_debug_flag:='N';
2019 END;
2020 END IF;
2021
2022 IF v_debug_flag ='Y' THEN
2023 v_myfilehandle := UTL_FILE.FOPEN(v_utl_location,'ja_in_wsh_dlry_dtls_au_trg.LOG','A');
2024 UTL_FILE.PUT_LINE(v_myfilehandle,'************************START************************************');
2025 UTL_FILE.PUT_LINE(v_myfilehandle,'The TIME Stamp this ENTRY IS Created IS ' ||TO_CHAR(SYSDATE,'DD/MM/RRRR HH24:MI:SS'));
2026 END IF;
2027
2028 /*
2029 || Code added by aiyer for the bug 4035566
2030 || Call the function jai_cmn_utils_pkg.check_jai_exists to check the current set of books in INR/NON-INR based.
2031 */
2032 --IF jai_cmn_utils_pkg.check_jai_exists ( p_calling_object => 'JA_IN_WSH_DLRY_DTLS_AU_TRG' ,
2033 -- p_org_id => pr_new.org_id
2034 -- ) = FALSE
2035 --THEN
2036 /*
2037 || return as the current set of books is NON-INR based
2038 */
2039 -- RETURN;
2040 --END IF;
2041
2042 OPEN get_ship_to_org_id_cur( v_source_line_id);
2043 FETCH get_ship_to_org_id_cur INTO v_ship_to_org_id;
2044 CLOSE get_ship_to_org_id_cur;
2045
2046 /*
2047 This code is added by aiyer for the bug #3139718
2048 Added the check that the trigger should be bypassed in case the functional currency code is NON INR.
2049 */
2050 IF pr_new.org_id IS NOT NULL THEN
2051 /* Bug 5243532. Added by Lakshmi Gopalsami
2052 Removed the reference to cursor sob_cur and implemented using
2053 caching logic.
2054 */
2055
2056 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
2057 (p_org_id => pr_new.org_id);
2058 v_currency_code := l_func_curr_det.currency_code;
2059 v_set_of_books_id := l_func_curr_det.ledger_id;
2060 -- end for bug 5243532
2061 END IF;
2062
2063 -- Check for the Delivery Status
2064 OPEN Get_Status_Cur;
2065 FETCH Get_Status_Cur INTO v_delivery_id, v_date_confirmed, v_status_code;
2066 CLOSE Get_Status_Cur;
2067 IF NVL(v_status_code,'#') NOT IN ('CO', 'IT','CL') THEN
2068 RETURN;
2069 END IF; --1
2070 --2001/05/31 Anuradha Parthasarathy
2071 IF NVL(pr_new.shipped_quantity,0) = 0 THEN
2072 RETURN;
2073 END IF;
2074 -- Check whether Line Details exists in Localization table.
2075 OPEN Get_So_Lines_Count_Cur;
2076 FETCH Get_So_Lines_Count_Cur INTO v_so_lines_count;
2077 CLOSE Get_So_Lines_Count_Cur ;
2078 IF v_so_lines_count = 0 THEN --2
2079 RETURN;
2080 END IF; --2
2081 -- Fetch Lines Details from Localization Table
2082 OPEN Get_So_Lines_Details_Cur;
2083 FETCH Get_So_Lines_Details_Cur INTO
2084 v_selling_price,
2085 v_quantity,
2086 v_tax_category_id,
2087 v_assessable_value,
2088 ln_vat_assessable_value,
2089 v_excise_exempt_type,
2090 v_excise_exempt_refno,
2091 v_excise_exempt_date,
2092 -- Added by Brathod for Bug#4215808
2093 lv_vat_exemption_flag,
2094 lv_vat_exemption_type,
2095 ld_vat_exemption_date,
2096 lv_vat_exemption_refno;
2097 -- End of Bug#4215808
2098 CLOSE Get_So_Lines_Details_Cur;
2099 --Get The Location Id
2100 OPEN Location_Cursor;
2101 FETCH Location_Cursor INTO v_location_id, v_trading_flag;
2102 CLOSE Location_Cursor;
2103
2104 OPEN Trading_register_code_cur(v_organization_id, v_location_id,v_delivery_detail_id, v_source_header_type_id);
2105 FETCH Trading_register_code_cur INTO v_trad_register_code;
2106 CLOSE Trading_register_code_cur;
2107
2108 --2001/07/10 Anuradha Parthasarathy
2109 OPEN item_trading_cur;
2110 FETCH item_trading_cur INTO v_item_trading_flag;
2111 CLOSE item_trading_cur;
2112 OPEN get_item_attributes;
2113 FETCH get_item_attributes INTO v_exe_flag,v_mod_flag;
2114 CLOSE get_item_attributes;
2115 /*
2116 Code modified by aiyer for the bug#3032569
2117 Modified the IF statment to raise an error only when trading register code is in 23D_DOMESTIC_EXCISE and 23D_EXPORT_EXCISE.
2118 The other two trading register_codes '23D_DOM_WITHOUT_EXCISE' and '23D_EXPORT_WITHOUT_EXCISE' have been removed from this if statement
2119 as matched receipts is not relevant in this case.
2120 */
2121
2122 -- Start of Bug #3032569
2123
2124 IF v_trad_register_code IN(
2125 '23D_DOMESTIC_EXCISE',
2126 '23D_EXPORT_EXCISE'
2127 )
2128 THEN
2129 -- End of Bug #3032569
2130
2131 IF NVL(v_trading_flag,'N') = 'Y' AND NVL(V_item_trading_flag,'N') = 'Y' AND NVL(v_exe_flag,'N')= 'Y' THEN
2132 OPEN matched_receipt_cur1;
2133 FETCH matched_receipt_cur1 INTO v_matched_qty;
2134 CLOSE matched_receipt_cur1;
2135 IF NVL(v_shipped_quantity,0) <> NVL(v_matched_qty,0) THEN
2136 /* RAISE_APPLICATION_ERROR(-20401, 'Matched Quantity -- ' || TO_CHAR(NVL(v_matched_qty,0)) ||
2137 ' 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)) ||
2138 ' should be equal to Shipped Quantity -- ' || TO_CHAR(NVL(v_shipped_quantity,0)) ; return ;
2139
2140 END IF;
2141 END IF;
2142 END IF;
2143 /*
2144 THE FOLLOWING LINES ADDED BY SRIRAM - LC FUNCTIONALITY - BUG# 2165355 - 19/09/2002
2145 */
2146 OPEN C_CHECK_LC_ORDER;
2147 FETCH C_CHECK_LC_ORDER INTO v_check_lc_order;
2148 CLOSE C_CHECK_LC_ORDER;
2149 IF NVL(v_check_lc_order,'N') = 'Y' THEN
2150 OPEN C_MATCHED_QTY_CUR;
2151 FETCH C_MATCHED_QTY_CUR INTO v_lc_qty_matched;
2152 CLOSE C_MATCHED_QTY_CUR;
2153 IF NVL(v_lc_qty_matched,-999) <> NVL(v_lc_shipped_qty,-888) THEN
2154 /* RAISE_APPLICATION_ERROR(-20120, 'LC Matched Quantity -- ' || TO_CHAR(NVL(v_lc_qty_matched,0)) ||
2155 ' should be equal to Shipped Quantity -- ' || TO_CHAR(NVL(v_lc_shipped_qty,0)) || ' for LC enabled Orders'); */
2156 pv_return_code := jai_constants.expected_error ; pv_return_message := 'LC Matched Quantity -- ' || TO_CHAR(NVL(v_lc_qty_matched,0)) ||
2157 ' should be equal to Shipped Quantity -- ' || TO_CHAR(NVL(v_lc_shipped_qty,0)) || ' for LC enabled Orders' ; return ;
2158 END IF;
2159
2160 /* Start, bug#5680459 csahoo
2161 following code is to correct the existing lc_matching order line to new split line id
2162 */
2163 open c_order_line;
2164 fetch c_order_line into r_order_line;
2165 close c_order_line;
2166
2167 ln_lc_update_cnt := -1;
2168 if r_order_line.split_from_line_id is not null then
2169 open c_lc_mtch_dlry_line;
2170 fetch c_lc_mtch_dlry_line into r_lc_mtch_dlry_line;
2171 close c_lc_mtch_dlry_line;
2172 if pr_new.source_line_id <> r_lc_mtch_dlry_line.order_line_id then
2173
2174 update JAI_OM_LC_MATCHINGS
2175 set order_line_id = pr_new.source_line_id
2176 where delivery_detail_id = pr_new.Delivery_Detail_Id
2177 -- and order_line_id = r_order_line.split_from_line_id
2178 and release_flag is null;
2179 ln_lc_update_cnt := sql%rowcount;
2180 end if;
2181
2182 end if;
2183
2184 /* End, bug#5680459 csahoo */
2185
2186
2187 UPDATE JAI_OM_LC_MATCHINGS
2188 SET RELEASE_FLAG = 'Y'
2189 WHERE DELIVERY_DETAIL_ID = pr_new.Delivery_Detail_id;
2190 END IF;
2191 /*
2192 ENDS HERE - CHANGES BY SRIRAM FOR LC FUNCTIONALITY - BUG # 2165355 - 19/09/2002
2193 */
2194
2195 -- Start of code for bug #3139718
2196 OPEN get_conv_detail_cur;
2197 FETCH get_conv_detail_cur INTO v_currency_code, v_conv_type_code,v_conv_rate, v_conv_date;
2198
2199 IF get_conv_detail_cur%FOUND THEN
2200
2201 v_curr_conv_rate := jai_cmn_utils_pkg.currency_conversion (
2202 v_set_of_books_id ,
2203 v_currency_code ,
2204 v_conv_date ,
2205 v_conv_type_code ,
2206 v_conv_rate
2207 );
2208 END IF;
2209 -- End of code for bug #3139718
2210
2211 CLOSE get_conv_detail_cur;
2212
2213 -- Start Inserting Tax Lines
2214 FOR Rec IN Get_Tax_Lines_Details_Cur
2215 LOOP
2216 counter:=counter+1;
2217
2218 /* Moved the code from below else part to here for bug# 6498072 */
2219 OPEN uom_code;
2220 FETCH uom_code INTO v_order_quantity_uom;
2221 CLOSE uom_code;
2222
2223 --2001/06/08 Anuradha Parthasarathy
2224 /*
2225 This if statement has been modified by aiyer for the bug #2988829.
2226 As the tax amount, base_tax_amount and func_tax_amount would be present in table JAI_CMN_MATCH_TAXES
2227 only when match receipt functionality has been done .
2228 Now in order to check that the match receipts functionality has been performed the following check has been added in additions
2229 to the other three checks (of organization being a trading organization, Item being tradable and excisable ):-
2230 The order attached to the bond register is one of 'Trading Domestic With Excise' or 'Export With Excise'
2231 hence applying the check that
2232 v_trad_register_code IN ( '23D_DOMESTIC_EXCISE','23D_EXPORT_EXCISE') .
2233 Before this fix, Null value used to get inserted into taxes in table JAI_OM_WSH_LINE_TAXES for Bond register with
2234 'Trading Domestic Without Excise' and 'Export Without Excise' ( problem stated in the bug )
2235 */
2236 IF NVL(v_trading_flag,'N') = 'Y' AND
2237 NVL(v_item_trading_flag,'N') = 'Y' AND
2238 NVL(v_exe_flag,'N') = 'Y' AND
2239 v_trad_register_code IN ( '23D_DOMESTIC_EXCISE','23D_EXPORT_EXCISE')
2240 THEN
2241
2242 OPEN rg23d_amount_cur(rec.tax_id);
2243 FETCH rg23d_amount_cur INTO v_tax_amt,v_base_tax_amt,v_func_tax_amt;
2244 CLOSE rg23d_amount_cur;
2245
2246 v_conversion_rate := 1; -- conversion_rate should be 1 in case of trading, added by JMEENA for bug#6280735 (FP 6164922)
2247 ELSE
2248 /*
2249 this control comes here for manufacturing - all scenarios
2250 */
2251 v_tax_amt := (v_shipped_quantity * (rec.tax_amount/v_quantity)) ;
2252 v_base_tax_amt := (v_shipped_quantity * (rec.base_tax_amount/v_quantity)) ;
2253 /*
2254 Code modified by aiyer for the bug 3139718
2255 As the Conversion rate can be different while the sales order was booked and when the sales order would be shipped.
2256 So during shipping the functional tax amount needs to be recalculated from the tax_amount column in JAI_OM_OE_SO_TAXES
2257 , hence setting the v_func_tax_amt = v_tax_amt * nvl((v_curr_conv_rate ,1)
2258 */
2259 v_func_tax_amt := (v_tax_amt * nvl(v_curr_conv_rate,1)) ; -- added by ssumaith - bug#3609172
2260 --END IF; Commented by JMEENA for bug#6280735
2261
2262 -- Proportionate the Tax Amounts as per the New Shipped Quantity
2263 -- and round it off according to the Rounding Factor Defined.
2264 -- 2001/10/03 Anuradha Parthasarathy
2265
2266 --Moved the following code to the start of the loop for bug# 6498072
2267 --OPEN uom_code;
2268 --FETCH uom_code INTO v_order_quantity_uom;
2269 --CLOSE uom_code;
2270
2271 /*included the UOM conversion logic into the else part Earlier it was outside the end of If condition.
2272 added by JMEENA for bug#6280735 (FP 6164922) */
2273 Inv_Convert.inv_um_conversion(v_Requested_Quantity_Uom,
2274 v_order_quantity_uom,
2275 v_inventory_item_id,
2276 v_conversion_rate);
2277 IF NVL(v_conversion_rate, 0) <= 0 THEN
2278 Inv_Convert.inv_um_conversion(v_Requested_Quantity_Uom,
2279 v_order_quantity_uom,
2280 0,
2281 v_conversion_rate);
2282 IF NVL(v_conversion_rate, 0) <= 0 THEN
2283 v_conversion_rate := 0;
2284 END IF;
2285 END IF;
2286 END IF; --Added by JMEENA for bug#6280735
2287 -- the following section added by sriram on 24-aug-02 bug # 25310103
2288 -- this was done because - it will ensure that Line splitting \ Backordering is not supported only
2289 -- when the Organization as well as Item are both Trading which is a
2290 -- requirement when match receipt funtionality for RG23D is being used.
2291
2292 -- code reorg done by sriram - bug#3609172 - for old code refer to rcs version 619.1
2293
2294 v_tax_amount := ROUND((v_tax_amt) * v_conversion_rate,rec.rounding_factor);
2295 v_base_tax_amount := ROUND((v_base_tax_amt) * v_conversion_rate,rec.rounding_factor);
2296
2297 IF NVL(v_trading_flag,'N') = 'Y' AND
2298 NVL(v_item_trading_flag,'N') = 'Y' AND
2299 NVL(v_exe_flag,'N') = 'Y' AND
2300 v_trad_register_code IN ( '23D_DOMESTIC_EXCISE','23D_EXPORT_EXCISE')
2301 AND Rec.Tax_type like '%Excise%' -- Excise added into the if by Sriram for Bug# 3148621 17/09/2003
2302 THEN
2303 v_func_tax_amount := ROUND((v_func_tax_amt)* v_conversion_rate, rec.rounding_factor);
2304 ELSE
2305 IF v_curr_conv_rate <> 1 AND rec.tax_type like '%Excise%' THEN
2306 v_func_tax_amount := ROUND((v_func_tax_amt)* v_conversion_rate,0);
2307 ELSE
2308 v_func_tax_amount := ROUND((v_func_tax_amt)* v_conversion_rate, rec.rounding_factor);
2309 END IF;
2310 END IF;
2311
2312
2313 -- Accumulate the respective types of Excise Duties
2314 -- for inserting into JAI_OM_WSH_LINES_ALL Table.
2315 IF rec.tax_type = 'Excise' THEN --3
2316 v_basic_excise_duty_amount := NVL(v_basic_excise_duty_amount,0) + v_tax_amount ;
2317 ELSIF rec.tax_type = 'Addl. Excise' THEN --3
2318 v_add_excise_duty_amount := NVL(v_add_excise_duty_amount,0) + v_tax_amount ;
2319 ELSIF rec.tax_type = 'Other Excise' THEN --3
2320 v_oth_excise_duty_amount := NVL(v_oth_excise_duty_amount,0) + v_tax_amount ;
2321 END IF;
2322
2323 IF v_debug_flag ='Y' THEN
2324 UTL_FILE.PUT_LINE(v_myfilehandle,'1 v_basic_excise_duty_amount -> '||v_basic_excise_duty_amount
2325 ||', v_add_excise_duty_amount -> '|| v_add_excise_duty_amount
2326 ||', v_oth_excise_duty_amount -> '|| v_oth_excise_duty_amount
2327 );
2328 END IF; --3
2329 -- Check for the existence of Tax Lines in JAI_OM_WSH_LINE_TAXES
2330 OPEN Pick_Tax_Line_Count_Cur(rec.tax_id);
2331 FETCH Pick_Tax_Line_Count_Cur INTO v_picking_tax_lines_count;
2332 CLOSE Pick_Tax_Line_Count_Cur;
2333 IF v_picking_tax_lines_count = 0 THEN --4
2334 INSERT INTO JAI_OM_WSH_LINE_TAXES(Delivery_Detail_Id,
2335 Tax_Line_No,
2336 Precedence_1,
2337 Precedence_2,
2338 Precedence_3,
2339 Precedence_4,
2340 Precedence_5,
2341 Precedence_6, -- CHANGE ON 02/11/2006 BY SACSETHI FOR BUG 5228046
2342 Precedence_7,
2343 Precedence_8,
2344 Precedence_9,
2345 Precedence_10,
2346 Tax_Id,
2347 Tax_Rate,
2348 Qty_Rate,
2349 Uom,
2350 Tax_Amount,
2351 Base_Tax_Amount,
2352 Func_Tax_Amount,
2353 Creation_Date,
2354 Created_By,
2355 Last_Update_Date,
2356 Last_Updated_By,
2357 Last_Update_Login)
2358 VALUES (
2359 v_delivery_detail_id,
2360 rec.Tax_Line_No,
2361 rec.Precedence_1,
2362 rec.Precedence_2,
2363 rec.Precedence_3,
2364 rec.Precedence_4,
2365 rec.Precedence_5,
2366 rec.Precedence_6, -- CHANGE ON 02/11/2006 BY SACSETHI FOR BUG 5228046
2367 rec.Precedence_7,
2368 rec.Precedence_8,
2369 rec.Precedence_9,
2370 rec.Precedence_10,
2371 rec.Tax_id,
2372 rec.Tax_rate,
2373 rec.Qty_Rate,
2374 rec.Uom,
2375 v_tax_amount,
2376 v_base_tax_amount,
2377 v_func_tax_amount,
2378 v_creation_date,
2379 v_created_by,
2380 v_last_update_date,
2381 v_last_updated_by,
2382 v_last_update_login
2383 );
2384 ELSE --4
2385 UPDATE JAI_OM_WSH_LINE_TAXES
2386 SET Tax_Amount = v_tax_amount,
2387 Last_Update_Date = v_last_update_date,
2388 Last_Updated_By = v_last_updated_by,
2389 Last_Update_Login = v_last_update_login
2390 WHERE Delivery_Detail_Id = v_delivery_detail_id
2391 AND Tax_Id = rec.Tax_Id;
2392 END IF; --4
2393 END LOOP;
2394
2395
2396 /** Added by Bgowrava for forward porting Bug#5631784 , TCS Enh.*/
2397
2398 /**
2399 Aim: Populate TCS Surcharge and Surcharge cess type of taxes if threshold level is high.
2400
2401 Check if TCS type of taxes exists, If yes using the threshold API found out the slab and the tax category id
2402 and delegate the call tax defaultation API
2403 */
2404
2405 ln_tcs_exists := 0; --kunkumar for bug#5604375 and 6066750
2406 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Checking if TCS type of tax exists?'); */ --commented by bgowrava for bug#5631784
2407 open c_chk_rgm_tax_exists ( cp_regime_code => jai_constants.tcs_regime
2408 , cp_delivery_detail_id => v_delivery_detail_id
2409 );
2410 fetch c_chk_rgm_tax_exists into ln_tcs_exists;
2411 close c_chk_rgm_tax_exists ;
2412
2413 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'ln_tcs_exists='||ln_tcs_exists); */ --commented by bgowrava for bug#5631784
2414 if nvl(ln_tcs_exists,0) >0 then --kunkumar for bug#5604375 and 6066750
2415 /* TCS type of tax is present */
2416 fnd_file.put_line(FND_FILE.LOG,'Localization' );
2417
2418 open c_get_regime_id (cp_regime_code => jai_constants.tcs_regime);
2419 fetch c_get_regime_id into ln_tcs_regime_id;
2420 close c_get_regime_id;
2421 /* Find out what is the current slab */
2422 /* 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
2423 jai_rgm_thhold_proc_pkg.get_threshold_slab_id
2424 (
2425 p_regime_id => ln_tcs_regime_id
2426 , p_organization_id => v_organization_id
2427 , p_party_type => jai_constants.party_type_customer
2428 , p_party_id => v_customer_id
2429 , p_org_id => v_org_id
2430 , p_source_trx_date => v_date_confirmed
2431 , p_threshold_slab_id => ln_threshold_slab_id
2432 , p_process_flag => lv_process_flag
2433 , p_process_message => lv_process_message
2434 );
2435 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Process Result for JAI_RGM_THHOLD_PROC_PKG.GET_THRESHOLD_SLAB_ID');
2436 jai_cmn_debug_contexts_pkg.print (ln_reg_id,
2437 'lv_process_flag =' ||lv_process_flag||chr(10)
2438 ||'lv_process_message =' ||lv_process_message
2439 ); */ --commented by bgowrava for bug#5631784
2440
2441 if lv_process_flag <> jai_constants.successful then
2442 app_exception.raise_exception
2443 (exception_type => 'APP'
2444 ,exception_code => -20275
2445 ,exception_text => lv_process_message
2446 );
2447 end if;
2448
2449 if ln_threshold_slab_id is not null then
2450 /* Threshold level is up. Surcharge needs to be defaulted , so find out the tax category based on the threshold slab */
2451 jai_rgm_thhold_proc_pkg.get_threshold_tax_cat_id
2452 (
2453 p_threshold_slab_id => ln_threshold_slab_id
2454 , p_org_id => v_org_id
2455 , p_threshold_tax_cat_id => ln_threshold_tax_cat_id
2456 , p_process_flag => lv_process_flag
2457 , p_process_message => lv_process_message
2458 );
2459
2460 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Process Result for JAI_RGM_THHOLD_PROC_PKG.GET_THRESHOLD_TAX_CAT_ID');
2461 jai_cmn_debug_contexts_pkg.print (ln_reg_id
2462 ,'lv_process_flag =' ||lv_process_flag||chr(10)
2463 ||'lv_process_message =' ||lv_process_message
2464 ); */ --commented by bgowrava for bug#5631784
2465
2466
2467 if lv_process_flag <> jai_constants.successful then
2468 app_exception.raise_exception
2469 (exception_type => 'APP'
2470 ,exception_code => -20275
2471 ,exception_text => lv_process_message
2472 );
2473 end if;
2474
2475 /* Get line number after which threshold taxes needs to be defaulted */
2476 select max(tax_line_no)
2477 into ln_last_line_no
2478 from JAI_OM_WSH_LINE_TAXES
2479 where delivery_detail_id = v_delivery_detail_id;
2480
2481 /* Get line number of the base tax (tax_type=TCS) for calculating the surcharge basically to set a precedence */
2482 select max(tax_line_no)
2483 into ln_base_line_no
2484 from JAI_OM_WSH_LINE_TAXES jsptl
2485 , JAI_CMN_TAXES_ALL jtc
2486 where jsptl.delivery_detail_id = v_delivery_detail_id
2487 and jsptl.tax_id = jtc.tax_id
2488 and jtc.tax_type = jai_constants.tax_type_tcs;
2489
2490 /*
2491 ||Call the helper method to default surcharge taxes on top of the SO taxes using the tax category
2492 || The api jai_rgm_thhold_proc_pkg.default_thhold_taxes inserts lines as per the same specified in the TCS tax category
2493 || into the JAI_OM_WSH_LINE_TAXES table
2494 */
2495
2496 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Calling JAI_RGM_THHOLD_PROC_PKG.DEFAULT_THHOLD_TAXES');*/ --commented by bgowrava for bug#5631784
2497
2498 jai_rgm_thhold_proc_pkg.default_thhold_taxes
2499 (
2500 p_source_trx_id => ''
2501 , p_source_trx_line_id => v_delivery_detail_id
2502 , p_source_event => jai_constants.source_ttype_delivery
2503 , p_action => jai_constants.default_taxes
2504 , p_threshold_tax_cat_id => ln_threshold_tax_cat_id
2505 , p_tax_base_line_number => ln_base_line_no
2506 , p_last_line_number => ln_last_line_no
2507 , p_currency_code => v_currency_code
2508 , p_currency_conv_rate => v_conv_rate
2509 , p_quantity => nvl(v_shipped_quantity,0)
2510 , p_base_tax_amt => nvl((v_selling_price * v_conversion_rate) * v_shipped_quantity,0)
2511 , p_assessable_value => nvl(v_assessable_value * v_shipped_quantity, 0) /* Added v_shipped_quantity and nvl() for bug#6498072 */
2512 , p_inventory_item_id => v_inventory_item_id
2513 , p_uom_code => v_order_quantity_uom
2514 , p_vat_assessable_value => ln_vat_assessable_value
2515 , p_process_flag => lv_process_flag
2516 , p_process_message => lv_process_message
2517 );
2518
2519 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Process Result for JAI_RGM_THHOLD_PROC_PKG.DEFAULT_THHOLD_TAXES');
2520 jai_cmn_debug_contexts_pkg.print (ln_reg_id
2521 ,'lv_process_flag =' ||lv_process_flag||chr(10)
2522 ||'lv_process_message =' ||lv_process_message
2523 ); */ --commented by bgowrava for bug#5631784
2524
2525 if lv_process_flag <> jai_constants.successful then
2526 app_exception.raise_exception
2527 (exception_type => 'APP'
2528 ,exception_code => -20275
2529 ,exception_text => lv_process_message
2530 );
2531 end if;
2532
2533 end if; /* ln_threshold_slab_id is not null then */
2534
2535 end if; /** ln_tcs_exists is not null then */
2536
2537 /** End bug 5631784*/
2538
2539
2540
2541
2542
2543 -- THE FOLLOWING CODE ADDED BY SRIRAM - BUG # 2330055 - 08-may-2002
2544 -- This Code was Added because - in case there are items which do not have tax lines , the
2545 -- v_conversion_rate variable is not getting populated - so the selling price and the
2546 -- assessable value fields are being multiplied by the v_conversion_rate which is 0 initially.
2547 -- hence the addition of the following lines ensures the v_conversion_rate is calculated ,
2548 -- multiplied and correctly done with selling price and assessable value.
2549 OPEN uom_code;
2550 FETCH uom_code INTO v_order_quantity_uom;
2551 CLOSE uom_code;
2552 Inv_Convert.inv_um_conversion(v_Requested_Quantity_Uom,
2553 v_order_quantity_uom,
2554 v_inventory_item_id,
2555 v_conversion_rate);
2556 IF NVL(v_conversion_rate, 0) <= 0 THEN
2557 Inv_Convert.inv_um_conversion(v_Requested_Quantity_Uom,
2558 v_order_quantity_uom,
2559 0,
2560 v_conversion_rate);
2561 IF NVL(v_conversion_rate, 0) <= 0 THEN
2562 v_conversion_rate := 0;
2563 END IF;
2564 END IF;
2565 /*
2566 insert into debug_data (str) values ('AFTER - the value OF v_conversion_rate IS ' || to_char(v_conversion_rate));
2567 insert into debug_data (str) values ('AFTER - the value OF v_Requested_Quantity_Uom IS ' || v_Requested_Quantity_Uom);
2568 insert into debug_data (str) values ('AFTER - the value OF v_order_quantity_uom IS ' || v_order_quantity_uom);
2569 */
2570 -- debug code ends here - sriram
2571 /*
2572 code added ends here - 2330055 - 08-may-2002
2573 */
2574 OPEN get_item_dtls(v_organization_id,v_inventory_item_id);
2575 FETCH get_item_dtls INTO v_excise_flag,v_item_class;
2576 CLOSE get_item_dtls;
2577
2578 /*
2579 This if statement has been modified by aiyer for the bug #2988829.
2580 As the tax amount, base_tax_amount and func_tax_amount would be present in table JAI_CMN_MATCH_TAXES
2581 only when match receipt functionality has been done .
2582 Now in order to check that the match receipts functionality has been performed the following check has been added in additions
2583 to the other three checks (of organization being a trading organization, Item being tradable and excisable ):-
2584 The order attached to the bond register is one of 'Trading Domestic With Excise' or 'Export With Excise'
2585 hence applying the check that
2586 v_trad_register_code IN ( '23D_DOMESTIC_EXCISE','23D_EXPORT_EXCISE') .
2587 Before this fix, Null value used to get inserted into taxes in table JAI_OM_WSH_LINES_ALL for Bond register with
2588 'Trading Domestic Without Excise' and 'Export Without Excise' ( problem stated in the bug )
2589 */
2590
2591
2592 IF nvl(v_trading_flag,'N') = 'Y' AND
2593 nvl(v_item_trading_flag,'N') = 'Y' AND
2594 nvl(v_excise_flag,'N') = 'Y' AND
2595 v_trad_register_code IN ( '23D_DOMESTIC_EXCISE','23D_EXPORT_EXCISE')
2596 THEN
2597 OPEN ed_cur('Excise');
2598 FETCH ed_cur INTO v_basic_excise_duty_amount;
2599 CLOSE ed_cur ;
2600 OPEN ed_cur('Addl. Excise');
2601 FETCH ed_cur INTO v_add_excise_duty_amount;
2602 CLOSE ed_cur;
2603 OPEN ed_cur('Other Excise');
2604 FETCH ed_cur INTO v_oth_excise_duty_amount;
2605 CLOSE ed_cur;
2606 END IF;
2607 --Get Cumulative Excise amount
2608 --for inserting into JAI_OM_WSH_LINES_ALL Table.
2609 v_excise_amount := (v_basic_excise_duty_amount + v_add_excise_duty_amount +
2610 v_oth_excise_duty_amount);
2611 -- Get Total Tax Amount for the Line
2612 -- for Inserting into JAI_OM_WSH_LINES_ALL Table.
2613 OPEN Get_Tot_Tax_Amount_Cur;
2614 FETCH Get_Tot_Tax_Amount_Cur INTO v_tot_tax_amount;
2615 CLOSE Get_Tot_Tax_Amount_Cur;
2616 -- Check for Delivery lines existence in JAI_OM_WSH_LINES_ALL Table
2617 OPEN Get_Delivery_Line_Count_Cur ;
2618 FETCH Get_Delivery_Line_Count_Cur INTO v_delivery_line_count;
2619 CLOSE Get_Delivery_Line_Count_Cur ;
2620 IF v_delivery_line_count = 0 THEN
2621 --5
2622 INSERT INTO JAI_OM_WSH_LINES_ALL(
2623 Delivery_Detail_Id,
2624 Order_Header_Id,
2625 Order_Line_Id,
2626 split_from_delivery_detail_id,
2627 Selling_Price,
2628 Quantity,
2629 Assessable_value,
2630 vat_assessable_value,
2631 Tax_Category_Id,
2632 Tax_Amount,
2633 Inventory_Item_Id,
2634 Organization_Id,
2635 Location_Id,
2636 Unit_Code,
2637 Excise_Amount,
2638 Basic_Excise_Duty_Amount,
2639 Add_Excise_Duty_Amount,
2640 Oth_Excise_Duty_Amount,
2641 Excise_Exempt_Type,
2642 Excise_Exempt_Refno,
2643 Excise_Exempt_Date,
2644 Creation_Date,
2645 Created_By,
2646 Last_Update_Date,
2647 Last_Updated_By,
2648 Last_Update_Login,
2649 ORG_ID,
2650 CUSTOMER_ID,
2651 SHIP_TO_ORG_ID,
2652 ORDER_TYPE_ID,
2653 SUBINVENTORY,
2654 DELIVERY_ID,
2655 -- Added by Brathod for Bug#4215808
2656 VAT_EXEMPTION_FLAG,
2657 VAT_EXEMPTION_TYPE,
2658 VAT_EXEMPTION_DATE,
2659 VAT_EXEMPTION_REFNO
2660 -- End of Bug#4215808
2661 )
2662 VALUES ( v_delivery_detail_id,
2663 v_source_header_id,
2664 v_source_line_id,
2665 pr_new.split_from_delivery_detail_id,
2666 v_selling_price * v_conversion_rate,
2667 v_shipped_quantity,
2668 v_assessable_value * v_conversion_rate,
2669 ln_vat_assessable_value * v_conversion_rate,
2670 v_tax_category_id,
2671 v_tot_tax_amount,
2672 v_Inventory_Item_Id,
2673 v_Organization_Id,
2674 v_location_id,-- 2001/06/23 Anuradha Parthasarathy
2675 v_Requested_Quantity_Uom,
2676 v_excise_amount,
2677 v_basic_excise_duty_amount,
2678 v_add_excise_duty_amount,
2679 v_oth_excise_duty_amount,
2680 v_excise_exempt_type,
2681 v_excise_exempt_refno,
2682 v_excise_exempt_date,
2683 v_creation_date,
2684 v_created_by,
2685 v_last_update_date,
2686 v_last_updated_by,
2687 v_last_update_login,
2688 v_org_Id,
2689 v_customer_id,
2690 v_ship_to_org_id,
2691 v_source_header_type_id,
2692 v_subinventory,
2693 v_DELIVERY_ID,
2694 -- Added by Brathod for Bug#4215808
2695 lv_vat_exemption_flag,
2696 lv_vat_exemption_type,
2697 ld_vat_exemption_date,
2698 lv_vat_exemption_refno
2699 -- End of Bug#4215808
2700 );
2701
2702 -- Insert the Data Required for RG entries into a Temporary Table
2703 IF NVL(v_excise_flag,'N') = 'Y' THEN
2704 INSERT INTO JAI_OM_OE_GEN_TAXINV_T(
2705 date_released,
2706 date_confirmed,
2707 delivery_detail_id,
2708 order_header_id,
2709 creation_date,
2710 created_by,
2711 last_update_date,
2712 last_updated_by,
2713 last_update_login,
2714 delivery_id
2715 )
2716 VALUES (
2717 SYSDATE,
2718 v_date_confirmed,
2719 v_delivery_detail_id,
2720 v_source_header_id,
2721 v_creation_date,
2722 v_created_by,
2723 v_last_update_date,
2724 v_last_updated_by,
2725 v_last_update_login,
2726 v_delivery_id
2727 );
2728 END IF;
2729
2730 -- Added by brathod for Bug#4215808
2731 /*
2732 || check if VAT type of tax exists
2733 */
2734
2735 OPEN cur_chk_vat_exists (cp_del_det_id => v_delivery_detail_id) ;
2736 FETCH cur_chk_vat_exists INTO ln_vat_cnt;
2737 CLOSE cur_chk_vat_exists ;
2738
2739 OPEN cur_chk_vat_proc_entry (cp_delivery_id => v_delivery_id);
2740 FETCH cur_chk_vat_proc_entry INTO ln_vat_proc_cnt ;
2741 CLOSE cur_chk_vat_proc_entry;
2742
2743
2744 /*
2745 || Added by csahoo for bug#5680459
2746 || Check if only 'VAT REVERSAL' tax type is present in JAI_OM_WSH_LINE_TAXES
2747 */
2748 IF nvl(ln_vat_cnt,0) = 0 THEN /* If taxes of type 'VAT' are not present */
2749 lv_vat_reversal := 'VAT REVERSAL' ;
2750 OPEN c_chk_vat_reversal(cp_del_det_id => v_delivery_detail_id,
2751 cp_tax_type => lv_vat_reversal) ;
2752 FETCH c_chk_vat_reversal INTO ln_vat_reversal_exists;
2753 CLOSE c_chk_vat_reversal ;
2754
2755 /*
2756 || VAT invoice number should be punched as 'NA' and accounting should happen
2757 || when 'VAT REVERSAL' type of tax exist and 'VAT' type of tax(es) doesn't exist
2758 */
2759 lv_vat_invoice_no := jai_constants.not_applicable ;
2760 lv_vat_inv_gen_status := 'C' ;
2761 END IF ;
2762
2763 /*
2764 || Added 'OR nvl(ln_vat_reversal_exists,0) = 1' for bug#5680459
2765 || If taxes of 'VAT' type (or) taxes of 'VAT REVERSAL' type exists
2766 */
2767
2768 IF (nvl(ln_vat_cnt,0) > 0 OR nvl(ln_vat_reversal_exists,0) = 1 ) AND nvl (ln_vat_proc_cnt,0) = 0 THEN
2769 /* VAT type of tax exists*/
2770 /* Get the regime id for these type of taxes */
2771 OPEN cur_get_regime_info (cp_organization_id => v_organization_id,
2772 cp_location_id => v_location_id
2773 );
2774 FETCH cur_get_regime_info INTO ln_regime_id,
2775 lv_regns_num;
2776 CLOSE cur_get_regime_info;
2777
2778 INSERT INTO JAI_RGM_INVOICE_GEN_T ( regime_id ,
2779 delivery_id ,
2780 delivery_date ,
2781 customer_trx_id ,
2782 organization_id ,
2783 location_id ,
2784 registration_num ,
2785 vat_invoice_no ,
2786 vat_inv_gen_status ,
2787 vat_inv_gen_err_message ,
2788 vat_acct_status ,
2789 vat_acct_err_message ,
2790 request_id ,
2791 program_application_id ,
2792 program_id ,
2793 program_update_date ,
2794 party_id ,
2795 party_site_id ,
2796 party_type ,
2797 creation_date ,
2798 created_by ,
2799 last_update_date ,
2800 last_update_login ,
2801 last_updated_by
2802 )
2803 VALUES (ln_regime_id ,
2804 v_delivery_id ,
2805 v_creation_date ,
2806 null , -- customer_trx_id
2807 v_organization_id ,
2808 v_location_id ,
2809 lv_regns_num ,
2810 lv_vat_invoice_no , -- vat_invoice_no --Replaced NULL with lv_vat_invoice_no for bug#5680459
2811 nvl(lv_vat_inv_gen_status, 'P'), -- vat_inv_gen_status --Added nvl() for bug#5680459
2812 null , -- vat_inv_gen_err_message
2813 'P' , -- vat_acct_status
2814 null , -- vat_acct_err_message
2815 null , -- request_id
2816 null , -- program_application_id
2817 null , -- program_id
2818 null , -- program_update_date
2819 pr_new.customer_id ,
2820 v_ship_to_org_id ,
2821 jai_constants.party_type_customer,
2822 v_creation_date ,
2823 v_created_by ,
2824 v_last_update_date ,
2825 v_last_update_login ,
2826 v_last_updated_by
2827 );
2828
2829 END IF;
2830 -- End of Bug#4215808
2831
2832 ELSE
2833 UPDATE JAI_OM_WSH_LINES_ALL
2834 SET quantity = v_shipped_quantity,
2835 tax_amount = v_tot_tax_amount,
2836 order_line_id = v_source_line_id,
2837 excise_amount = v_excise_amount,
2838 basic_excise_duty_amount = v_basic_excise_duty_amount,
2839 add_excise_duty_amount = v_add_excise_duty_amount,
2840 oth_excise_duty_amount = v_oth_excise_duty_amount,
2841 last_update_date = v_last_update_date,
2842 last_updated_by = v_last_updated_by,
2843 last_update_login = v_last_update_login,
2844 -- Added by Brathod for Bug#4215808
2845 VAT_EXEMPTION_FLAG = lv_vat_exemption_flag,
2846 VAT_EXEMPTION_TYPE = lv_vat_exemption_type,
2847 VAT_EXEMPTION_DATE = ld_vat_exemption_date,
2848 VAT_EXEMPTION_REFNO = lv_vat_exemption_refno
2849 -- End of Bug#4215808
2850 WHERE Delivery_id = v_delivery_id
2851 AND Delivery_Detail_id = v_delivery_detail_id;
2852 END IF; --5
2853
2854
2855 /*
2856 || Start of bug #5631784
2857 || Code added by bgowrava for the forward porting bug
2858 */
2859 /* jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
2860 pv_log_msg => ' Before call to jai_ar_tcs_rep_pkg.wsh_interim_accounting '
2861 ); */ --commented by bgowrava for bug#5631784
2862
2863 /*
2864 ||Does interim TCS accounting for the TCS type of taxes
2865 */
2866 jai_ar_tcs_rep_pkg.wsh_interim_accounting ( p_delivery_id => v_delivery_id ,
2867 p_delivery_detail_id => v_delivery_detail_id ,
2868 p_order_header_id => v_source_header_id ,
2869 p_organization_id => v_organization_id ,
2870 p_location_id => v_location_id ,
2871 p_currency_code => v_currency_code ,
2872 p_process_flag => lv_process_flag ,
2873 p_process_message => lv_process_message
2874 );
2875 /* jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
2876 pv_log_msg => ' Returned from jai_ar_tcs_rep_pkg.wsh_interim_accounting '
2877 ); */ --commented by bgowrava for bug#5631784
2878
2879 IF lv_process_flag = jai_constants.expected_error OR ---------A2
2880 lv_process_flag = jai_constants.unexpected_error
2881 THEN
2882 /*
2883 || As Returned status is an error/not applicable hence:-
2884 || Set out variables p_process_flag and p_process_message accordingly
2885 */
2886 /* jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
2887 pv_log_msg => ' Error in processing jai_ar_tcs_rep_pkg.wsh_interim_accounting ' ||CHR(10)
2888 ||',lv_process_flag -> '||lv_process_flag ||CHR(10)
2889 ||',lv_process_message -> '||lv_process_message
2890 ); */ --commented by bgowrava for bug#5631784
2891 fnd_message.set_name ( application => 'JA',
2892 name => 'JAI_ERR_DESC'
2893 );
2894
2895 fnd_message.set_token ( token => 'JAI_ERROR_DESCRIPTION',
2896 value => lv_process_message
2897 );
2898
2899 app_exception.raise_exception;
2900
2901 END IF; ---------A2
2902
2903
2904 /*
2905 || End of bug #5631784
2906 */
2907
2908
2909 --------------------------------------------------------------------------------------------------------
2910 -- start
2911 OPEN bonded_cur(v_organization_id, v_subinventory);
2912 FETCH bonded_cur INTO v_bonded_flag;
2913 CLOSE bonded_cur;
2914 IF v_debug_flag ='Y' THEN
2915 UTL_FILE.PUT_LINE(v_myfilehandle,'2 BEFORE the assignment OF v_order_type_id');
2916 END IF;
2917 v_order_type_id := v_source_header_type_id;
2918 IF v_debug_flag ='Y' THEN
2919 UTL_FILE.PUT_LINE(v_myfilehandle,'3 v_order_type_id IS '|| v_order_type_id);
2920 END IF;
2921
2922 -- added by sriram - bug # 3021588
2923
2924 jai_cmn_bond_register_pkg.GET_REGISTER_ID(v_organization_id,
2925 v_location_id,
2926 v_order_type_id,
2927 'Y',
2928 v_asst_register_id,
2929 v_register_code
2930 );
2931
2932
2933 -- following cursor has been commented and instead call to the jai_cmn_bond_register_pkg package has been done
2934
2935 /*OPEN register_code_cur(v_organization_id, v_location_id,v_order_type_id);
2936 FETCH register_code_cur INTO v_register_code;
2937 CLOSE register_code_cur;
2938 */
2939
2940
2941 IF v_debug_flag ='Y' THEN
2942 UTL_FILE.PUT_LINE(v_myfilehandle,'4 AFTER the Register Code CURSOR');
2943 END IF;
2944 OPEN fin_year_cur(v_organization_id);
2945 FETCH fin_year_cur INTO v_fin_year;
2946 CLOSE fin_year_cur;
2947 IF v_debug_flag ='Y' THEN
2948 UTL_FILE.PUT_LINE(v_myfilehandle,'5 AFTER the Financial Year CURSOR');
2949 END IF;
2950 IF v_delivery_id <> -1 THEN
2951 v_old_register := NULL;
2952 v_old_excise_invoice_no := NULL;
2953 IF v_old_register IS NULL THEN
2954 v_reg_type := NULL;
2955 v_rg_type := NULL;
2956 v_exc_invoice_no := NULL;
2957 v_tot_basic_ed_amt := v_basic_excise_duty_amount ;
2958 v_tot_addl_ed_amt := v_add_excise_duty_amount;
2959 v_tot_oth_ed_amt := v_oth_excise_duty_amount;
2960 v_tot_excise_amt := v_excise_amount;
2961 IF NVL(v_bonded_flag,'Y') = 'Y'
2962 AND ( NVL(v_tot_excise_amt,0) > 0 OR v_excise_exempt_type IS NOT NULL ) THEN
2963 IF v_debug_flag ='Y' THEN
2964 UTL_FILE.PUT_LINE(v_myfilehandle,'6 BEFORE the Preference cursors');
2965 END IF;
2966 --Changed by Nagaraj.s for Enh#2415656
2967 OPEN pref_cur(v_organization_id, v_location_id);
2968 FETCH pref_cur INTO v_pref_rg23a, v_pref_rg23c, v_pref_pla,v_export_oriented_unit;
2969 CLOSE pref_cur;
2970 ----Changed by Nagaraj.s for Enh#2415656
2971 IF v_debug_flag ='Y' THEN
2972 UTL_FILE.PUT_LINE(v_myfilehandle,'7 BEFORE the RG Balance CURSOR');
2973 END IF;
2974 OPEN rg_bal_cur(v_organization_id, v_location_id);
2975 FETCH rg_bal_cur INTO v_rg23a_balance, v_rg23c_balance, v_pla_balance,
2976 v_basic_pla_balance,v_additional_pla_balance,v_other_pla_balance;
2977 CLOSE rg_bal_cur;
2978 IF v_debug_flag ='Y' THEN
2979 UTL_FILE.PUT_LINE(v_myfilehandle,'8 BEFORE the SSI Unit Flag CURSOR');
2980 END IF;
2981 OPEN ssi_unit_flag_cur(v_organization_id, v_location_id);
2982 FETCH ssi_unit_flag_cur INTO v_ssi_unit_flag;
2983 CLOSE ssi_unit_flag_cur;
2984 IF v_debug_flag ='Y' THEN
2985 UTL_FILE.PUT_LINE(v_myfilehandle,'9 BEFORE the Register Code CURSOR');
2986 END IF;
2987
2988
2989 -- added by sriram - bug # 3021588
2990
2991 jai_cmn_bond_register_pkg.GET_REGISTER_ID(v_organization_id,
2992 v_location_id,
2993 v_order_type_id,
2994 'Y',
2995 v_asst_register_id,
2996 v_register_code
2997 );
2998
2999 -- call to the jai_cmn_bond_register_pkg package has been included instead of using the cursors
3000 -- to get the bond register balance info and other bond register details
3001
3002 /*OPEN register_code_cur(v_organization_id, v_location_id, v_order_type_id);
3003 FETCH register_code_cur INTO v_register_code;
3004 CLOSE register_code_cur;
3005 */
3006
3007
3008 IF NVL(v_register_code,'N') IN ('DOMESTIC_EXCISE','EXPORT_EXCISE') THEN
3009 OPEN get_item_dtls(v_organization_id,v_inventory_item_id);
3010 FETCH get_item_dtls INTO v_excise_flag,v_item_class;
3011 CLOSE get_item_dtls;
3012 IF NVL(v_excise_flag,'N') = 'Y' THEN
3013 IF NVL(v_excise_exempt_type, '@@@') NOT IN ('CT2', 'EXCISE_EXEMPT_CERT',
3014 'CT2_OTH', 'EXCISE_EXEMPT_CERT_OTH','CT3' ) THEN
3015 --***************************************************************************************************
3016 --Calling the Function by Nagaraj.s for Enh#2415656............................
3017 IF v_debug_flag ='Y' THEN
3018 UTL_FILE.PUT_LINE(v_myfilehandle,'10 BEFORE the jai_om_wsh_processing_pkg.excise_balance_check FUNCTION');
3019 UTL_FILE.FCLOSE(v_myfilehandle);
3020 END IF;
3021
3022 open c_cess_amount(v_delivery_id);
3023 fetch c_cess_amount into ln_cess_amount;
3024 close c_cess_amount;
3025
3026 -- start Bgowrava for forward porting bug#5989740
3027 open c_sh_cess_amount(v_delivery_id);
3028 fetch c_sh_cess_amount into ln_sh_cess_amount;
3029 close c_sh_cess_amount;
3030
3031 -- end Bgowrava for forward porting bug#5989740
3032
3033
3034 v_reg_type:= jai_om_wsh_processing_pkg.excise_balance_check
3035 (v_pref_rg23a,
3036 v_pref_rg23c,
3037 v_pref_pla,
3038 NVL(v_ssi_unit_flag,'N'),
3039 v_tot_excise_amt,
3040 v_rg23a_balance,
3041 v_rg23c_balance,
3042 v_pla_balance,
3043 v_basic_pla_balance,
3044 v_additional_pla_balance,
3045 v_other_pla_balance,
3046 v_basic_excise_duty_amount,
3047 v_add_excise_duty_amount ,
3048 v_oth_excise_duty_amount,
3049 v_export_oriented_unit,
3050 v_register_code,
3051 v_delivery_id ,
3052 v_organization_id,
3053 v_location_id ,
3054 ln_cess_amount ,
3055 ln_sh_cess_amount , --Bgowrava for forward porting bug#5989740
3056 lv_process_flag ,
3057 lv_process_message
3058 );
3059 --Ends here......................................
3060 IF v_debug_flag ='Y' THEN
3061 v_myfilehandle := UTL_FILE.FOPEN(v_utl_location,'ja_in_wsh_dlry_dtls_au_trg.LOG','A');
3062 UTL_FILE.PUT_LINE(v_myfilehandle,'11 AFTER the jai_om_wsh_processing_pkg.excise_balance_check FUNCTION v_reg_type -> ' || v_reg_type);
3063 END IF;
3064 --***************************************************************************************************
3065 ELSE
3066 OPEN get_item_dtls(v_organization_id,v_inventory_item_id);
3067 FETCH get_item_dtls INTO v_excise_flag,v_item_class;
3068 CLOSE get_item_dtls;
3069 IF v_item_class NOT IN ('OTIN', 'OTEX') THEN
3070 IF v_excise_exempt_type IN ('CT2_OTH', 'EXCISE_EXEMPT_CERT_OTH' ) THEN
3071 OPEN Get_Tax_Lines_Details_Cur1;
3072 FETCH Get_Tax_Lines_Details_Cur1 INTO v_modvat_tax_rate,v_rounding_factor;
3073 CLOSE Get_Tax_Lines_Details_Cur1;
3074 ELSE
3075 OPEN for_modvat_percentage(v_organization_id, v_location_id);
3076 FETCH for_modvat_percentage INTO v_modvat_tax_rate;
3077 CLOSE for_modvat_percentage;
3078 END IF;
3079 v_exempt_bal := (NVL(v_exempt_bal, 0) + v_shipped_quantity * v_assessable_value * NVL(v_modvat_tax_rate,0))/100;
3080 --*********************************************************************************************************
3081 IF v_debug_flag ='Y' THEN
3082 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);
3083 END IF;
3084 --Calling the Function by Nagaraj.s for Enh#2415656............................
3085
3086 /*
3087 Following cursor added by ssumaith - bug#4171272
3088 */
3089
3090 open c_cess_amount(v_delivery_id);
3091 fetch c_cess_amount into ln_cess_amount;
3092 close c_cess_amount;
3093
3094
3095 -- start Bgowrava for forward porting bug#5989740
3096
3097 open c_sh_cess_amount(v_delivery_id);
3098 fetch c_sh_cess_amount into ln_sh_cess_amount;
3099 close c_sh_cess_amount;
3100 -- end Bgowrava for forward porting bug#5989740
3101
3102
3103 v_reg_type := jai_om_wsh_pkg.get_excise_register_with_bal
3104 (v_pref_rg23a,
3105 v_pref_rg23c,
3106 v_pref_pla,
3107 NVL(v_ssi_unit_flag,'N'),
3108 v_exempt_bal,
3109 v_rg23a_balance,
3110 v_rg23c_balance,
3111 v_pla_balance,
3112 v_basic_pla_balance,
3113 v_additional_pla_balance,
3114 v_other_pla_balance,
3115 v_basic_excise_duty_amount,
3116 v_add_excise_duty_amount ,
3117 v_oth_excise_duty_amount,
3118 v_export_oriented_unit,
3119 v_register_code,
3120 v_delivery_id,
3121 v_organization_id,
3122 v_location_id ,
3123 ln_cess_amount ,
3124 ln_sh_cess_amount , --Bgowrava for forward porting bug#5989740
3125 lv_process_flag ,
3126 lv_process_message
3127 );
3128 --Ends here......................................
3129 IF v_debug_flag ='Y' THEN
3130 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);
3131 END IF;
3132 --*********************************************************************************************************
3133 v_basic_ed_amt := v_exempt_bal;
3134 v_tot_basic_ed_amt := NVL(v_tot_basic_ed_amt,0) + v_exempt_bal;
3135 v_remarks := 'Against Modvat Recovery'||'-'||v_excise_exempt_refno;
3136 IF v_debug_flag ='Y' THEN
3137 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);
3138 UTL_FILE.FCLOSE(v_myfilehandle);
3139 END IF;
3140 END IF;
3141 END IF;
3142 END IF;
3143 ELSIF NVL(v_register_code,'N') IN ('BOND_REG') THEN
3144 v_bond_tax_amount := NVL(v_tot_excise_amt,0) + NVL(v_bond_tax_amount,0);
3145
3146 -- commenting the following cursor definition as balance already fetched using the call to bond register package
3147
3148 /*OPEN register_balance_cur(v_organization_id, v_location_id);
3149 FETCH register_balance_cur INTO v_register_balance;
3150 CLOSE register_balance_cur;
3151 */
3152
3153 jai_cmn_bond_register_pkg.GET_REGISTER_DETAILS(v_asst_register_id,
3154 v_register_balance,
3155 v_reg_exp_date ,
3156 v_lou_flag);
3157
3158
3159 -- added logic to check if the register validity is ok
3160 IF nvl(v_reg_exp_date,sysdate) < sysdate then
3161 /* RAISE_APPLICATION_ERROR(-20122, 'Bonded Register Validity has Expired on ' || v_reg_exp_date);
3162 */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'Bonded Register Validity has Expired on ' || v_reg_exp_date ; return ;
3163 end if;
3164 -- added the letter of undertaking comparison in the following if.
3165 -- logic is : to check if it is a letter of undertaking and only then if balance is a problem , raise an error
3166 v_tot_excise_amt := v_tot_excise_amt * v_curr_conv_rate; /* ssumaith - bug#6487667*/
3167 IF NVL(v_register_balance,0) < NVL(v_tot_excise_amt,0) and nvl(v_lou_flag,'N') = 'N' THEN
3168 /* RAISE_APPLICATION_ERROR(-20120, 'Bonded Register Has Balance -> '
3169 || TO_CHAR(v_register_balance) || ' ,which IS less than Excisable Amount -> '
3170 || 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 -> '
3171 || TO_CHAR(v_tot_excise_amt) ; return ;
3172 END IF;
3173 END IF;
3174 END IF;
3175 END IF;
3176 END IF;
3177 -- end
3178 ---------------------------------------------------------------------------------------
3179 Exception
3180 When Others then
3181 /* raise_application_error (-20001,substr(sqlerrm,1,200)); */ pv_return_code := jai_constants.expected_error ; pv_return_message := substr(sqlerrm,1,200) ; return ;
3182
3183 END ARU_T3 ;
3184
3185 END JAI_OM_WDD_TRIGGER_PKG ;
[Home] [Help]