1 PACKAGE BODY jai_om_wsh_pkg AS
2 /* $Header: jai_om_wsh.plb 120.19.12020000.10 2013/04/07 08:20:39 mmurtuza ship $ */
3
4
5 PROCEDURE process_delivery
6 (
7 errbuf OUT NOCOPY VARCHAR2 ,
8 retcode OUT NOCOPY VARCHAR2 ,
9 p_delivery_id IN NUMBER
10 )
11 IS
12 /*
13 || This procedure is used to Create the Excise Invoice No for each delivery_id,
14 || Post the entries into the excise register tables, bond regsiter table
15 */
16 lv_debug CHAR(1); -- := 'Y' ; --Ramananda for File.Sql.35
17 lv_block_no CHAR(3) ;
18 lv_statement_no CHAR(3) ;
19 lv_error_mesg VARCHAR2(255) ;
20 lv_procedure_name VARCHAR2(25); -- := 'process_delivery'; --Ramananda for File.Sql.35
21
22 --Anuradha Parthasarathy
23 v_initial_pickup_date DATE ;
24 v_actual_shipment_date DATE ;
25 v_date DATE ;
26
27 --Anuradha Parthasarathy
28 v_inventory_item_id NUMBER ;
29 v_order_line_id NUMBER ;
30 v_location_id NUMBER ;
31 v_register JAI_OM_WSH_LINES_ALL.REGISTER%TYPE ;
32 v_uom_code JAI_OM_WSH_LINES_ALL.UNIT_CODE%TYPE ;
33 v_shp_qty NUMBER ;
34 v_item_class JAI_INV_ITM_SETUPS.ITEM_CLASS%TYPE ;
35 v_excise_flag VARCHAR2(1) ;
36 v_basic_ed_amt NUMBER ;
37 v_addl_ed_amt NUMBER ;
38 v_oth_ed_amt NUMBER ;
39 v_excise_exempt_type JAI_OM_WSH_LINES_ALL.EXCISE_EXEMPT_TYPE%TYPE ;
40 v_selling_price NUMBER ;
41 v_set_of_books_id NUMBER ;
42 v_currency_code OE_ORDER_HEADERS_ALL.TRANSACTIONAL_CURR_CODE%TYPE ;
43 v_conv_type_code OE_ORDER_HEADERS_ALL.CONVERSION_TYPE_CODE%TYPE ;
44 v_conv_rate NUMBER ;
45 v_conv_date DATE ;
46 v_customer_id NUMBER ;
47 v_ship_to_org_id NUMBER ;
48 v_receipt_id NUMBER ;
49 v_creation_date DATE; -- := SYSDATE; --Ramananda for File.Sql.35
50 v_created_by NUMBER ;
51 v_last_update_date DATE ;
52 v_last_updated_by NUMBER ;
53 v_last_update_login NUMBER ;
54 v_order_type_id NUMBER ;
55 v_subinventory WSH_DELIVERY_DETAILS.SUBINVENTORY%TYPE ;
56 v_bonded_flag VARCHAR2(1) ;
57 v_trading_flag VARCHAR2(1) ;
58 v_pref_rg23a NUMBER ;
59 v_pref_rg23c NUMBER ;
60 v_pref_pla NUMBER ;
61 v_ssi_unit_flag VARCHAR2(1) ;
62 v_register_code VARCHAR2(30) ;
63 v_Trad_register_code VARCHAR2(30) ;
64 v_reg_type VARCHAR2(10) ;
65 v_modvat_tax_rate NUMBER ;
66 v_rounding_factor NUMBER ;
67 v_assessable_value NUMBER ;
68 v_exempt_bal NUMBER ;
69 v_bond_tax_amount NUMBER ;
70 v_register_balance NUMBER ;
71 v_order_number NUMBER ;
72 v_meaning VARCHAR2(80) ;
73 v_fin_year NUMBER ;
74 v_transaction_type_code OE_TRANSACTION_TYPES_ALL.TRANSACTION_TYPE_CODE%TYPE ;
75 v_start_number NUMBER ;
76 v_end_number NUMBER ;
77 v_jump_by NUMBER ;
78 v_prefix VARCHAR2(50) ;
79 V_EXC_INVOICE_NO JAI_OM_WSH_LINES_ALL.EXCISE_INVOICE_NO%TYPE ;
80 v_excise_check NUMBER := 0 ; --added by Vijay on 2002/02/07
81 v_gp_1 NUMBER ;
82 v_gp_2 NUMBER ;
83 v_tax_rate NUMBER ;
84 v_qty_reg_type VARCHAR2(1) ;
85 v_part_i_register_id NUMBER ;
86 v_source_name VARCHAR2(100); -- := 'Register India' ; --Ramananda for File.Sql.35
87 v_category_name VARCHAR2(100); -- := 'Register India' ; --Ramananda for File.Sql.35
88 v_remarks VARCHAR2(60) ;
89 v_rg23_part_i_no NUMBER ;
90 v_rg23_part_ii_no NUMBER ;
91 v_pla_register_no NUMBER ;
92 v_converted_rate NUMBER ;
93 v_organization_id NUMBER ;
94 v_rg23a_balance NUMBER ;
95 v_rg23c_balance NUMBER ;
96 v_excise_amount NUMBER ;
97 v_pla_balance NUMBER ;
98 v_raise_error_flag VARCHAR2(1) ;
99 v_raise_exempt_flag VARCHAR2(1) ;
100 v_rg_type VARCHAR2(1) ;
101 v_line_id NUMBER ;
102 v_header_no NUMBER ;
103 v_quantity_applied NUMBER ;
104 V_item_trading_flag VARCHAR2(1) ;
105 v_register_id NUMBER ;
106 V_QTY_TO_ADJUST NUMBER := 0 ;
107 v_invoice_to_site_use_id NUMBER ;
108 V_SHIP_TO_SITE_USE_ID NUMBER ;
109 V_EXCISE_DUTY_RATE NUMBER ;
110 V_RATE_PER_UNIT NUMBER ;
111 V_DUTY_AMOUNT NUMBER ;
112
113 -- Start, cbabu for Bug# 2736191
114 v_proportionate_rpu NUMBER; -- should contain proportionate Rate Per Unit if detail is matched to multiple receipts
115 v_proportionate_edr NUMBER; -- should contain proportionate excise duty rate if detail is matched to multiple receipts
116 v_total_quantity_applied NUMBER; -- should contain the total quantity applied on multiple receipts for the same detail
117 v_total_base_duty_amount NUMBER; -- should contain the total base duty amount on which the rate per unit is calculated for this detail
118 v_total_rate NUMBER; -- should contain accumilated value of rate_per_unit * quantity_applied for all the receipts in which the detail is applied
119 -- End, cbabu for Bug# 2736191
120 /*Added by nprashar for bug # 5735284 added for bug#6199766 ,start*/
121 v_qnty_received NUMBER;
122 v_tot_duty_amt NUMBER;
123 v_tot_cvd_amt NUMBER;
124 v_tot_addl_cvd_amt NUMBER;
125 /*added for bug#6199766 ,end*/
126 v_source_header_id NUMBER ;
127 v_source_line_id NUMBER ;
128 v_excise_exempt_refno VARCHAR2(30) ;
129 v_old_delivery_id NUMBER ;
130 v_tot_excise_amt NUMBER ;
131 v_tot_basic_ed_amt NUMBER ;
132 v_tot_addl_ed_amt NUMBER ;
133 v_tot_oth_ed_amt NUMBER ;
134 v_old_register JAI_OM_WSH_LINES_ALL.REGISTER%TYPE ;
135 v_old_excise_invoice_no VARCHAR2(200) ;
136 v_status_code WSH_NEW_DELIVERIES.STATUS_CODE%TYPE ;
137 v_org_id NUMBER ; --2001/04/01 Vijay
138 v_source_line_id_pick NUMBER ;
139 v_no_records_fetched NUMBER := 0 ; --28/05/02 cbabu for debug
140 v_trans_type_up VARCHAR2(3) ;
141 v_order_invoice_type_up VARCHAR2(25) ;---ashish 10june
142 v_register_code_up VARCHAR2(25) ;---ashish 10june
143
144 --New Variables Declared by Nagaraj.s for Enh2415656
145 v_output NUMBER ;-- By Nagaraj.s to get the output of the function ja_in_exc_balance_amt
146 v_export_oriented_unit JAI_CMN_INVENTORY_ORGS.EXPORT_ORIENTED_UNIT%TYPE ;
147 v_basic_pla_balance NUMBER ;
148 v_additional_pla_balance NUMBER ;
149 v_other_pla_balance NUMBER ;
150 v_myfilehandle UTL_FILE.FILE_TYPE ; -- This is for File handling
151 v_utl_location VARCHAR2(512) ;
152 v_ret_stat BOOLEAN ; -- 2663211 -- for raising error
153 --Ends here for Enh2415656
154
155 v_asst_register_id NUMBER ; -- bug # 3021588
156 v_register_exp_date JAI_OM_OE_BOND_REG_HDRS.BOND_EXPIRY_DATE%TYPE ; -- bug # 3021588
157 v_lou_flag JAI_OM_OE_BOND_REG_HDRS.LOU_FLAG%TYPE ; -- bug # 3021588
158
159 -- Cursor to fetch required values for corresponding Delivery_detail_id in
160 CURSOR Get_delivery_detail_cur(p_delivery_detail_id NUMBER) IS
161 SELECT A.order_line_id, A.organization_id, A.location_id, A.register,
162 A.inventory_item_id, A.unit_code uom_code, A.quantity,
163 b.item_class, b.excise_flag ,A.basic_excise_duty_amount,
164 A.add_excise_duty_amount, A.oth_excise_duty_amount, A.excise_amount,
165 A.excise_exempt_type, A.selling_price, A.customer_id, A.ship_to_org_id,
166 A.order_type_id, A.subinventory, A.assessable_value,
167 A.EXCISE_EXEMPT_REFNO, A.org_id -- added a.org_id by vijay for multi org support
168 FROM JAI_OM_WSH_LINES_ALL A,
169 JAI_INV_ITM_SETUPS B
170 WHERE A.delivery_detail_id = p_delivery_detail_id
171 AND A.organization_id = b.organization_id
172 AND A.inventory_item_id = b.inventory_item_id
173 ORDER BY b.item_class;
174
175 /* Bug 5243532. Added by Lakshmi Gopalsami
176 * Removed the cursor set_of_books_cur and implemented using caching logic.
177 */
178
179 /*
180 || Code changed by aiyer for the bug #3090371.
181 || Modified the cursor to get the actual_shipment_date from oe_order_lines_all instead of the
182 || conversion_date from oe_order_headers_all.
183 || As a Sales order shipment date can be different from its creation date, hence the conversion rate
184 || applicable on the date of shipment should be considerd for all processing rather than the creation
185 || date of the Sales order.
186 */
187
188 CURSOR get_conv_detail_cur(
189 cp_order_header_id OE_ORDER_HEADERS_ALL.HEADER_ID%TYPE ,
190 cp_line_id OE_ORDER_LINES_ALL.LINE_ID%TYPE
191 ) IS
192 SELECT
193 order_number ,
194 transactional_curr_code ,
195 conversion_type_code ,
196 conversion_rate ,
197 b.actual_shipment_date
198 FROM
199 oe_order_headers_all a ,
200 oe_order_lines_all b
201 WHERE
202 a.header_id = b.header_id AND
203 b.line_id = cp_line_id AND
204 a.header_id = cp_order_header_id ;
205
206 CURSOR bonded_cur(p_organization_id NUMBER, p_subinventory VARCHAR2) IS
207 SELECT NVL(A.bonded,'Y') bonded,NVL(A.trading,'Y') trading
208 FROM JAI_INV_SUBINV_DTLS A
209 WHERE A.sub_inventory_name = p_subinventory
210 AND A.organization_id = p_organization_id;
211
212 CURSOR pref_cur(p_organization_id NUMBER, p_location_id NUMBER)IS
213 --This is included in the select by Nagaraj.s for Enh2415656
214 SELECT pref_rg23a, pref_rg23c, pref_pla,
215 NVL(Export_oriented_unit ,'N')
216 FROM JAI_CMN_INVENTORY_ORGS
217 WHERE organization_id = p_organization_id
218 AND location_id = p_location_id ;
219
220 --This is included in the select by Nagaraj.s for Enh2415656
221 CURSOR rg_bal_cur(p_organization_id NUMBER, p_location_id NUMBER)IS
222 SELECT NVL(rg23a_balance,0) rg23a_balance ,NVL(rg23c_balance,0) rg23c_balance,NVL(pla_balance,0) pla_balance,
223 NVL(basic_pla_balance,0) basic_pla_balance,
224 NVL(additional_pla_balance,0) additional_pla_balance,
225 NVL(other_pla_balance,0) other_pla_balance
226 FROM JAI_CMN_RG_BALANCES
227 WHERE organization_id = p_organization_id
228 AND location_id = p_location_id ;
229
230 CURSOR ssi_unit_flag_cur(p_organization_id IN NUMBER, p_location_id IN NUMBER) IS
231 SELECT ssi_unit_flag
232 FROM JAI_CMN_INVENTORY_ORGS
233 WHERE organization_id = p_organization_id AND
234 location_id = p_location_id;
235
236 CURSOR register_code_cur(p_organization_id NUMBER, p_location_id NUMBER, p_delivery_detail_id NUMBER, p_order_type_id NUMBER) IS
237 SELECT A.register_code
238 FROM JAI_OM_OE_BOND_REG_HDRS A, JAI_OM_OE_BOND_REG_DTLS b
239 WHERE A.organization_id = p_organization_id
240 AND A.location_id = p_location_id
241 AND A.register_id = b.register_id
242 AND b.order_flag = 'Y'
243 AND b.order_type_id = p_order_type_id ;
244
245 CURSOR for_modvat_tax_rate(p_delivery_detail_id NUMBER) IS
246 SELECT A.tax_rate, b.rounding_factor
247 FROM JAI_OM_WSH_LINE_TAXES A, JAI_CMN_TAXES_ALL b
248 WHERE A.tax_id = b.tax_id
249 AND A.delivery_detail_id = p_delivery_detail_id
250 AND b.tax_type = jai_constants.tax_type_modvat_recovery ; /* --'Modvat Recovery'; Ramananda for removal of SQL LITERALs */
251
252 CURSOR for_modvat_percentage(p_organization_id NUMBER, p_location_id NUMBER) IS
253 SELECT MODVAT_REVERSE_PERCENT
254 FROM JAI_CMN_INVENTORY_ORGS
255 WHERE organization_id = p_organization_id
256 AND (location_id = p_location_id
257 OR
258 (location_id is NULL AND p_location_id is NULL)); /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
259 --AND NVL(location_id,0) = NVL(p_location_id,0);
260
261 CURSOR register_balance_cur(p_organization_id IN NUMBER, p_location_id IN NUMBER) IS
262 SELECT register_balance
263 FROM JAI_OM_OE_BOND_TRXS
264 WHERE transaction_id = (SELECT MAX(A.transaction_id)
265 FROM JAI_OM_OE_BOND_TRXS A, JAI_OM_OE_BOND_REG_HDRS B
266 WHERE A.register_id = B.register_id
267 AND B.organization_id = p_organization_id
268 AND B.location_id = p_location_id);
269
270 CURSOR Register_Code_Meaning_Cur(p_register_code IN VARCHAR2, lv_lookup_type ja_lookups.lookup_type%type) IS
271 SELECT meaning
272 FROM ja_lookups
273 WHERE lookup_code = p_register_code
274 AND lookup_type = lv_lookup_type ; /*'JAI_REGISTER_TYPE'; Ramananda for removal of SQL LITERALs */
275
276 CURSOR fin_year_cur(p_organization_id IN NUMBER) IS
277 SELECT MAX(A.fin_year)
278 FROM JAI_CMN_FIN_YEARS A
279 WHERE organization_id = p_organization_id
280 AND fin_active_flag = 'Y';
281
282 CURSOR Get_transaction_type(p_order_type_id NUMBER) IS
283 SELECT name
284 FROM oe_transaction_types_tl
285 WHERE transaction_type_id = p_order_type_id;
286
287 CURSOR Def_Excise_Invoice_Cur(p_organization_id IN NUMBER, p_location_id IN NUMBER, p_fin_year IN NUMBER,
288 p_batch_name IN VARCHAR2, p_register_code IN VARCHAR2) IS
289 SELECT start_number, end_number, jump_by, prefix
290 FROM JAI_CMN_RG_EXC_INV_NOS
291 WHERE organization_id = p_organization_id
292 AND location_id = p_location_id
293 AND fin_year = p_fin_year
294 AND order_invoice_type = p_batch_name
295 AND register_code = p_register_code; /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
296 --AND NVL(order_invoice_type,'###') = p_batch_name
297 --AND NVL(register_code,'###') = NVL(p_register_code,'***');
298
299 CURSOR excise_invoice_cur(p_organization_id IN NUMBER, p_location_id IN NUMBER, p_fin_year IN NUMBER) IS
300 SELECT NVL(MAX(GP1),0),NVL(MAX(GP2),0)
301 FROM JAI_CMN_RG_EXC_INV_NOS
302 WHERE organization_id = p_organization_id
303 AND location_id = p_location_id
304 AND fin_year = p_fin_year
305 AND order_invoice_type IS NULL
306 AND register_code IS NULL;
307
308 CURSOR ec_code_cur(p_organization_id IN NUMBER, p_location_id IN NUMBER) IS
309 SELECT A.Organization_Id, A.Location_Id
310 FROM JAI_CMN_INVENTORY_ORGS A
311 WHERE A.Ec_Code IN (SELECT B.Ec_Code
312 FROM JAI_CMN_INVENTORY_ORGS B
313 WHERE B.Organization_Id = p_organization_id
314 AND B.Location_Id = p_location_id);
315
316 --Anuradha Parthasarathy 2001/05/26
317 CURSOR Tr_ec_code_cur(p_organization_id IN NUMBER, p_location_id IN NUMBER) IS
318 SELECT A.Organization_Id, A.Location_Id
319 FROM JAI_CMN_INVENTORY_ORGS A
320 WHERE A.Tr_Ec_Code IN (SELECT B.Tr_Ec_Code
321 FROM JAI_CMN_INVENTORY_ORGS B
322 WHERE B.Organization_Id = p_organization_id
323 AND B.Location_Id = p_location_id);
324
325 -- Related Cursors added by Arun for Trading on 28 oct 2000 at 3:30
326 CURSOR Trading_register_code_cur(p_organization_id NUMBER, p_location_id NUMBER,
327 p_delivery_detail_id NUMBER, p_order_type_id NUMBER) IS
328 SELECT A.register_code
329 FROM JAI_OM_OE_BOND_REG_HDRS A, JAI_OM_OE_BOND_REG_DTLS b
330 WHERE A.organization_id = p_organization_id
331 AND A.location_id = p_location_id
332 AND A.register_id = b.register_id
333 AND b.order_flag = 'Y'
334 AND b.order_type_id = p_order_type_id
335 AND A.REGISTER_CODE LIKE '23D%';
336
337 CURSOR matched_receipt_cur1(p_reference_line_id IN NUMBER) IS
338 SELECT SUM(A.quantity_applied) quantity_applied, A.subinventory
339 FROM JAI_CMN_MATCH_RECEIPTS A
340 WHERE A.ref_line_id = p_reference_line_id
341 AND ORDER_INVOICE = 'O'; -- cbabu for Bug# 2736191
342 -- GROUP BY A.subinventory; -- cbabu for Bug# 2736191
343
344 CURSOR shipped_qty_cur (p_picking_line_id IN NUMBER) IS
345 SELECT SUM(quantity) shipped_quantity
346 FROM JAI_OM_WSH_LINES_ALL
347 WHERE delivery_detail_id = p_picking_line_id;
348
349 CURSOR Header_id (p_picking_line_id IN NUMBER) IS
350 SELECT source_line_id, source_header_id
351 FROM wsh_delivery_details
352 WHERE delivery_detail_id = p_picking_line_id;
353
354 CURSOR matched_receipt_cur(p_reference_line_id IN NUMBER) IS
355 SELECT A.receipt_id, A.quantity_applied
356 FROM JAI_CMN_MATCH_RECEIPTS A
357 WHERE A.ref_line_id = p_reference_line_id
358 AND ORDER_INVOICE = 'O' -- cbabu for Bug# 2736191
359 AND A.quantity_applied > 0;
360
361 CURSOR ship_bill_cur (p_order_header_id NUMBER) IS
362 SELECT INVOICE_TO_ORG_ID,SHIP_TO_ORG_ID
363 FROM oe_order_headers_all
364 WHERE header_id = p_order_header_id;
365
366 CURSOR qty_to_adjust_cur (p_receipt_id NUMBER) IS
367 SELECT qty_to_adjust,excise_duty_rate,rate_per_unit,quantity_received --added quantity_received for bug#5735284
368 FROM JAI_CMN_RG_23D_TRXS
369 WHERE register_id = p_receipt_id;
370
371 /*added the following cursor for bug#5735284*/
372 CURSOR get_duty_amt_cur (p_receipt_id NUMBER) IS
373 SELECT duty_amount,basic_ed, additional_ed,other_ed, cvd,additional_cvd
374 FROM JAI_CMN_RG_23D_TRXS
375 WHERE register_id = p_receipt_id;
376 rec_get_duty_amt get_duty_amt_cur%rowtype;
377
378 -- Till Here
379 /*
380 || Changed by aiyer for the bug 3139718
381 || As in this procedure the values of basic_excise_duty_amount ,add_excise_duty_amount,oth_excise_duty_amount
382 || and excise_amount should always be in INR currency hence converting the same and rounding it here instead
383 || of doing it at a later point.
384 */
385
386 -- Start of bug #3448674
387 /*
388 || Code modified by Aiyer for the 3448674.
389 || changed the cursor get_total_excise_amt to do rounding at a paticualr delivery level rather
390 || than the line level.
391 || For this , we are first summing the taxes and then rounding them
392 */
393 CURSOR get_total_excise_amt(
394 p_delivery_id NUMBER ,
395 cp_conversion_rate NUMBER
396 ) IS
397 SELECT
398 round(nvl((sum(basic_excise_duty_amount * cp_conversion_rate)),0),2) , /*Removed round function from all columns by mmurtuza for bug 16534065*/
399 round(nvl((sum(add_excise_duty_amount * cp_conversion_rate)),0),2) ,
400 round(nvl((sum(oth_excise_duty_amount * cp_conversion_rate)),0),2) ,
401 round(nvl((sum(excise_amount * cp_conversion_rate)),0),2) /*Added round function by mmurtuza for bug 16343067 / 16611281*/
402 FROM
403 JAI_OM_WSH_LINES_ALL
404 WHERE
405 delivery_id = p_delivery_id
406 and excise_exempt_type is null; -- sriram - 5th nov - bug # 3207685
407 -- End of bug #3448674
408
409 -- following cursor added by sriram - bug # 3207685
410 cursor c_get_modvat_records (p_delivery_id NUMBER) IS
411 select delivery_detail_id , quantity , assessable_Value , excise_exempt_type
412 from JAI_OM_WSH_LINES_ALL
413 where delivery_id = p_delivery_id
414 and excise_exempt_type is not null;
415
416 v_mod_basic_ed_amt number:=0;
417
418 CURSOR get_prev_del_dtl(p_delivery_id NUMBER) IS
419 SELECT register, EXCISE_INVOICE_NO
420 FROM JAI_OM_WSH_LINES_ALL
421 WHERE delivery_id = p_delivery_id
422 AND register IS NOT NULL;
423
424 --Anuradha Parthasarathy
425 CURSOR get_delivery_status(p_delivery_id NUMBER) IS
426 SELECT status_code,initial_pickup_date
427 FROM Wsh_New_deliveries
428 WHERE delivery_id = p_delivery_id;
429 ----------------------------------------------
430 --Cursor added by Jagdish on 2001/09/13
431 CURSOR get_order_line_id(p_delivery_detail_id NUMBER) IS
432 SELECT SOURCE_LINE_ID FROM wsh_delivery_details
433 WHERE delivery_detail_id = p_delivery_detail_id;
434 --Anuradha Parthasarathy
435
436 CURSOR get_actual_shipment_date(p_order_line_id NUMBER) IS
437 SELECT actual_shipment_date
438 FROM Oe_Order_Lines_All
439 WHERE line_id = p_order_line_id;
440
441 -- added by sriram Bug # 2454978
442 v_line_amount NUMBER;
443 v_tax_amt NUMBER;
444
445
446 -- added by sriram -- bug # 2769440
447
448 /* Ramananda for File.Sql.35 */
449 v_ref_10 gl_interface.reference10%type; -- := 'India Localization Entry for sales order # '; -- will hold a standard text such as 'India Localization Entry for sales order'
450 v_std_text varchar2(50); -- := 'India Localization Entry for sales order # '; -- bug # 3158976
451 v_ref_23 gl_interface.reference23%type; -- := 'process_delivery'; -- holds the object name -- 'process_delivery'
452 v_ref_24 gl_interface.reference24%type; -- := 'wsh_new_deliveries'; -- holds the table name -- ' wsh_new_deliveries'
453 v_ref_25 gl_interface.reference25%type; -- := 'delivery_id'; -- holds the column name -- 'delivery_id'
454 /* Ramananda for File.Sql.35 */
455
456 v_ref_26 gl_interface.reference26%type ; -- holds the column value -- eg -- 13645
457
458 v_ord_num oe_order_headers_all.order_number%type;
459
460 CURSOR c_order_num(p_hdr_id number) is
461 SELECT order_number
462 FROM oe_order_headers_all
463 WHERE header_id = p_hdr_id;
464
465 -- additions by sriram ends here
466
467 /*
468 || Added by aiyer for the bug 3446362.
469 || Check whether rows exist in the JAI_OM_WSH_LINES_ALL tables with excise exempt type other than CT3.
470 || Even records with null values are fine.
471 */
472 CURSOR c_ct3_flag_exists
473 IS
474 SELECT
475 count(1)
476 FROM
477 JAI_OM_WSH_LINES_ALL
478 WHERE
479 delivery_id = p_delivery_id AND
480 nvl(excise_exempt_type,'$$') <> 'CT3';
481
482 ln_count NUMBER;
483
484 /* added by bgowrava for forward porting - bug# 5554420 */
485 lv_exc_inv_gen_for_dlry_flag varchar2(1);
486 ln_excise_tax_cnt number;
487 CURSOR c_excise_tax_cnt(cp_delivery_detail_id JAI_OM_WSH_LINE_TAXES.delivery_detail_id%type) IS
488 SELECT count(1)
489 FROM JAI_OM_WSH_LINE_TAXES JSPTL ,
490 JAI_CMN_TAXES_ALL JTC
491 WHERE JSPTL.TAX_ID = JTC.TAX_ID
492 AND JSPTL.delivery_detail_id = cp_delivery_detail_id
493 AND upper(jtc.tax_type) like '%EXCISE%';
494
495
496 /*
497 || Added by Ramananda
498 || Start of bug#4543424
499 */
500 CURSOR c_excise_tax_rate(cp_delivery_detail_id JAI_OM_WSH_LINE_TAXES.DELIVERY_DETAIL_ID%TYPE) IS
501 SELECT SUM(NVL(JSPTL.tax_rate,0)) , count(1) --NVL(sum(JSPTL.tax_rate),0)
502 FROM JAI_OM_WSH_LINE_TAXES JSPTL ,
503 JAI_CMN_TAXES_ALL JTC
504 WHERE JSPTL.TAX_ID = JTC.TAX_ID
505 AND JSPTL.delivery_detail_id = cp_delivery_detail_id
506 AND UPPER(JTC.TAX_TYPE) = 'EXCISE';
507
508 ln_total_tax_rate JAI_CMN_TAXES_ALL.tax_rate%TYPE;
509 ln_number_of_Taxes NUMBER;
510 /*
511 || Added by Ramananda
512 || End of bug#4543424
513 */
514
515 /*Start additions by mmurtuza for bug 15971482*/
516 CURSOR c_excise_base_tax_amt(cp_delivery_detail_id JAI_OM_WSH_LINE_TAXES.DELIVERY_DETAIL_ID%TYPE) IS
517 SELECT sum(NVL(JSPTL.base_tax_amount,0) / JOWLA.quantity)
518 FROM JAI_OM_WSH_LINE_TAXES JSPTL ,
519 JAI_CMN_TAXES_ALL JTC,
520 JAI_OM_WSH_LINES_ALL JOWLA
521 WHERE JSPTL.TAX_ID = JTC.TAX_ID
522 AND JSPTL.delivery_detail_id = cp_delivery_detail_id
523 AND JSPTL.delivery_detail_id = JOWLA.delivery_detail_id
524 AND UPPER(JTC.TAX_TYPE) = 'EXCISE';
525
526 ln_total_of_base_tax NUMBER;
527
528 /*End additions by mmurtuza for bug 15971482*/
529
530 CURSOR c_cess_amount (cp_Delivery_id JAI_OM_WSH_LINES_ALL.delivery_id%type) is
531 SELECT sum(jsptl.func_tax_amount) tax_amount
532 FROM JAI_OM_WSH_LINE_TAXES jsptl ,
533 JAI_CMN_TAXES_ALL jtc
534 WHERE jtc.tax_id = jsptl.tax_id
535 AND delivery_detail_id in
536 (SELECT delivery_detail_id
537 FROM JAI_OM_WSH_LINES_ALL
538 WHERE delivery_id = cp_delivery_id
539 )
540 AND upper(jtc.tax_type) in (jai_constants.tax_type_cvd_edu_cess,jai_constants.tax_type_exc_edu_cess); /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
541 -- AND upper(jtc.tax_type) in ('CVD_EDUCATION_CESS','EXCISE_EDUCATION_CESS');
542
543 /*Cursor added by ssawant for bug 5989740 */
544 CURSOR c_sh_cess_amount (cp_Delivery_id JAI_OM_WSH_LINES_ALL.delivery_id%type) is
545 SELECT sum(jsptl.func_tax_amount) tax_amount
546 FROM JAI_OM_WSH_LINE_TAXES jsptl ,
547 JAI_CMN_TAXES_ALL jtc
548 WHERE jtc.tax_id = jsptl.tax_id
549 AND delivery_detail_id in
550 (SELECT delivery_detail_id
551 FROM JAI_OM_WSH_LINES_ALL
552 WHERE delivery_id = cp_delivery_id
553 )
554 AND upper(jtc.tax_type) in (jai_constants.tax_type_sh_cvd_edu_cess,jai_constants.tax_type_sh_exc_edu_cess);
555
556 ln_cess_amount number;
557 ln_sh_cess_amount number; /* added by ssawant for bug 5989740 */
558 lv_process_flag varchar2(5);
559 lv_process_message varchar2(1996);
560
561
562
563 /* ends additions by ssumaith - bug# 4311993 */
564
565 /*
566 || Start of bug 4566054
567 ||Code added by aiyer for the bug 4566054
568 ||Get the total cess amount at the delivery detail level
569 ||hence calculate the cess and pass it to the procedure jai_om_rg_pkg.ja_in_rg_i_entry with source as 'WSH'
570 */
571 CURSOR cur_get_del_det_cess_amt (cp_delivery_detail_id JAI_OM_WSH_LINES_ALL.DELIVERY_DETAIL_ID%TYPE)
572 IS
573 SELECT
574 sum(func_tax_amount) cess_amount
575 FROM
576 jai_om_wsh_lines_all jspl ,
577 jai_om_wsh_line_taxes jsptl,
578 jai_cmn_taxes_all jtc
579 WHERE
580 jspl.delivery_detail_id = jsptl.delivery_detail_id AND
581 jsptl.tax_id = jtc.tax_id AND
582 upper(jtc.tax_type) IN (jai_constants.tax_type_cvd_edu_cess,jai_constants.tax_type_exc_edu_cess) AND
583 jspl.delivery_detail_id = cp_delivery_detail_id;
584
585 ln_del_det_totcess_amt JAI_CMN_RG_I_TRXS.CESS_AMT%TYPE;
586 /* End of bug 4566054 */
587
588 /*START, Bgowrava for forward porting bug#5989740*/
589 CURSOR cur_get_del_det_sh_cess_amt (cp_delivery_detail_id JAI_OM_WSH_LINES_ALL.DELIVERY_DETAIL_ID%TYPE)
590 IS
591 SELECT
592 sum(func_tax_amount) cess_amount
593 FROM
594 JAI_OM_WSH_LINES_ALL jspl,
595 JAI_OM_WSH_LINE_TAXES jsptl,
596 JAI_CMN_TAXES_ALL jtc
597 WHERE
598 jspl.delivery_detail_id = jsptl.delivery_detail_id AND
599 jsptl.tax_id = jtc.tax_id AND
600 upper(jtc.tax_type) IN (jai_constants.tax_type_sh_cvd_edu_cess,
601 jai_constants.tax_type_sh_exc_edu_cess)
602 AND
603 jspl.delivery_detail_id = cp_delivery_detail_id;
604
605 ln_del_det_totshcess_amt JAI_CMN_RG_I_TRXS.SH_CESS_AMT%TYPE;
606
607 /*END, Bgowrava for forward porting bug#5989740*/
608
609 /* Bug4562791. Added by Lakshmi Gopalsami */
610 CURSOR c_conc_request_submit_date
611 (cp_Request_id FND_CONCURRENT_REQUESTS.REQUEST_ID%TYPE) IS
612 SELECT REQUEST_DATE
613 FROM FND_CONCURRENT_REQUESTS
614 WHERE request_id = cp_Request_id;
615
616 ld_request_submit_Date FND_CONCURRENT_REQUESTS.REQUEST_DATE%TYPE;
617
618 /* Bug 5243532. Added by Lakshmi Gopalsami
619 * Define variable for implementing caching logic.
620 */
621 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
622
623 BEGIN
624
625 /*------------------------------------------------------------------------------------------
626 CHANGE HISTORY: FILENAME: process_delivery.sql
627 S.No Date Author and Details
628 ------------------------------------------------------------------------------------------
629 1 2001/04/01 Vijay Added - Org_Id for Multi-Org Support
630
631 2 2001/04/09 Manohar Mishra - Changed the cases for Register Codes
632
633 3 2001/05/25 Anuradha Parthasarathy -
634 Added arguments v_exc_invoice_no and v_exc_invoice_date to RG23d procedure instead of nulls
635
636 4 2001/05/26 Anuradha Parthasarathy
637 Cursor added to ensure incrementation of excise invoice nos for trading Organizations
638
639 5 2001/05/30 Anuradha Parthasarathy
640 Condition added to ensure that the registers are hit only when the excise amount
641 is greater than zero.
642
643 6 2001/06/15 For Bond Register
644 7 2001/07/12 Anuradha Parthasarathy
645 Excise Invoice Generation for Trading to go on same lines as Manufacturing.
646
647 8 2001/09/13 Jagdish Bhosle - For Split order Cursor added to populate correct order_line_id.
648
649 9 2001/08/07 Jagdish Bhosle
650 Initilise v_tax_rate to 0 as excise tax rate was doubling in RGi manual entry screen.
651
652 10 2001/10/01 Jagdish Bhosle - To avoid same Excise Invoice No if setup is not done.
653
654 11 2001/09/24 Vijay - Rounded amounts for RG23_Partii and PLA
655
656 12 2001/10/05 Jagdish Bhosle - Excise Generation after successful Inventory /OM interface.
657
658 13 2001/11/01 Anuradha Parthasarathy - Code added to deal with Modvat Recovery for excise exempt types.
659
660 14 2002/02/07 Vijay - Added a check to avoid duplication of Excise Invoice Number in RG23PartII
661
662 15 2002/02/20 Vijay - Check added to avoid duplication of Excise Invoice Number in PLA, RG23 PartI
663
664 16 2002/05/24 cbabu - bug2389773
665 added a check, so that bond register table is hit only once for a delivery_id
666
667 17 2002/05/29 asshukla - bug2392099 Added the condition to check for excise flag for a item
668
669 18 2002/06/20 ASSHUKLA - Bug 2404190 Code added for generating excise invoice generation at transaction type level.
670
671 19 2002/07/03 Nagaraj.s - For Enh#2415656.
672 Cursors pref_cur - Incorporated v_export_oriented_unit also in the select clause
673 RG Bal Cur- Incorporated basic,additional,other pla balance also in the select clause.
674 Functions jai_om_wsh_processing_pkg.excise_balance_check - for preference checks in case of EOU and Non-EOU for total excise amount
675 jai_om_wsh_pkg.get_excise_register_with_bal - for preference checks in case of EOU and Non-EOU
676 for exempted amount.
677 Before sending this patch it has to be taken care that, the alter scripts,functions should also
678 accompany the patch otherwise the patch would certainly fail.
679
680 20 2002/07/20 SSUMAITH Bug # 2454978 Added code for ensuring the tax target amount is not 0 for 0% CST.
681
682 21 2002/10/24 SSUMAITH Bug # 2638797 - Added code to see that the register is gettig hit for a trading organization
683 and the item a trading item , then the register to be hit is RG23D.
684
685 22 2003/01/02 SSUMAITH Bug # 2731434 - File Version - 615.3
686 Added the (NVL(v_bonded_flag,'Y') = 'Y' to the condition which checks if the exise_flag is 'Y' for the item
687 so that excise invoice number gets generated only if item is excisable and subinventory is bonded.
688
689 23 2003/01/13 SSUMAITH Bug # 2746921 . File Version - 615.4
690 Excise invoice number was not getting generated for a trading subinventory.This was reported after the patch
691 associated with the Bug # 2731434 was applied. It was noticed that , an earlier bug Bug # 2392099 was the reason
692 as it was considering only "item being excisable" to be the constraint for excise invoice generation .This has been
693 supplemented by the condition that the subinventory should also be bonded , which caused that for a trading
694 subinventory , excise invoice number not getting generated. This issue has been fixed by adding the following
695 check.
696 Item should be excisable AND (Subinventory is either Bonded or Trading) for excise invoice num to be generated.
697
698 23 2003/01/15 cbabu for Bug# 2736191, File Version# 615.5 (Obsoleted with 2803409)
699 When a trading transaction is done with delivery detail matched to multiple receipts, then RG23D register is not being hit properly.
700 Code changes are made to hit the register with proper quantity and reduce the balances of receipts as per the matched quantity
701
702 24. 2003/01/27 ssumaith Bug # 2769436 File version 616.6 (Obsoleted with 2803409)
703 When a transaction is done with the register code as DOM_WITHOUT_EXCISE , still excise invoice number was getting
704 generated based on gp2 instead of gp1.This was because , this transaction type was excluded in the if condition .
705 adding this condition to the if which takes care of this issue.Also taken care of register type '23D_DOM_WITHOUT_EXCISE'
706 which was not handled till now.
707
708 25 2003/02/19 cbabu for Bug# 2803409, FileVersion# 615.7
709 DELETE from JAI_OM_OE_GEN_TAXINV_T statement got deleted in file version 615.5(Bug# 2736191) somehow. This statement is
710 reincorporated with his bug. Bugs 2736191 and 2769436 were made obsolete and this bug needs to be send instead of them
711
712 26. 2003/02/20 ssumaith - Bug # 2663211 File Version # 615.8
713 Excise invoice generation logic in this procedure has been removed and instead a call to the
714 excise invoice generation procedure has been made.
715 This has dependency on the jai_cmn_setup_pkg.generate_excise_invoice_no procedure . Hence this bug
716 is a pre-requisite for future bugs.
717
718 27. 2003/07/24 Aiyer - Bug #3032569, File Version 616.1
719 The Excise Invoice number is being generated for non excisable RG23D transactions.
720 This needs to be stopped for Trading Domestic Without Excise and Export Without excise scenario's.
721 Modified the IF statment to remove the check that the trading register_codes should be in
722 '23D_DOM_WITHOUT_EXCISE' and '23D_EXPORT_WITHOUT_EXCISE' .
723 Now the excise invoice number would be generated only for orders with Bond register types as Domestic
724 Trading With Excise and Export with Excise.
725
726 Dependency Introduced Due to this Bug : -
727 None
728
729 28. 2003/07/28 Aiyer - Bug#3071342, File Version 616.2
730 As the excise invoice generation should not be done in case Domestic Without Excise for trading and manufacturing
731 organizations and hence modified the if statement to validate that
732 excise invoice generation procedure is called only in case where v_register_code is in '23D_DOMESTIC_EXCISE','23D_EXPORT_EXCISE'
733 ,'DOMESTIC_EXCISE', 'EXPORT_EXCISE' ,'BOND_REG'.
734 This would ensure that the excise invoice generation would not happen in case where v_register_code in
735 'DOM_WITHOUT_EXCISE','23D_DOM_WITHOUT_EXCISE' i.e Domestic Without Excise for Trading and manufacturing organizations.
736
737 Dependency Introduced Due to this Bug : -
738 None
739
740 29. 2003/07/31 SSUMAITH Bug # 2769440 File Version 616.3 (GL Link)
741
742 As part of the GL Link Enhancement , added parameters in call to the jai_om_rg_pkg's procedures ja_in_rg23_part_II_entry ,
743 ja_in_pla_entry and Ja_In_Rg23d_Entry.
744
745 Dependency Introduced Due to this Bug : -
746 This patch has dependency on all further patches using this object.
747
748
749 30. 2003/08/22 SSUMAITH Bug # 3021588 File Version 616.4 (Bond Register Enhancement)
750
751 For Multiple Bond Register Enhancement,
752 Instead of using the cursors for fetching the register associated with the order type , a call has been made to the procedures
753 of the jai_cmn_bond_register_pkg package. There enhancement has created dependency because of the
754 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.
755
756 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
757
758 Provision for letter of undertaking has been incorporated. In the case of the letter of undetaking , its also a type of bond register
759 but without validation for the balances.
760 This has been done by checking if the LOU_FLAG is 'Y' in the JAI_OM_OE_BOND_REG_HDRS table for the
761 associated register id , if yes , then validation is only based on bond expiry date .
762
763 Dependency Introduced Due to this Bug : -
764 This fix has introduced huge dependency . All future changes in this object should have this bug as a prereq
765
766
767 31. 2003/09/24 Aiyer - Bug#3139718, File Version 616.5
768
769 Modified the cursor get_conv_detail_cur to get the actual_shipment_date from oe_order_lines_all instead of the
770 conversion_date from oe_order_headers_all.
771 As a Sales order shipment date can be different from its creation date, hence the conversion rate
772 applicable on the date of shipment should be considered for all processing rather than the creation
773 date of the Sales order.
774 Added a call to jai_cmn_utils_pkg.currency_conversion procedure to get the conversion rate based on the actual shipment date.
775
776 Fix of Bug 3158282: -
777 1. The values passed to the parameters p_basic_ed/p_dr_basic_ed,p_additional_ed/p_dr_additional_ed and p_other_ed/p_dr_other_ed
778 in the procedures ja_in_rg_I_entry and ja_in_rg23_part_I_entry of the package
779 jai_om_rg_pkg was rounded off (using a round function to 0 decimal values) so that all values of columns basic,
780 additional and other amounts passed to the tables JAI_CMN_RG_I_TRXS, JAI_CMN_RG_23AC_I_TRXS table
781 get rounded off.
782
783 2. For the calls to the procedure procedures ja_in_rg23_part_II_entry,ja_in_pla_entry,ja_in_regsiter_txn_entry in the jai_om_rg_pkg
784 the values of fields being passed i.e basic_excise_duty_amount, add_excise_duty_amount, oth_excise_duty_amount and excise_amount
785 have been rounded off at the cursor get_total_excise_amt level itself, as these values should always be in INR.
786
787
788 Dependency Due to this bug:-
789 None
790
791
792 32. 11/10/2003 ssumaith - Bug # 3158976 File Version 616.6
793
794 Sales order number is appended to the variable v_ref_10 which holds a standard text
795 in a loop of delivery details for a particular delivery.
796 If the number of delivery details are huge , the appending is causing the width of the concatenated text
797 to exceed beyond 250 characters.
798 It causes PL/SQL Numeric or value error.
799
800 33. 11/10/2003 ssumaith - bug # 3138194 File Version 616.7
801
802 Population of ST forms related functionality is removed from this procedure and instead moved into a
803 new concurrent program. All other logic remains same , with respect to hitting the RG registers.
804
805 34. 4-nov-03 ssumaith - bug # 3207685
806
807 For excise exempted transactions, modvat entry was happening only for the first line.
808 The reason this happens because the value being fetched was only of the first record.
809 This has been corrected by calculating the excise exempted amount correctly and passing
810 it to the jai_om_rg_pkg .
811
812 35. 11-Nov-2003 Aiyer - Bug #3249375 File Version 617.1
813 References to JA_IN_OE_ST_FORMS_HDR table, which has been obsolete post IN60105D1 patchset, was found
814 in this file in some cursors.
815 As these tables do not exists in the database any more, post application of the above mentioned patchset
816 hence deleting the cursors.
817
818 Dependency Due to This Bug:-
819 Can be applied only post application of IN60105D1.
820
821 36.05-Dec-2003 ssumaith - bug # 3229697 version 617.2
822 Performance improvement done in 'Excise invoice genration program'
823
824 Dependency Due to This Bug:-
825 None
826
827 37.30-jan-04 ssumaith bug# 3368475 file version 618.1
828
829 CENVAT Reversal Entries should not be passed for CT3 Transaction.
830
831 On Shipping goods to an 100% EOU against a CT 3 Form at an exempted rate,
832 India Localization reverses the CENVAT at 8% of the Base Amount. This
833 is incorrect. The CENVAT should not be reversed.
834
835 This has been acheived by making code changes:
836
837 1) if CT3 type of excise exemption is chosen in the sales order :
838 only quantity register gets hit
839 amount register does not get hit.
840 2) if other than CT3 type of excise exemption is chosen in the sales order
841 both quantity and amount registers get hit.
842
843 3) Please note that in cases where amount registers are not hit because of CT3 excise exemption, the
844 register column in the JAI_OM_WSH_LINES_ALL table will show NULL. this is
845 also the change which i have incorporated.
846
847 Dependency Due to This Bug:-
848 None
849
850 38. 18-Feb-2004 Aiyer Bug #3448674, File Version 618.2
851 Issue:-
852 ======
853 Amount registers are hit with excise amoutns which are getting round at Shipping Line level
854 instead of at a Delivery level.
855
856 Solution:-
857 ==========
858 Changed the cursor get_total_excise_amt to do rounding at a paticualr delivery level rather
859 than the line level.
860 For this , we are first summing the taxes and then rounding them. This prevents line level
861 rounding and enforces delivery level rounding.
862
863 Dependency Due to This Bug:-
864 None
865
866 38. 09-Mar-2004 Aiyer Bug #3446362, File Version 618.3(reopened) fixed in 618.4.
867 Issue:-
868 ==========
869 When a Order has multiple lines out of which one line is Excise Exempted and the other are not,
870 then the behaviour expected is that the item which is not excise exempt should hit Excise registers.
871 However, the same is not currently happening.
872
873 Reason: -
874 ======
875 This was happening as the code that calls the jai_om_rg_pkg package to insert records into any of the amount
876 registers i.e either of JAI_CMN_RG_23AC_II_TRXS or JAI_CMN_RG_PLA_TRXS was bypassed if the first one was found to contain
877 a 'CT3' type of exemption.
878
879 Fix :-
880 ======
881 Modified the code to check whether any of the delivery details for the given delivery has a excise exemption
882 type other than 'CT3' (this includes null rows also). Only if one or more such records exist, then
883 hit the amount registers, else bypass the call.
884 Cursor c_ct3_flag_exists has been added to the code to take the count of records which do not have excise exemption
885 of type CT3.
886
887 38. 19-Mar-2004 Aiyer Bug #3446362, File Version 618.4(reopened) fixed in 619.1
888 Issue:-
889 ========
890 The bug 3446362 version 618.4 of this file did not work correctly on the clients instance.
891 It was still not entering the excise amount line in the amount registers when the order conatined mulitple lines with the first line excise exempted.
892
893 Reason:-
894 The amount register is hit based on the which amount register needs to be hit.The value of amount register to be hit is stored in the variable v_reg_type.Now initially the v_reg_type
895 would be set to Null for the line which had 'CT3'type of exemption.
896 Now if this line happens to be the first line then and the register are also hit only once, so the code ignores the other lines as v_reg_type is set to null.
897
898 Fix:-
899 =====
900 The fix done is that instead of setting the v_reg_type to null when a 'CT3' exemption is found and later
901 updating this variable into JAI_OM_WSH_LINES_ALL.register, handled this condition though a decode
902 statement, instead of directly updating the v_reg_type variable.
903 The v_reg_type now still holds the value for the register, where as the table is updated with null when ever the exemption is 'CT3'.
904 This has rectified the problem.
905
906 Dependency Due to This Bug:-
907 None
908
909 39. 24-Aug-2004 Sanjikum Bug #3849638, File Version 115.1
910 Issue:-
911 ========
912 Excise invoice number is generated even when inventory interface ends in warning
913
914 Reason:-
915
916 For selecting the cases where Inventory Interface has failed, the following condition was being used -
917 NVL(wdd.inv_interfaced_flag,'N') = 'N'
918 For the failure there can be one more status - 'P'. Which is being missed in this case.
919
920 Fix:-
921 =====
922 The fix done is that instead of condition - NVL(wdd.inv_interfaced_flag,'N') = 'N'
923 The new condition is used - NVL(wdd.inv_interfaced_flag,'N') <> 'Y'
924 This has rectified the problem.
925 While updating the JAI_OM_WSH_LINES_ALL, new columns are added -
926 last_update_date, last_updated_by, last_update_login, as these were not previously updated
927
928 Dependency Due to This Bug:-
929 None
930
931 40. 20-JAN-2005 - ssumaith - Bug#4136981 - Corrected the call to the JAI_OM_OE_BOND_TRXS entry.
932 It was not consistent with the other calls , such as rg1 entry , rg23_part_ii entry , pla entry.
933 This fix introduces no dependency.
934
935 41. 2005/02/11 ssumaith - bug# 4171272 - File version 115.3
936
937 Shipment needs to be stopped if education cess is not available.
938
939 The basic business logic validation is that both cess and excise should be available as
940 part of the same register type and the register preference setup at the organization additional information
941 needs to be considered for picking up the correct register .
942
943 This code object calls the functions jai_om_wsh_processing_pkg.excise_balance_check_f and jai_om_wsh_pkg.get_excise_register_with_bal_f
944 which have had changes in their signature and hence the caller also needs to pass the correct
945 parameters.
946
947 The change done in this object is to pass the additional parameters correctly to the functions.
948
949 Dependency Due to this Bug:-
950 The current procedure becomes dependent on the functions jai_om_wsh_processing_pkg.excise_balance_check (version 115.1) and
951 jai_om_wsh_pkg.get_excise_register_with_bal (version 115.1) also packaged as part of this bug.
952
953 42. 2005/02/16 ssumaith - bug# 4185392 - File version 115.4
954
955 Excise Duty rate was going in as zero in JAI_CMN_RG_I_TRXS table. This was because the variable
956 corresponding to the excise_duty_rate parameter in the ja_in_rg_i_entry procedure was
957 explicitly set to zero.
958
959 This has been changed and made as excise_duty_amount divided by (assessable value * shipped quantity).
960 Care has been taken to ensure that zero divide error does not come by checking for non zero values
961 for the elements in the denominator of the fraction.
962
963 As expected by IL support , rounding the tax_rate to two decimals.
964
965 Dependency Due to this Bug:-
966 None.
967
968 43. 08-Jun-2005 File Version 116.3. Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
969 as required for CASE COMPLAINCE.
970
971 44. 13-Jun-2005 Ramananda for bug#4428980. File Version: 116.4
972 Removal of SQL LITERALs is done
973
974 45. 06-Jul-2005 Ramananda for bug#4477004. File Version: 116.5
975 GL Sources and GL Categories got changed. Refer bug for the details
976
977 46. 9-Aug-2005 Ramananda for bug#4543424. File version 120.2
978 Excise Duty rate was going as fractions in JAI_CMN_RG_I_TRXS table.
979 This is because the excise amount is rounded off at the shipment level and when the rate is recalculated
980 it is calculated as excise amount divided by assessable value * 100. This results in rounding issue.
981
982 This has been resolved by making the following changes.
983 The excise rate is calculated as a sum of total 'EXCISE' tax rates divided by the number of 'EXCISE' taxes.
984
985 Dependency Due to this Bug:-
986 None.
987
988 47. 19-Aug-2005 Bug4562791. Added by Lakshmi Gopalsami Version 120.3
989 The excise invoice date , the date on which rg registers are hit should be the
990 date when the concurent request is submitted. This is as per the product
991 management requirement.
992
993 Hence added a cursor that gets the request submitted date and punched that
994 date in the calls to the ja_in_rg_pkg.ja_in_rg23_part_ii_entry ,
995 ja_in_Rg_pkg.pla_entry and the same gets carried forward
996 to the gl interface as well.
997
998 Also changed the creation_date and last_update_date with v_date.
999
1000 Dependencies (Compilation and Functional Dependencies)
1001 ------------
1002 jai_om_rg.pls 120.2
1003 jai_om_rg.plb 120.3
1004
1005 48. 23-Aug-2005 Aiyer - Bug 4566054 (Forward porting for the 11.5 bug 4346220 ),Version 120.4
1006 Issue :-
1007 Rg does not show correct cess value in case of Shipment transactions.
1008
1009 Fix:-
1010 Two fields cess_amt and source have been added in JAI_CMN_RG_I_TRXS table.
1011 The cess amt and source would be populated from jai_jar_t_aru_t1 (Source -> 'AR' ) and
1012 as 'WSH' from jai_om_wsh.plb procedure Shipment.
1013 Corresponding changes have been done in the form JAINIRGI.fmb and JAFRMRG1.rdf .
1014 For shipment and Ar receivable transaction currently the transaction_id is 33 and in some cases where the jai_cmn_rg_i_trxs.ref_doc_id
1015 exactly matches the jai_om_wsh_lines_all.delivery_detail_id and jai_ar_trxs.customer_trx_id the tracking of the source
1016 becomes very difficult hence to have a clear demarcation between WSh and AR sources hence the source field has been added.
1017
1018 Added 2 new parametes p_cess_amt and p_source to jai_om_rg_pkg.ja_in_rg_i_entry package.
1019 This has been populated from this and jai_om_wsh_pkg.process_delivery procedure.
1020
1021 A migration script has been provided to migrate the value for cess and source.
1022
1023 Dependency due to this bug:-
1024 1. Datamodel change in table JAI_CMN_RG_I_TRXS, added the cess_amt and source fields
1025 2. Added two new parameters in jai_om_rg_pkg.ja_in_rg_i_entry procedure to insert data into JAI_CMN_RG_I_TRXS table
1026 3. Modified the trigger jai_jar_t_aru_t1
1027 4. Procedure jai_om_wsh_pkg.process_delivery
1028 5. Report JAICMNRG1.rdf
1029 6. Created a migration script to populate cess_amt and source for Shipment and Receivable transactions.
1030 Both functional and technical dependencies exists
1031
1032
1033 49. 01-DEC-2005 Aiyer - Bug 4765347 ,Version 120.5
1034 Issue :- Excise invoice program runs into error.
1035 Fix :- Changed the form JAIITMCl.fmb to insert into the jai_inv_items_setups form
1036 also changed the current procedure to add who column into jai_cmn_errors_t.
1037
1038 Dependencies introduced due to this bug:-
1039 Yes, please refer the future dependencies section.
1040
1041 50. 13-Feb-2007 bgowrava for forward porting bug#5554420 (11i bug#5531051). File Version 120.7
1042 Issue: Excise invoice/register not getting updated properly in ja_in_so_picking_lines
1043 Also observed that if excise invoice is not generated for first line of delivery,
1044 then it is not getting generated at all.
1045
1046 Resolution: introduced the flag to know whether excise invoice is generated for the delivery.
1047 if not generated for 1st line, added the code to execute the generation code again for next lines for delivery
1048 - added new cursor c_excise_tax_cnt to know the excise taxes count and generate exc invoice number
1049 only if count > 0
1050 - Added the logic to execute amount register hitting logic once for every delivery (usually this
1051 will be done for the first line that has excise implication)
1052
1053 51. 23/02/07 bduvarag for bug#5403048,File version 120.8
1054 Forward porting the changes done in 11i bug 5401533
1055
1056 52. 13-April-2007 ssawant for bug 5989740 ,File version 120.9
1057 Forward porting Budget07-08 changes of handling secondary and
1058 Higher Secondary Education Cess from 11.5( bug no 5907436) to R12 (bug no 5989740).
1059
1060 53. 18/Apr/2007 Bgowrava for forward porting bug#5989740, 11i BUG#5907436 File Version 120.9
1061 ENH: Handling Secondary and Higher Education Cess
1062 Added the new cess types jai_constants.tax_type_sh_exc_edu_cess, jai_constants.tax_type_sh_cvd_edu_cess
1063
1064 54. 28/05/2007 CSahoo for bug#6077065, File version 120.10
1065 Issue: The excise invoice num and excise_invoice_date was not getting updated in the JAI_OM_WSH_LINES_ALL table.
1066 Fix:added the following line in the code lv_exc_inv_gen_for_dlry_flag := 'Y'.
1067
1068
1069 55. 05/07/2007 kunkumar for Bug#6121833 File version 120.11
1070 Added an if condition After open-fetch-close in lv_statement_no :=18
1071
1072
1073 56. 20-MAY-2008 JMEENA bug#7043292
1074 Issue :- Excise Invoice Generation completes with ORA-01476
1075 Fix :- The calculation of v_proportionate_edr was using v_total_base_duty_amount fetched
1076 from matched_receipt_cur which was comming as zero.
1077 A check is introduced to verify that v_proportionate_edr is calculated
1078 only in the case when v_total_base_duty_amount is non-zero.
1079
1080 57. 26-Aug-2008 vkaranam for bug#7354983, File Version 115.20.6107.11
1081 Issue: EXCISE INVOICE DATE AND AR INVOICE DATE FOR DELIVERY ID NOT IN SYNCHRONIZATION
1082 FIX: Modified the code to populate the excise invoice date with the actual shipment date.
1083 Further the gl accounting date and the transaction dates for all the register
1084 updates is also populated by the actual shipment date.
1085
1086 58. 30-dec-2008 CSahoo for bug#7647742, File Version 120.6.12000000.7
1087 Reverted the changes done in the file version 120.6.12000000.6
1088 Further removed the nvl condition in IF condition for checking modvat tax rate.
1089
1090 59. 10-nov-2009 vkaranam for bug#8904363
1091 Issue:
1092 TST1212 XB1:QA: UNABLE TO GENERATE EXCISABLE INVOICE
1093
1094 If the subinventory is neither bonded nor tradable ,conncurrent request is not showing the user message.
1095
1096 Fix:
1097 Added the user log message
1098 "Subinventory is neither bonded nor tradable .henc eexcise invoice number cannot be generated".
1099 this will be displayed in the India excise invocie generation log file only if bonded_flag='N' and trading_flag='N'.
1100
1101 60 24-jun-2010 vkaranam for bug#9839132
1102 issue:
1103 rg23d register is hitting the wrong duty for foreign currency transactions.
1104 fix:
1105 passed v_tot_duty_amt instead of v_excise_amount*conversion rate.
1106
1107 61. 17-Dec-2012 mmurtuza for bug 15971482
1108 Description: INDIA - RG1 REGISTER REPORT SHOWING THE EXCISE PERCENTAGE WRONGLY
1109 Fix: Commented the fix done for bug 4185392. Also added cursor c_excise_base_tax_amt to fetch base tax amount of excise tax
1110 and pass the same to jai_om_rg_pkg.ja_in_rg_i_entry.
1111
1112
1113 62. 31-Dec-2012 nkodkan for the bug 16014905
1114 Description: RG1 REGISTER ENTRY FORM SHOWING THE ASSESSABLE VALUE IN FOREIGN CURRENCY
1115 Fix : Multiplied the Assessable Value and excise amount with conversion rate (v_converted_rate)
1116
1117 63. 21-Jan-2013 nkodakan for the bug 16197185
1118 Description: RG1 REGISTER ENTRY FORM SHOWING THE ASSESSABLE VALUE IN FOREIGN CURRENCY
1119 Fix : removed the v_converted_rate which is multiplying with ln_total_of_base_tax in a call jai_om_rg_pkg.ja_in_rg_i_entry
1120
1121 64. 19-FEB-2013 nkodakan for the bug 16353242
1122 Description : TIME STAMP FOR EXCISE INVOICE IS NOT COMING IN JAI_OM_WSH_LINES_ALL TABLE
1123 Fix : Removed the TRUNC function for v_actual_shipment_date
1124
1125 65. 05-Mar-2013 mmurtuza for bug 16308603
1126 Description: INDIA EXCISE INVOICE GENERATION PROGRAM ,PICKS ALL DELIVERIES ACROSS OU
1127 Fix: Changed the pick to pick the delivery ids when the delivery id is not provided as input
1128
1129 66. 22-Mar-2013 mmurtuza for bug 16534065
1130 Description: EXCISE AMOUNT IS ROUNDED IN REGISTERS
1131 Fix: Removed the rounding
1132
1133 07-Apr-2013 mmurtuza for bug 16343067
1134 Description: EXCISE AMOUNT IS ROUNDED IN REGISTERS
1135 Fix: Added the rounding for get_total_excise_amount
1136
1137
1138 Future Dependencies For the release Of this Object:-
1139 (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/
1140 A datamodel change )
1141
1142 ----------------------------------------------------------------------------------------------------------------------------------------------------
1143 Current Version Current Bug Dependent Files Version Author Date Remarks
1144 Of File On Bug/Patchset Dependent On
1145 process_delivery.sql
1146 ----------------------------------------------------------------------------------------------------------------------------------------------------
1147 616.1 3032569 IN60104D1 None -- Aiyer 24/07/2003 Row introduces to start dependency tracking
1148
1149
1150 616.3 2769440 IN60104D1 + jai_om_rg_pkg.sql Ssumaith 31/07/2003 GL Link Enhancement.
1151 2801751 + ja_in_gl_interface_new.sql
1152 2769440
1153
1154 616.4 3021588 IN60104D1 + ssumaith 22/08/2003 Bond Register Enhancement
1155 2801751 +
1156 2769440
1157
1158 617.1 3249375 IN60105D1 Aiyer 11/Nov/2003 Can be applied only after IN60105D1 patchset
1159 has been applied.
1160
1161 619.1 3446362 IN60105D2 None -- Aiyer 19/03/2004 Row introduces to start dependency tracking
1162
1163
1164 115.3 4171272 IN60106 +
1165 4147608 jai_om_wsh_pkg.get_excise_register_with_bal_f.sql 115.1 ssumaith 11/02/2005 New parameters added to function.
1166 jai_om_wsh_processing_pkg.excise_balance_check_f.sql 115.1 ssumaith 11/02/2005 New parameters added to function.
1167
1168 12.0 4566054 jai_om_rg.pls 120.3 Aiyer 24-Aug-2005
1169 jai_om_rg.plb 120.4
1170 jai_om_wsh.plb (jai_om_wsh_pkg.process_delivery) 120.4
1171 JAINIRGI.fmb 120.2
1172 jain14.odf 120.3
1173 jain14reg.ldt 120.3
1174 New migration script to port data into new tables 120.0
1175 JAICMNRG1.rdf 120.3
1176 jai_jai_t.sql (trigger jai_jar_t_aru_t1) 120.1
1177
1178 120.4 4765347 JAIITMCL.fmb 120.9
1179 jai_om_rg.plb 120.4
1180
1181 ------------------------------------------------------------------------------------------------------------------------------------------------*/
1182 /* Ramananda for File.Sql.35 */
1183 lv_debug := jai_constants.yes ;
1184 lv_procedure_name := 'process_delivery';
1185 v_creation_date := SYSDATE;
1186 v_source_name := 'Register India' ;
1187 v_category_name := 'Register India' ;
1188 v_ref_10 := 'India Localization Entry for sales order # '; -- will hold a standard text such as 'India Localization Entry for sales order'
1189 v_std_text := 'India Localization Entry for sales order # '; -- bug # 3158976
1190 v_ref_23 := 'process_delivery'; -- holds the object name -- 'process_delivery'
1191 v_ref_24 := 'wsh_new_deliveries'; -- holds the table name -- ' wsh_new_deliveries'
1192 v_ref_25 := 'delivery_id'; -- holds the column name -- 'delivery_id';
1193 /* Ramananda for File.Sql.35 */
1194
1195 lv_block_no := '0';
1196 Fnd_File.PUT_LINE(Fnd_File.LOG, ' 1 START Delivery id = ' || p_delivery_id );
1197 FOR Each_record IN
1198 (
1199 SELECT *
1200 FROM JAI_OM_OE_GEN_TAXINV_T ja_tmp
1201 WHERE delivery_id = p_delivery_id
1202 and /*The idea of putting the exists is to see that do not process a delivery ,if it has
1203 at least one delivery detail with inv_interfaced_flag = 'N' */
1204 not exists
1205 ( select 1
1206 FROM
1207 wsh_delivery_details wdd ,
1208 wsh_new_deliveries wnd ,
1209 wsh_delivery_assignments wda
1210 WHERE
1211 wdd.delivery_detail_id = wda.delivery_detail_id AND
1212 wda.Delivery_Id = wnd.Delivery_Id AND
1213 wnd.Delivery_Id = ja_tmp.delivery_id AND
1214 wdd.source_code = 'OE' AND
1215 --NVL(wdd.inv_interfaced_flag,'N') = 'N'
1216 --Commented the above and added the below for bug #3849638
1217 NVL(wdd.inv_interfaced_flag,'N') <> 'Y'
1218 )
1219 )
1220 LOOP
1221 v_ref_26 := p_delivery_id ;
1222 OPEN c_order_num(each_record.order_header_id) ;
1223 FETCH c_order_num into v_ord_num ;
1224 CLOSE c_order_num ;
1225
1226
1227
1228 /* Bug 4562791. Added by Lakshmi Gopalsami */
1229 OPEN c_conc_request_submit_date(FND_GLOBAL.conc_request_id);
1230 FETCH c_conc_request_submit_date INTO ld_request_submit_Date;
1231 CLOSE c_conc_request_submit_date;
1232 /* Ends here Bug 4562791. Added by Lakshmi Gopalsami */
1233
1234 v_ref_10 := v_std_text || v_ord_num; -- instead of appending v_ref_10 every time , appending the so# to the standard text instead.-- bug # 3158976
1235 Fnd_File.PUT_LINE(Fnd_File.LOG, p_delivery_id||', '||'2 Each_record.Delivery detail id = ' || Each_record.delivery_detail_id );
1236 v_tax_rate := 0; --2001/08/07 Jagdish
1237 lv_statement_no := '1';
1238
1239 OPEN get_delivery_status(each_record.delivery_id);
1240 FETCH get_delivery_status INTO v_status_code,v_initial_pickup_date;
1241 CLOSE get_delivery_status;
1242
1243 IF v_status_code NOT IN ('CO','IT','CL') THEN
1244 return;
1245 END IF;
1246
1247 -- 2001/10/05 Jagdish Bhosle
1248 lv_statement_no := '2';
1249
1250 OPEN get_order_line_id(Each_record.delivery_detail_id);
1251 FETCH get_order_line_id INTO v_source_line_id_pick;
1252 CLOSE get_order_line_id;
1253
1254 --Anuradha Parthasarathy
1255 lv_statement_no := '4';
1256 OPEN get_actual_shipment_date(v_source_line_id_pick);
1257 FETCH get_actual_shipment_date INTO v_actual_shipment_date;
1258 CLOSE get_actual_shipment_date;
1259
1260 /* Bug 4562791. Added by Lakshmi Gopalsami
1261 Commented the below and initialised with different value
1262 v_date := NVL(v_initial_pickup_date,v_actual_shipment_date); */
1263 v_date := ld_request_submit_Date;
1264
1265 /* Bug 4562791. Added by Lakshmi Gopalsami
1266 The concurrent submission date needs to be the excise invoice date and
1267 transaction date in the rg registers and the same date should be
1268 the accounting date in gl_interface.
1269 */
1270 jai_om_rg_pkg.gl_accounting_date :=v_actual_shipment_date; --replaced v_date for bug#7354983
1271
1272
1273 --Anuradha Parthasarathy
1274 v_created_by := Each_record.created_by ;
1275 v_last_update_date := Each_record.last_update_date;
1276 v_last_updated_by := Each_record.last_updated_by;
1277 v_last_update_login := Each_record.last_update_login;
1278
1279 lv_statement_no := '5';
1280 OPEN Get_delivery_detail_cur(each_record.delivery_detail_id);
1281 FETCH Get_delivery_detail_cur INTO v_order_line_id,
1282 v_organization_id, v_location_id, v_register, v_inventory_item_id,
1283 v_uom_code, v_shp_qty, v_item_class, v_excise_flag, v_basic_ed_amt,
1284 v_addl_ed_amt, v_oth_ed_amt, v_excise_amount, v_excise_exempt_type,
1285 v_selling_price, v_customer_id, v_ship_to_org_id,
1286 v_order_type_id, v_subinventory, v_assessable_value,
1287 v_excise_exempt_refno, v_org_id; --2001/04/01 Vijay
1288 CLOSE Get_delivery_detail_cur;
1289
1290
1291
1292 /* following cursor modified from c_excise_tax_rate to c_excise_tax_cnt because this cursor should
1293 only be used for excise invoice generation. bgowrava for Bug#5554420*/
1294 ln_excise_tax_cnt := 0;
1295 OPEN c_excise_tax_cnt(each_record.delivery_detail_id);
1296 FETCH c_excise_tax_cnt INTO ln_excise_tax_cnt ;
1297 CLOSE c_excise_tax_cnt;
1298
1299
1300 Fnd_File.PUT_LINE(Fnd_File.LOG, p_delivery_id||', '||'4 v_register = ' || v_register
1301 ||', v_order_type_id = ' || v_order_type_id ||', v_excise_exempt_type = ' || v_excise_exempt_type
1302 );
1303
1304 IF v_register IS NULL THEN --z999
1305 lv_statement_no := '6';
1306
1307 /* Bug 5243532. Added by Lakshmi Gopalsami
1308 * Removed the cursor set_of_books_cur and implemented using caching logic.
1309 */
1310 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
1311 (p_org_id => v_organization_id );
1312 v_set_of_books_id := l_func_curr_det.ledger_id;
1313
1314 Fnd_File.PUT_LINE(Fnd_File.LOG, p_delivery_id||', '||'7 v_set_of_books_id = ' || v_set_of_books_id);
1315 lv_statement_no := '7';
1316
1317 /*
1318 Code changed by aiyer for the bug #3139718.
1319 Modified the cursor get_conv_detail_cur to also provide the sales order line_id as input
1320 Added a call to jai_cmn_utils_pkg.currency_conversion procedure to get the conversion rate based on the actual shipment date.
1321 */
1322 -- Start of code for bug #3139718
1323 OPEN get_conv_detail_cur(
1324 cp_order_header_id => each_record.order_header_id ,
1325 cp_line_id => v_order_line_id
1326 );
1327
1328 FETCH get_conv_detail_cur INTO v_order_number ,
1329 v_currency_code ,
1330 v_conv_type_code,
1331 v_conv_rate ,
1332 v_conv_date;
1333
1334 IF get_conv_detail_cur%FOUND THEN
1335 v_converted_rate := jai_cmn_utils_pkg.currency_conversion (
1336 v_set_of_books_id ,
1337 v_currency_code ,
1338 v_conv_date ,
1339 v_conv_type_code ,
1340 v_conv_rate
1341 );
1342
1343 if v_converted_rate is null then
1344 v_converted_rate := 1;
1345 end if;
1346 END IF;
1347 -- End of code for bug #3139718
1348 CLOSE get_conv_detail_cur;
1349
1350 lv_statement_no := '8' ;
1351 OPEN bonded_cur(v_organization_id, v_subinventory) ;
1352 FETCH bonded_cur INTO v_bonded_flag,v_trading_flag ;
1353 CLOSE bonded_cur ;
1354
1355 fnd_file.put_line( fnd_file.log,
1356 p_delivery_id||', '||'8 v_organization_id = ' || v_organization_id
1357 ||', v_subinventory = ' || v_subinventory ||', v_bonded_flag = ' || v_bonded_flag
1358 ||', v_trading_flag = ' || v_trading_flag
1359 );
1360
1361 lv_statement_no := '9';
1362
1363 --start additions for bug#8904363
1364 if nvl( v_bonded_flag,'X')='N' and nvl(v_trading_flag,'X')='N'
1365 then
1366 fnd_file.put_line( fnd_file.log,'Subinventory '||v_subinventory ||' is neither bonded nor Tradable.Hence Excise Invoice Number cannot be generated.');
1367 end if;
1368 --end additions for bug#8904363
1369
1370
1371 /*
1372 Code modified by sriram - bug # 3021588 - Multiple Bond Registers.
1373 Calling the package jai_cmn_bond_register_pkg.GET_REGISTER_ID
1374 */
1375
1376
1377 jai_cmn_bond_register_pkg.get_register_id ( v_organization_id ,
1378 v_location_id ,
1379 v_order_type_id , -- order type id
1380 'Y' , -- order invoice type
1381 v_asst_register_id , -- out parameter to get the register id
1382 v_register_code
1383 ); -- out parameter to get the register code
1384
1385
1386
1387 fnd_file.put_line(
1388 fnd_file.log,
1389 p_delivery_id||', '||', v_register_code = ' || v_register_code
1390 );
1391 lv_statement_no := '10';
1392 OPEN fin_year_cur(v_organization_id);
1393 FETCH fin_year_cur INTO v_fin_year;
1394 CLOSE fin_year_cur;
1395
1396 /*
1397 code added here by sriram for modvat recovery in case of excise exempt transactions
1398 */
1399 IF v_item_class NOT IN ('OTIN', 'OTEX') THEN
1400
1401 IF v_excise_exempt_type IN ('CT2_OTH', 'EXCISE_EXEMPT_CERT_OTH' ) THEN
1402 lv_statement_no := '17';
1403 OPEN for_modvat_tax_rate(each_record.delivery_detail_id);
1404 FETCH for_modvat_tax_rate INTO v_modvat_tax_rate,v_rounding_factor;
1405 CLOSE for_modvat_tax_rate;
1406 ELSE
1407 IF v_excise_exempt_type IS NOT NULL THEN
1408 lv_statement_no := '18';
1409 OPEN for_modvat_percentage(v_organization_id, v_location_id);
1410 FETCH for_modvat_percentage INTO v_modvat_tax_rate;
1411 CLOSE for_modvat_percentage;
1412
1413
1414 --Added the below by kunkumar for bug#6121833
1415 IF v_modvat_tax_rate IS NULL THEN --removed the nvl condition for bug#7647742 because even if modvat rate is 0 it gives the error
1416 fnd_file.put_line(fnd_file.log, p_delivery_id||', '||'ERROR - MODVAT REVERSAL% SHOULD BE DEFINED IN ORGANIZATION ADDITIONAL INFORMATION ');
1417 errbuf := 'Error - MODVAT Reversal% should be defined in Organization Additional Information';
1418 retcode := 2; --to signal an error.
1419 return;
1420 END IF;
1421 --Ends additions by kunkumar for Bug#6121833
1422 END IF;
1423 END IF;
1424
1425 lv_statement_no := '19';
1426 fnd_file.put_line(fnd_file.log, p_delivery_id||', '||'8.01 v_exempt_bal = ' || v_exempt_bal);
1427 v_exempt_bal := NVL(v_exempt_bal, 0) +( v_shp_qty * v_assessable_value * NVL(v_modvat_tax_rate,0))/100;
1428 fnd_file.put_line(fnd_file.log, p_delivery_id||', '||'8.02 v_exempt_bal = ' || v_exempt_bal);
1429 end if;
1430
1431 /* following 3 lines of code added by bgowrava for Bug#5554420 */
1432 if nvl(v_old_delivery_id,0) <> nvl(each_record.delivery_id,-1)
1433 then
1434 lv_exc_inv_gen_for_dlry_flag := 'N';
1435 v_old_register := null;
1436 v_old_excise_invoice_no := null;
1437 end if;
1438
1439 /* following code moved here from below by bgowrava for bug#5554420 */
1440 if v_old_register is null
1441 then
1442 OPEN get_prev_del_dtl(each_record.delivery_id);
1443 FETCH get_prev_del_dtl INTO v_old_register, v_old_excise_invoice_no;
1444 CLOSE get_prev_del_dtl;
1445 end if;
1446
1447
1448 Fnd_File.PUT_LINE(Fnd_File.LOG, p_delivery_id||', 9 v_fin_year = ' || v_fin_year
1449 ||', v_old_delivery_id = ' || v_old_delivery_id );
1450 IF NVL(v_old_delivery_id,0) <> NVL(each_record.delivery_id,-1)
1451 or lv_exc_inv_gen_for_dlry_flag = 'N' /* condition added by bgowrava for bug#5554420 */
1452 THEN --f999
1453
1454 lv_statement_no := '11';
1455 /* commented here and moved out of this if condition. bgowrava for bug#5554420.
1456 v_old_register := NULL;
1457 v_old_excise_invoice_no := NULL;
1458 OPEN get_prev_del_dtl(each_record.delivery_id);
1459 FETCH get_prev_del_dtl INTO v_old_register, v_old_excise_invoice_no;
1460 CLOSE get_prev_del_dtl;
1461 */
1462
1463 fnd_file.put_line(
1464 fnd_file.log,
1465 p_delivery_id||', '||'15 v_old_register = ' || v_old_register
1466 ||', v_old_excise_invoice_no = ' || v_old_excise_invoice_no
1467 );
1468
1469 IF v_old_register IS NULL THEN --e999
1470 v_reg_type := NULL;
1471 v_rg_type := NULL;
1472 v_exc_invoice_no := NULL;
1473
1474 lv_statement_no := '12';
1475
1476
1477 /* modvat recovery was happening for last line only - bug # 3207685
1478 the following loop added for modvat recovery calculation.
1479 */
1480 for modvat_rec in c_get_modvat_records(
1481 p_delivery_id => each_record.delivery_id
1482 )
1483 loop
1484 if modvat_rec.excise_Exempt_type in ('CT2_OTH', 'EXCISE_EXEMPT_CERT_OTH') then
1485 OPEN for_modvat_tax_rate(modvat_rec.delivery_detail_id);
1486 FETCH for_modvat_tax_rate INTO v_modvat_tax_rate,v_rounding_factor;
1487 CLOSE for_modvat_tax_rate;
1488 v_mod_basic_ed_amt := nvl(v_mod_basic_ed_amt,0) + ( (modvat_rec.quantity * modvat_rec.assessable_value * v_modvat_tax_rate )/ 100 );
1489 else
1490 if modvat_rec.excise_exempt_type is not null then
1491 OPEN for_modvat_percentage(v_organization_id, v_location_id);
1492 FETCH for_modvat_percentage INTO v_modvat_tax_rate;
1493 CLOSE for_modvat_percentage;
1494 v_mod_basic_ed_amt := nvl(v_mod_basic_ed_amt,0) + ( (modvat_rec.quantity * modvat_rec.assessable_value * v_modvat_tax_rate )/ 100 );
1495 end if;
1496 end if;
1497 end loop;
1498
1499
1500
1501 OPEN get_total_excise_amt(
1502 p_delivery_id => each_record.delivery_id ,
1503 cp_conversion_rate => v_converted_rate
1504 );
1505
1506 FETCH get_total_excise_amt INTO v_tot_basic_ed_amt, v_tot_addl_ed_amt, v_tot_oth_ed_amt, v_tot_excise_amt;
1507 CLOSE get_total_excise_amt;
1508
1509
1510 fnd_file.put_line(
1511 fnd_file.log,
1512 p_delivery_id||', 17 v_tot_excise_amt = ' || v_tot_excise_amt
1513 ||', v_tot_basic_ed_amt = ' || v_tot_basic_ed_amt ||', v_tot_addl_ed_amt = ' || v_tot_addl_ed_amt
1514 ||', v_tot_oth_ed_amt = ' || v_tot_oth_ed_amt
1515 || ',v_mod_basic_ed_amt =' || v_mod_basic_ed_amt
1516 );
1517
1518 IF NVL(v_bonded_flag,'Y') = 'Y' AND
1519 (
1520 nvl(v_tot_excise_amt,0) > 0 OR
1521 v_excise_exempt_type IS NOT NULL
1522 )
1523 THEN ---b999
1524 lv_statement_no := '13';
1525 --Changed by Nagaraj.s for Enh#2415656
1526 OPEN pref_cur(v_organization_id, v_location_id);
1527 FETCH pref_cur INTO v_pref_rg23a, v_pref_rg23c, v_pref_pla,v_export_oriented_unit;
1528 CLOSE pref_cur;
1529 Fnd_File.PUT_LINE(Fnd_File.LOG, p_delivery_id||', '||'18 v_pref_rg23a = ' || v_pref_rg23a ||', v_pref_rg23c = ' || v_pref_rg23c ||', v_pref_pla = ' || v_pref_pla );
1530
1531 lv_statement_no := '14';
1532 ----Changed by Nagaraj.s for Enh#2415656
1533 OPEN rg_bal_cur(v_organization_id, v_location_id);
1534 FETCH rg_bal_cur INTO v_rg23a_balance, v_rg23c_balance, v_pla_balance,
1535 v_basic_pla_balance,v_additional_pla_balance,v_other_pla_balance;
1536 CLOSE rg_bal_cur;
1537
1538 Fnd_File.PUT_LINE(Fnd_File.LOG, p_delivery_id||', '||'19 v_rg23a_bala = ' || v_rg23a_balance ||', v_rg23c_bal = '
1539 || v_rg23c_balance ||', v_pla_balae = ' || v_pla_balance );
1540
1541 lv_statement_no := '15';
1542 OPEN ssi_unit_flag_cur(v_organization_id, v_location_id);
1543 FETCH ssi_unit_flag_cur INTO v_ssi_unit_flag;
1544 CLOSE ssi_unit_flag_cur;
1545
1546 lv_statement_no := '16';
1547
1548 /*
1549 Code modified by sriram - bug # 3021588.
1550 Calling the package jai_cmn_bond_register_pkg.GET_REGISTER_ID
1551 */
1552
1553 jai_cmn_bond_register_pkg.GET_REGISTER_ID ( v_organization_id ,
1554 v_location_id ,
1555 v_order_type_id , -- order type id
1556 'Y' , -- order invoice type
1557 v_asst_register_id , -- out parameter to get the register id
1558 v_register_code
1559 ); -- out parameter for register code
1560
1561
1562 IF NVL(v_register_code,'N') IN ('DOMESTIC_EXCISE','EXPORT_EXCISE') THEN ---a999
1563 IF NVL(v_excise_flag,'N') = 'Y' THEN
1564 IF NVL(v_excise_exempt_type, '@@@') NOT IN (
1565 'CT2',
1566 'EXCISE_EXEMPT_CERT',
1567 'CT2_OTH',
1568 'EXCISE_EXEMPT_CERT_OTH',
1569 'CT3'
1570 ) THEN
1571 --***************************************************************************************************
1572 --Calling the Function by Nagaraj.s for Enh#2415656............................
1573
1574
1575 open c_cess_amount(p_delivery_id);
1576 fetch c_cess_amount into ln_cess_amount;
1577 close c_cess_amount;
1578
1579 /* added by ssawant for bug 5989740 */
1580 open c_sh_cess_amount(p_delivery_id);
1581 fetch c_sh_cess_amount into ln_sh_cess_amount;
1582 close c_sh_cess_amount;
1583
1584 v_reg_type:= jai_om_wsh_processing_pkg.excise_balance_check(
1585 v_pref_rg23a ,
1586 v_pref_rg23c ,
1587 v_pref_pla ,
1588 NVL(v_ssi_unit_flag,'N') ,
1589 v_tot_excise_amt ,
1590 v_rg23a_balance ,
1591 v_rg23c_balance ,
1592 v_pla_balance ,
1593 v_basic_pla_balance ,
1594 v_additional_pla_balance ,
1595 v_other_pla_balance ,
1596 v_tot_basic_ed_amt ,
1597 v_tot_addl_ed_amt ,
1598 v_tot_oth_ed_amt ,
1599 v_export_oriented_unit ,
1600 v_register_code ,
1601 p_delivery_id ,
1602 v_organization_id ,
1603 v_location_id ,
1604 ln_cess_amount ,
1605 ln_sh_cess_amount ,/* added by ssawant for bug 5989740 */
1606 lv_process_flag ,
1607 lv_process_message
1608 );
1609
1610 fnd_file.put_line(fnd_file.log, p_delivery_id||', '||'18.1 The Value OF v_reg_type IS '|| v_reg_type);
1611 --**************************************************************************************************************************
1612 ELSE
1613 IF v_item_class NOT IN ('OTIN', 'OTEX') THEN
1614
1615 open c_cess_amount(p_delivery_id);
1616 fetch c_cess_amount into ln_cess_amount;
1617 close c_cess_amount;
1618
1619 /* added by ssawant for bug 5989740 */
1620 open c_sh_cess_amount(p_delivery_id);
1621 fetch c_sh_cess_amount into ln_sh_cess_amount;
1622 close c_sh_cess_amount;
1623
1624
1625 v_reg_type := jai_om_wsh_pkg.get_excise_register_with_bal(
1626 v_pref_rg23a ,
1627 v_pref_rg23c ,
1628 v_pref_pla ,
1629 NVL(v_ssi_unit_flag,'N') ,
1630 v_exempt_bal ,
1631 v_rg23a_balance ,
1632 v_rg23c_balance ,
1633 v_pla_balance ,
1634 v_basic_pla_balance ,
1635 v_additional_pla_balance ,
1636 v_other_pla_balance ,
1637 v_tot_basic_ed_amt ,
1638 v_tot_addl_ed_amt ,
1639 v_tot_oth_ed_amt ,
1640 v_export_oriented_unit ,
1641 v_register_code ,
1642 p_delivery_id ,
1643 v_organization_id ,
1644 v_location_id ,
1645 ln_cess_amount ,
1646 ln_sh_cess_amount ,/* added by ssawant for bug 5989740 */
1647 lv_process_flag ,
1648 lv_process_message
1649 );
1650 --Ends here......................................
1651 -------------------------------------------------------------------------------------------------------------------
1652 Fnd_File.PUT_LINE(Fnd_File.LOG, p_delivery_id||', '||'23 v_raise_exempt_flag = ' || v_raise_exempt_flag);
1653 v_basic_ed_amt := v_exempt_bal;
1654 v_tot_basic_ed_amt := NVL(v_tot_basic_ed_amt,0) + nvl(v_mod_basic_ed_amt,0) ; --+ v_exempt_bal; -- bug# 3207685
1655 v_remarks := 'Against Modvat Recovery'||'-'||v_excise_exempt_refno;
1656 --2001/11/01 Anuradha Parthasarathy
1657 END IF;
1658 END IF;
1659 END IF;
1660 ELSIF NVL(v_register_code,'N') IN ('BOND_REG') THEN --a999
1661 v_bond_tax_amount := NVL(v_tot_excise_amt,0) + NVL(v_bond_tax_amount,0);
1662 lv_statement_no := '20';
1663
1664 -- Following code modified by sriram.
1665 -- call to the jai_cmn_bond_register_pkg is being done which
1666 -- fetches the balances.
1667 -- bug # 3021588
1668 jai_cmn_bond_register_pkg.get_register_details
1669 (v_asst_register_id,
1670 v_register_balance,
1671 v_register_exp_date,
1672 v_lou_flag
1673 );
1674
1675 if nvl(v_register_exp_date,sysdate) < sysdate then
1676 Fnd_File.PUT_LINE(Fnd_File.LOG,'Error Occured - The Validity Period of the Bond Register ' || v_register_exp_date || ' has lapsed');
1677 RAISE_APPLICATION_ERROR(-20121,'The Validity Period of the Bond Register has lapsed');
1678 end if;
1679
1680
1681 Fnd_File.PUT_LINE(Fnd_File.LOG,'LOU FLAG is ' || NVL(v_lou_flag,'N'));
1682
1683 Fnd_File.PUT_LINE(Fnd_File.LOG, p_delivery_id||', '||'24 v_bond_tax_amount = ' || v_bond_tax_amount||' , v_register_balance = ' || v_register_balance );
1684
1685 IF ( (nvl(v_lou_flag,'N') = 'N') and (NVL(v_register_balance,0) < NVL(v_tot_excise_amt,0)) ) THEN
1686
1687 Fnd_File.PUT_LINE(Fnd_File.LOG,'Error Occured - Bonded Register Has Balance -> ' || TO_CHAR(v_register_balance)
1688 || ' ,which IS less than Excisable Amount -> ' || TO_CHAR(v_tot_excise_amt));
1689
1690 RAISE_APPLICATION_ERROR(-20120, 'Bonded Register Has Balance -> ' || TO_CHAR(v_register_balance)
1691 || ' ,which IS less than Excisable Amount -> ' || TO_CHAR(v_tot_excise_amt));
1692
1693
1694 END IF;
1695 END IF; ---a999
1696 END IF; ---b999
1697
1698 /*
1699 Changed by aiyer for the bug #3071342
1700 As the excise invoice generation should not be done in case Domestic Without Excise for trading and manufacturing
1701 organizations and hence modified the if statement to call excise invoice generation procedure only in case where
1702 v_register_code is in '23D_DOMESTIC_EXCISE' ,'23D_EXPORT_EXCISE' ,'DOMESTIC_EXCISE' ,
1703 'EXPORT_EXCISE','BOND_REG'
1704 This would ensure that the excise invoice generation would not happen in case where v_register_code in
1705 'DOM_WITHOUT_EXCISE','23D_DOM_WITHOUT_EXCISE' i.e Domestic Without Excise for Trading and manufacturing organizations.
1706 */
1707
1708 IF (
1709 (
1710 NVL(v_bonded_flag,'N') = 'Y' OR
1711 NVL(v_trading_flag,'N') = 'Y'
1712 ) AND
1713 NVL(v_excise_flag,'N') = 'Y' AND
1714 v_register_code IN(
1715 '23D_DOMESTIC_EXCISE' ,
1716 '23D_EXPORT_EXCISE' ,
1717 'DOMESTIC_EXCISE' ,
1718 'EXPORT_EXCISE' ,
1719 'BOND_REG'
1720 )
1721 )
1722 THEN
1723
1724 lv_statement_no := '21';
1725 OPEN register_code_meaning_cur(v_register_code,'JAI_REGISTER_TYPE'); /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
1726 FETCH register_code_meaning_cur INTO v_meaning;
1727 CLOSE register_code_meaning_cur;
1728 Fnd_File.PUT_LINE(Fnd_File.LOG, p_delivery_id||', '||'25 v_meaning = ' || v_meaning);
1729
1730 lv_statement_no := '22';
1731 OPEN fin_year_cur(v_organization_id);
1732 FETCH fin_year_cur INTO v_fin_year;
1733 CLOSE fin_year_cur;
1734
1735 /* excise invoice generation logic written in the procedure has been removed and instead a
1736 call to the excise invoice generation procedure has been made
1737 */
1738 /*Bug 5403048 Start*/
1739 OPEN c_excise_tax_rate(each_record.delivery_detail_id);
1740 FETCH c_excise_tax_rate INTO ln_total_tax_rate , ln_number_of_Taxes ;
1741 CLOSE c_excise_tax_rate;
1742
1743 IF NVL(ln_number_of_Taxes,0) > 0 THEN /*Bug 5403048 End*/
1744 -- procedure call to the excise invoice number generation procedure added by sriram bug # 2663211
1745
1746 fnd_file.put_line(fnd_file.log,'Calling the Excise Invoice Generation procedure with following parameters ');
1747 fnd_file.put_line(fnd_file.log,' Organization_id => ' || v_organization_id || ' Location_id => ' || v_location_id);
1748 fnd_file.put_line(fnd_file.log,'Order Type id => ' || v_order_type_id || 'Fin Year => ' || v_fin_year);
1749
1750
1751 jai_cmn_setup_pkg.generate_excise_invoice_no(v_organization_id,v_location_id,'O',v_order_type_id,v_fin_year,v_exc_invoice_no,ERRBUF);
1752
1753 fnd_file.put_line(fnd_file.log,'After Call to the procedure output values are following');
1754 fnd_file.put_line(fnd_file.log,'Excise Invoice Number generated => ' || v_exc_invoice_no);
1755
1756 IF ERRBUF IS NOT NULL THEN
1757 Fnd_File.PUT_LINE(Fnd_File.LOG,'Error Message in the excise invoice generation procedure is => ' || ERRBUF);
1758 retcode :=2; --to signal an error.
1759 v_ret_stat := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',ERRBUF);
1760 return;
1761 END IF;
1762 /* excise invoice has been generated for this delivery. hence set the flag to 'Y' */
1763 lv_exc_inv_gen_for_dlry_flag := 'Y'; --added by csahoo for bug#6077065
1764
1765 /*Bug 5403048 Start*/
1766 ELSE
1767 Fnd_File.PUT_LINE(Fnd_File.LOG, 'Excise Invoice Not Generated for delivery_detail_id : ' || each_record.delivery_detail_id || ' since there are no Excise Taxes ');
1768 END IF;
1769 /*Bug 5403048 End*/
1770 END IF; --d999 --1
1771 ELSE
1772 v_reg_type := v_old_register;
1773 v_exc_invoice_no := v_old_excise_invoice_no;
1774 Fnd_File.PUT_LINE(Fnd_File.LOG, p_delivery_id||', '||'41 v_reg_type= ' ||v_reg_type);
1775 Fnd_File.PUT_LINE(Fnd_File.LOG, p_delivery_id||', '||'41 v_exc_invoice_no= ' ||v_exc_invoice_no);
1776 END IF; --e999
1777 END IF; --f999
1778
1779 lv_statement_no := '29';
1780
1781 /* if excise invoice has not been generated for this delivery.
1782 then set the value to null so that amount RG entry will not happen. bgowrava for Bug#5554420 */
1783 if v_exc_invoice_no is null then
1784 v_reg_type := null;
1785 end if;
1786
1787 /*
1788 following if condition added by Sriram bug#2638797
1789 The reason is that , for trading organization and trading type of items ,
1790 the register to be hit is always RG23D , Before this fix , the register
1791 was not getting hit for a trading organization.
1792 */
1793 OPEN bonded_cur(v_organization_id, v_subinventory);
1794 FETCH bonded_cur INTO v_bonded_flag,v_trading_flag;
1795 CLOSE bonded_cur;
1796
1797
1798 SELECT NVL(Item_Trading_Flag,'N') INTO V_item_trading_flag
1799 FROM JAI_INV_ITM_SETUPS
1800 WHERE organization_id = v_organization_id
1801 AND inventory_item_id = v_inventory_item_id;
1802
1803 if NVL(V_item_trading_flag,'N') = 'Y' and NVL(v_trading_flag,'N') = 'Y' then
1804 v_reg_type := 'RG23D';
1805 end if;
1806
1807 /* ends here - additional by sriram - bug#2638797 */
1808
1809 /* Added by bgowrava for bug#5554420 */
1810 if NVL(ln_excise_tax_cnt,0) > 0
1811 and lv_exc_inv_gen_for_dlry_flag = 'Y'
1812 then
1813
1814 UPDATE
1815 JAI_OM_WSH_LINES_ALL
1816 SET
1817 excise_invoice_no = v_exc_invoice_no,
1818 --excise_invoice_date = TRUNC(v_actual_shipment_date), --replaced v_date for bug#7354983
1819 -- commented the above and added below for the bug 16353242
1820 excise_invoice_date = v_actual_shipment_date,
1821 register = DECODE(nvl(v_excise_exempt_type,'$$$'), 'CT3',NULL,v_reg_type), /*register should be updated as null incase of CT3 excise exemption Bug 3446362*/
1822 order_line_id = v_source_line_id_pick, /*2001/09/13 Jagdish */
1823 --added the next 3 columns for Bug #3849638, as these were not updated previously
1824 last_update_date = sysdate,
1825 last_updated_by = v_last_updated_by,
1826 last_update_login = v_last_update_login
1827 WHERE
1828 organization_id = v_organization_id AND
1829 location_id = v_location_id AND
1830 delivery_detail_id = each_record.delivery_detail_id;
1831 end if;
1832
1833 /*
1834 || Code added for the bug 4566054
1835 || Initialize the ln_del_det_totcess_amt variable to null;
1836 */
1837
1838 ln_del_det_totcess_amt := null;
1839 ln_del_det_totshcess_amt := null; --added by Bgowrava for forward porting bug#5989740
1840
1841 --Changed by Nagaraj.s on 21/05/2002 for Bug#2340750
1842 --Changes Done: In case of an order where Currency is changed apart from
1843 --Functional currency it is necessary that the excise register balances get
1844 --Updated with the Functional currency and not with the Currency as changed
1845 --in order. For this reason it has been taken care of that the excise amounts
1846 --which are hit will be first converted into Functional currency amount and then
1847 -- hits the registers.
1848 --The amounts which are taken care are: v_basic_ed_amt,v_oth_ed_amt,
1849 --v_addl_ed_amt, v_tot_basic_ed_amt .......................
1850
1851 Fnd_File.PUT_LINE(Fnd_File.LOG, p_delivery_id||', '||'42 v_item_class= ' ||v_item_class);
1852 IF v_register_code IS NOT NULL AND NVL(v_bonded_flag,'N') = 'Y' THEN --g999 --1
1853
1854 /*
1855 || Start of bug 4566054
1856 ||Code added by aiyer for the bug 4566054
1857 ||The cess amount is also being maintained in JAI_CMN_RG_I_TRXS table at a delivery_detail_level
1858 ||hence calculate the cess and pass it to the procedure jai_om_rg_pkg.ja_in_rg_i_entry with source as 'WSH'
1859 */
1860
1861 OPEN cur_get_del_det_cess_amt (cp_delivery_detail_id => each_record.delivery_detail_id);
1862 FETCH cur_get_del_det_cess_amt INTO ln_del_det_totcess_amt;
1863 CLOSE cur_get_del_det_cess_amt ;
1864 /* End of bug 4566054 */
1865
1866 /*Bgowrava for forward porting bug#5989740, start*/
1867 OPEN cur_get_del_det_sh_cess_amt (cp_delivery_detail_id => each_record.delivery_detail_id);
1868 FETCH cur_get_del_det_sh_cess_amt INTO ln_del_det_totshcess_amt;
1869 CLOSE cur_get_del_det_sh_cess_amt ;
1870 /*Bgowrava for forward porting bug#5989740, end*/
1871
1872 IF v_item_class IN ('RMIN','RMEX','CGEX','CGIN','FGIN','FGEX','CCIN','CCEX') THEN
1873 IF v_item_class IN ('FGIN','FGEX','CCIN','CCEX') THEN
1874 lv_statement_no := '30';
1875
1876 /*
1877 || Code modified by aiyer for the fix of the bug #3158282
1878 || The basic , additional and others amount in the JAI_CMN_RG_I_TRXS table should be
1879 || should be rounded off
1880 */
1881
1882 /*
1883 || Added by Ramananda. Start of bug#4543424
1884 */
1885 OPEN c_excise_tax_rate(each_record.delivery_detail_id);
1886 FETCH c_excise_tax_rate INTO ln_total_tax_rate , ln_number_of_Taxes ;
1887 CLOSE c_excise_tax_rate;
1888
1889 /*Start additions by mmurtuza for bug 15971482*/
1890 OPEN c_excise_base_tax_amt(each_record.delivery_detail_id);
1891 FETCH c_excise_base_tax_amt into ln_total_of_base_tax;
1892 CLOSE c_excise_base_tax_amt;
1893 /*End additions by mmurtuza for bug 15971482*/
1894
1895 if NVL(ln_number_of_Taxes,0) = 0 then
1896 ln_number_of_Taxes := 1;
1897 end if;
1898
1899 v_tax_rate := ln_total_tax_rate / ln_number_of_Taxes;
1900 /*
1901 || Added by Ramananda. End of bug#4543424
1902 */
1903
1904 /*
1905 || Start Additions by ssumaith - bug# 4185392
1906 */
1907
1908 /*Start commenting by mmurtuza for bug 15971482*/
1909 /*if nvl(v_assessable_value,0) <> 0 and nvl(v_shp_qty,0) <> 0 then
1910 v_tax_rate := round(nvl(v_basic_ed_amt,0) / (v_assessable_value * v_shp_qty) * 100,2);
1911 end if;*/
1912 /*End commenting by mmurtuza for bug 15971482*/
1913
1914 /*
1915 || Ends here additions by ssumaith - bug# 4185392
1916 */
1917 jai_om_rg_pkg.ja_in_rg_i_entry(
1918 v_fin_year ,
1919 v_organization_id ,
1920 v_location_id ,
1921 v_inventory_item_id ,
1922 33 ,
1923 v_actual_shipment_date , --replaced v_date for bug#7354983
1924 'I' ,
1925 each_record.delivery_detail_id ,
1926 v_shp_qty ,
1927 v_excise_amount * v_converted_rate , /* multiplied with v_converted_rate for the bug 16014905 */ /*Removed round by mmurtuza for bug 16534065*/
1928 v_uom_code ,
1929 v_exc_invoice_no ,
1930 TRUNC(v_actual_shipment_date) , --replaced v_date for bug#7354983
1931 v_reg_type ,
1932 v_basic_ed_amt * v_converted_rate ,/*Removed round by mmurtuza for bug 16534065*/
1933 v_addl_ed_amt * v_converted_rate ,/*Removed round by mmurtuza for bug 16534065*/
1934 v_oth_ed_amt * v_converted_rate ,/*Removed round by mmurtuza for bug 16534065*/
1935 v_tax_rate ,
1936 v_customer_id ,
1937 v_ship_to_org_id ,
1938 v_register_code , /* Bug 4562791. Added by Lakshmi Gopalsami Commented creation_date and last_update_date and passing v_date v_creation_date ,*/
1939 v_date ,
1940 v_created_by ,
1941 --v_last_update_date ,
1942 v_date ,
1943 v_last_updated_by ,
1944 v_last_update_login ,
1945 --v_assessable_value , /*Commented and added below by mmurtuza for bug 15971482*/
1946 ln_total_of_base_tax , /* multiplied with v_converted_rate for the bug 16014905 */
1947 /* removed the v_converted_rate which is multiplying with ln_total_of_base_tax for bug 16197185 */
1948 ln_del_det_totcess_amt , /*Parameters p_cess_amt and p_source added by aiyer for the bug 4566054 */
1949 ln_del_det_totshcess_amt , --Bgowrava for forward porting bug#5989740
1950 jai_constants.source_wsh
1951 );
1952
1953 lv_statement_no := '31';
1954 SELECT JAI_CMN_RG_I_TRXS_S.CURRVAL INTO v_part_i_register_id FROM dual;
1955 Fnd_File.PUT_LINE(Fnd_File.LOG, p_delivery_id||', 42.1 ja_in_rg_I_entry is made register_id -> ' || v_part_i_register_id );
1956 ELSIF v_item_class IN ('CGEX','CGIN') THEN
1957 v_qty_reg_type := 'C';
1958
1959 ELSIF v_item_class IN ('RMIN','RMEX') THEN
1960 v_qty_reg_type := 'A';
1961 END IF;
1962
1963 IF v_item_class IN ('RMIN','RMEX','CGIN','CGEX') THEN
1964 lv_statement_no := '32';
1965
1966 /*
1967 Code modified by aiyer for the fix of the bug #3158282.
1968 The basic , additional and others amount in the JAI_CMN_RG_23AC_I_TRXS table should be
1969 should be rounded off
1970 */
1971
1972 jai_om_rg_pkg.ja_in_rg23_part_i_entry(
1973 v_qty_reg_type ,
1974 v_fin_year ,
1975 v_organization_id ,
1976 v_location_id ,
1977 v_inventory_item_id ,
1978 33 ,
1979 v_actual_shipment_date ,--replaced v_date for bug#7354983
1980 'I' ,
1981 v_shp_qty ,
1982 v_uom_code ,
1983 v_exc_invoice_no ,
1984 TRUNC(v_actual_shipment_date) ,--replaced v_date for bug#7354983
1985 v_basic_ed_amt* v_converted_rate ,/*Removed round by mmurtuza for bug 16534065*/
1986 v_addl_ed_amt* v_converted_rate ,/*Removed round by mmurtuza for bug 16534065*/
1987 v_oth_ed_amt* v_converted_rate ,/*Removed round by mmurtuza for bug 16534065*/
1988 v_customer_id ,
1989 v_ship_to_org_id ,
1990 each_record.delivery_detail_id ,
1991 v_actual_shipment_date ,--replaced v_date for bug#7354983
1992 v_register_code ,
1993 /* Bug 4562791. Added by Lakshmi Gopalsami
1994 Commented creation_date and last_update_date
1995 and passing v_date
1996 v_creation_date ,*/
1997 v_date ,
1998 v_created_by ,
1999 --v_last_update_date ,
2000 v_date ,
2001 v_last_updated_by ,
2002 v_last_update_login
2003 );
2004 lv_statement_no := '33';
2005 SELECT JAI_CMN_RG_23AC_I_TRXS_S.CURRVAL INTO v_part_i_register_id FROM dual;
2006 Fnd_File.PUT_LINE(Fnd_File.LOG, p_delivery_id||', 42.2 ja_in_rg23_part_I_entry is made, register_id -> ' || v_part_i_register_id );
2007 END IF;
2008 END IF;
2009 -- Start of Bug 3446362
2010 /*
2011 Added by aiyer for the bug 3446362.
2012 Check whether rows exist in the JAI_OM_WSH_LINES_ALL tables with excise exempt type other than CT3.
2013 Even records with null values are fine.
2014 If the count of rows with CT3 excise_exemption_type is greater than zero meaning
2015 some records are present which do not have CT3 excise exemption, then only in such a scenario let the
2016 amount registers be hit.
2017 */
2018
2019 OPEN c_ct3_flag_exists;
2020 FETCH c_ct3_flag_exists INTO ln_count;
2021 CLOSE c_ct3_flag_exists;
2022
2023 fnd_file.put_line( fnd_file.log, p_delivery_id||', '||'44 v_old_delivery_id = ' ||v_old_delivery_id
2024 ||', 45 v_old_register = ' ||v_old_register||', 46 v_reg_type = ' ||v_reg_type
2025 ||', 47 value of ln_count '||ln_count
2026 );
2027
2028 IF (NVL(v_old_delivery_id,0) <> NVL(each_record.delivery_id,-1)
2029 or v_old_register IS NULL /* added by bgowrava for forward porting Bug#5554420 to hit Amt register + avoid hitting the amount register multiple times*/
2030 )
2031 AND ln_count > 0
2032 AND v_exc_invoice_no is not null /* added by bgowrava for forward porting Bug#5554420 to make sure that, amt register will be hit only if excise invoice number is not null */
2033 THEN
2034 -- End of Bug 3446362
2035
2036 -- IF v_old_register IS NULL THEN /* commented here bgowrava for forward porting Bug#5554420 and moved to above IF condition*/
2037 IF v_reg_type IN ('RG23A', 'RG23C') THEN
2038 IF v_reg_type = 'RG23A' THEN
2039 v_rg_type := 'A';
2040 ELSE
2041 v_rg_type := 'C';
2042 END IF;
2043 lv_statement_no := '34';
2044 /*
2045 Code modified by aiyer for the fix of the bug #3158282.
2046 Shifted the conversion to INR currency and rounding off at the cursor level itself.
2047 refer cursor get_total_excise_amt.
2048 */
2049
2050 jai_om_rg_pkg.ja_in_rg23_part_II_entry(
2051 v_register_code ,
2052 v_rg_type ,
2053 v_fin_year ,
2054 v_organization_id ,
2055 v_location_id ,
2056 v_inventory_item_id ,
2057 33 ,
2058 v_actual_shipment_date ,--replaced v_date for bug#7354983
2059 v_part_i_register_id ,
2060 v_exc_invoice_no ,
2061 TRUNC(v_actual_shipment_date ) ,--replaced v_date for bug#7354983
2062 v_tot_basic_ed_amt , --2001/09/24 Vijay
2063 v_tot_addl_ed_amt , --2001/09/24 Vijay
2064 v_tot_oth_ed_amt , --2001/09/24 Vijay
2065 v_customer_id ,
2066 v_ship_to_org_id ,
2067 v_source_name ,
2068 v_category_name ,
2069 /* Bug 4562791. Added by Lakshmi Gopalsami
2070 Commented creation_date and last_update_date
2071 and passing v_date
2072 v_creation_date ,*/
2073 v_date ,
2074 v_created_by ,
2075 --v_last_update_date ,
2076 v_date ,
2077 v_last_updated_by ,
2078 v_last_update_login ,
2079 each_record.delivery_detail_id ,
2080 v_excise_exempt_type ,
2081 v_remarks ,
2082 v_ref_10 , -- bug # 2769440
2083 v_ref_23 , -- bug # 2769440
2084 v_ref_24 , -- bug # 2769440
2085 v_ref_25 , -- bug # 2769440
2086 v_ref_26 -- bug # 2769440
2087 );
2088
2089 fnd_file.put_line(fnd_file.log, p_delivery_id||', 46.1 ja_in_rg23_part_II_entry is made, v_exc_invoice_no -> ' || v_exc_invoice_no );
2090 ELSIF v_reg_type IN ('PLA') THEN
2091 lv_statement_no := '35';
2092
2093 /*
2094 Code modified by aiyer for the fix of the bug #3158282.
2095 Shifted the conversion to INR currency and rounding off at the cursor level itself.
2096 refer cursor get_total_excise_amt.
2097 */
2098
2099 jai_om_rg_pkg.ja_in_pla_entry(
2100 v_organization_id ,
2101 v_location_id ,
2102 v_inventory_item_id ,
2103 v_fin_year ,
2104 33, each_record.delivery_detail_id ,
2105 v_actual_shipment_date ,--replaced v_date for bug#7354983
2106 v_exc_invoice_no ,
2107 trunc(v_actual_shipment_date) ,--replaced v_date for bug#7354983
2108 v_tot_basic_ed_amt , --2001/09/24 Vijay
2109 v_tot_addl_ed_amt , --2001/09/24 Vijay
2110 v_tot_oth_ed_amt , --2001/09/24 Vijay
2111 v_customer_id ,
2112 v_ship_to_org_id ,
2113 v_source_name ,
2114 v_category_name ,
2115 /* Bug 4562791. Added by Lakshmi Gopalsami
2116 Commented creation_date and last_update_date
2117 and passing v_date
2118 v_creation_date ,*/
2119 v_date ,
2120 v_created_by ,
2121 --v_last_update_date ,
2122 v_date ,
2123 v_last_updated_by ,
2124 v_last_update_login ,
2125 v_ref_10 , -- bug # 2769440
2126 v_ref_23 , -- bug # 2769440
2127 v_ref_24 , -- bug # 2769440
2128 v_ref_25 , -- bug # 2769440
2129 v_ref_26 -- bug # 2769440
2130 );
2131 fnd_file.put_line(fnd_file.log, p_delivery_id||', 46.2 ja_in_pla_entry is made, v_exc_invoice_no -> ' || v_exc_invoice_no );
2132 END IF;
2133 -- END IF; /* commented by bgowrava for forward porting Bug#5554420 */
2134 END IF;
2135
2136 IF v_item_class IN ('FGIN','FGEX','CCIN','CCEX') and nvl(v_excise_exempt_type,'$$$') not in ('CT3') THEN -- sriram - bug# 3368475
2137
2138 SELECT JAI_CMN_RG_I_TRXS_S.CURRVAL INTO v_rg23_part_i_no FROM dual;
2139 IF v_reg_type IN( 'RG23A','RG23C') THEN
2140 lv_statement_no := '36';
2141 SELECT JAI_CMN_RG_23AC_II_TRXS_S.CURRVAL INTO v_rg23_part_ii_no FROM dual;
2142 lv_statement_no := '37';
2143 UPDATE
2144 JAI_CMN_RG_I_TRXS
2145 SET
2146 register_id_part_ii = v_rg23_part_ii_no,
2147 charge_account_id = (SELECT
2148 charge_account_id
2149 FROM
2150 JAI_CMN_RG_23AC_II_TRXS
2151 WHERE
2152 register_id = v_rg23_part_ii_no
2153 )
2154 WHERE
2155 register_id = v_rg23_part_i_no;
2156 ELSIF v_reg_type IN( 'PLA') THEN
2157 lv_statement_no := '38';
2158 SELECT JAI_CMN_RG_PLA_TRXS_S1.CURRVAL INTO v_pla_register_no FROM dual;
2159 lv_statement_no := '39';
2160 UPDATE
2161 JAI_CMN_RG_I_TRXS
2162 SET
2163 register_id_part_ii = v_pla_register_no,
2164 charge_account_id = (
2165 SELECT CHARGE_ACCOUNT_ID
2166 FROM
2167 JAI_CMN_RG_PLA_TRXS
2168 WHERE
2169 register_id = v_pla_register_no
2170 )
2171 WHERE
2172 register_id = v_rg23_part_i_no;
2173 END IF;
2174
2175 ELSIF v_item_class IN ('RMIN','RMEX','CGIN','CGEX') and nvl(v_excise_exempt_type,'$$$') not in ('CT3') THEN -- sriram - bug# 3368475
2176 lv_statement_no := '40';
2177 SELECT JAI_CMN_RG_23AC_I_TRXS_S.CURRVAL INTO v_rg23_part_i_no FROM dual;
2178 IF v_reg_type IN( 'RG23A','RG23C')
2179 THEN
2180 lv_statement_no := '41';
2181 SELECT JAI_CMN_RG_23AC_II_TRXS_S.CURRVAL INTO v_rg23_part_ii_no FROM dual;
2182 lv_statement_no := '42';
2183 UPDATE JAI_CMN_RG_23AC_I_TRXS
2184 SET REGISTER_ID_PART_II = v_rg23_part_ii_no,
2185 CHARGE_ACCOUNT_ID = (SELECT CHARGE_ACCOUNT_ID
2186 FROM JAI_CMN_RG_23AC_II_TRXS
2187 WHERE register_id = v_rg23_part_ii_no)
2188 WHERE register_id = v_rg23_part_i_no;
2189 ELSIF v_reg_type IN( 'PLA') THEN
2190 lv_statement_no := '43';
2191 SELECT JAI_CMN_RG_PLA_TRXS_S1.CURRVAL INTO v_pla_register_no FROM dual;
2192 lv_statement_no := '44';
2193 UPDATE
2194 JAI_CMN_RG_23AC_I_TRXS
2195 SET
2196 REGISTER_ID_PART_II = v_pla_register_no,
2197 charge_account_id = (
2198 SELECT
2199 charge_account_id
2200 FROM JAI_CMN_RG_PLA_TRXS
2201 WHERE
2202 register_id = v_pla_register_no
2203 )
2204 WHERE register_id = v_rg23_part_i_no;
2205 END IF;
2206 END IF;
2207
2208 fnd_file.put_line(fnd_file.log, p_delivery_id||', 46.3 v_rg23_part_ii_no -> ' || v_rg23_part_ii_no||', v_pla_register_no -> ' || v_pla_register_no );
2209 --2001/04/09 Manohar Mishra
2210 --Changed the cases
2211 IF NVL(v_register_code,'N') IN ('BOND_REG') AND
2212 NVL(v_old_delivery_id,0) <> NVL(each_record.delivery_id,-1) -- bug2389773 cbabu 24/02/2002. new check added
2213 THEN
2214 lv_statement_no := '45';
2215
2216 /*
2217 Code modified by aiyer for the fix of the bug #3158282.
2218 Shifted the conversion to INR currency and rounding off at the cursor level itself.
2219 refer cursor get_total_excise_amt.
2220 */
2221
2222 jai_om_rg_pkg.ja_in_register_txn_entry(
2223 v_organization_id ,
2224 v_location_id ,
2225 v_exc_invoice_no ,
2226 'BOND SALES' ,
2227 'Y' ,
2228 each_record.delivery_id , /* changed from order header id to delivery id for CESS bug - SSUMAITH - bug#4136981 */
2229 v_tot_excise_amt , --Jagdish 15-Jun-01
2230 'BOND_REG' ,
2231 /* Bug 4562791. Added by Lakshmi Gopalsami
2232 Commented creation_date and last_update_date
2233 and passing v_date
2234 v_creation_date ,*/
2235 v_date ,
2236 v_created_by ,
2237 --v_last_update_date ,
2238 v_Date ,
2239 v_last_updated_by ,
2240 v_last_update_login
2241 );
2242 END IF;
2243 END IF; --g999 --1
2244
2245 -- Altered by Arun For Incorporating TRADING CODE ON 31 OCT 2000 at 1:45
2246 lv_statement_no := '46';
2247 OPEN Trading_register_code_cur(v_organization_id, v_location_id, each_record.delivery_detail_id, v_order_type_id);
2248 FETCH Trading_register_code_cur INTO v_Trad_register_code;
2249 CLOSE Trading_register_code_cur;
2250
2251 IF v_Trad_register_code IS NOT NULL THEN --added by Gaurav.
2252 v_register_code := v_Trad_register_code;
2253 END IF;
2254
2255 lv_statement_no := '47';
2256 SELECT NVL(Item_Trading_Flag,'N') INTO V_item_trading_flag
2257 FROM JAI_INV_ITM_SETUPS
2258 WHERE organization_id = v_organization_id
2259 AND inventory_item_id = v_inventory_item_id;
2260
2261 lv_statement_no := '48';
2262 OPEN bonded_cur(v_organization_id, v_subinventory);
2263 FETCH bonded_cur INTO v_bonded_flag, v_trading_flag;
2264 CLOSE bonded_cur;
2265
2266 Fnd_File.PUT_LINE(Fnd_File.LOG, p_delivery_id||', 48.1 v_register_code -> ' || v_register_code
2267 ||', v_trading_flag -> ' || v_trading_flag ||', v_item_trading_flag -> ' || v_item_trading_flag
2268 );
2269
2270 /*
2271 Code modified by aiyer for the bug 3032569
2272 The Excise Invoice number is being generated for non excisable RG23D transactions.
2273 This needs to be stopped for Trading Domestic Without Excise and Export Without excise scenario's.
2274 Modified the IF statment to remove the check that the trading register_codes should be in
2275 '23D_DOM_WITHOUT_EXCISE' and '23D_EXPORT_WITHOUT_EXCISE'.
2276 */
2277
2278 -- Start of Bug #3032569
2279 IF v_register_code IN(
2280 '23D_DOMESTIC_EXCISE' ,
2281 '23D_EXPORT_EXCISE'
2282 ) THEN --y999
2283
2284
2285 IF NVL(v_trading_flag,'N') = 'Y' THEN
2286 IF NVL(V_item_trading_flag,'N') = 'Y' THEN
2287
2288 -- Start, cbabu for Bug# 2736191
2289 lv_statement_no := '55';
2290 OPEN header_id (each_record.delivery_detail_id) ;
2291 FETCH header_id INTO v_source_line_id, v_source_header_id;
2292 CLOSE header_id;
2293
2294 lv_statement_no := '56';
2295 OPEN matched_receipt_cur(each_record.delivery_detail_id);
2296 FETCH matched_receipt_cur INTO v_receipt_id, v_quantity_applied;
2297 CLOSE matched_receipt_cur;
2298
2299 lv_statement_no := '57';
2300 OPEN ship_bill_cur(v_source_header_id);
2301 FETCH ship_bill_cur INTO v_invoice_to_site_use_id, v_ship_to_site_use_id;
2302 CLOSE ship_bill_cur;
2303 -- End, cbabu for Bug# 2736191
2304
2305 lv_statement_no := '53';
2306 UPDATE JAI_CMN_MATCH_RECEIPTS
2307 SET ship_status='CLOSED'
2308 WHERE ref_line_id = each_record.delivery_detail_id
2309 AND ORDER_INVOICE = 'O'; -- cbabu for Bug# 2736191
2310
2311 -- cbabu for Bug# 2736191
2312 v_proportionate_rpu := 0;
2313 v_proportionate_edr := 0;
2314 v_total_quantity_applied := 0;
2315 v_total_base_duty_amount := 0;
2316 --start additions for bug#9839132
2317 v_tot_duty_amt := 0;
2318 v_total_rate := 0;
2319 v_tot_cvd_amt := 0;
2320 v_tot_addl_cvd_amt := 0;
2321 v_tot_basic_ed_amt := 0;
2322 v_tot_addl_ed_amt := 0;
2323 v_tot_oth_ed_amt := 0;
2324 --end additions for bug#9839132
2325
2326
2327 FOR match_rec IN matched_receipt_cur(each_record.delivery_detail_id) LOOP -- cbabu for Bug# 2736191
2328
2329 Fnd_File.PUT_LINE(Fnd_File.LOG, p_delivery_id||', 49.1 quantity_applied -> ' || match_rec.quantity_applied
2330 ||', receipt_id -> ' || match_rec.receipt_id
2331 );
2332
2333 lv_statement_no := '58';
2334 OPEN qty_to_adjust_cur(match_rec.receipt_id);
2335 FETCH qty_to_adjust_cur INTO v_qty_to_adjust, v_excise_duty_rate, v_rate_per_unit,v_qnty_received; --modified for bug#5735284
2336 CLOSE qty_to_adjust_cur ;
2337
2338 v_total_rate := v_total_rate + (v_rate_per_unit * match_rec.quantity_applied);
2339 /* Added the check for zero excise duty rate by JMEENA, Bug# 7043292 */
2340 IF NVL(v_excise_duty_rate,0) <> 0 THEN
2341 v_total_base_duty_amount := v_total_base_duty_amount +
2342 ((v_rate_per_unit/v_excise_duty_rate) * match_rec.quantity_applied );
2343 END IF;
2344 --End bug 7043292
2345 v_total_quantity_applied := v_total_quantity_applied + match_rec.quantity_applied;
2346
2347 fnd_file.put_line(fnd_file.log, p_delivery_id||', 49.2 v_register_id -> ' || v_register_id
2348 ||', v_exc_invoice_no -> ' || v_exc_invoice_no||', v_receipt_id -> ' || v_receipt_id
2349 ||', v_quantity_applied -> ' || v_quantity_applied||', v_qty_to_adjust -> ' || v_qty_to_adjust
2350 ||', v_excise_duty_rate -> ' || v_excise_duty_rate||', v_rate_per_unit -> ' || v_rate_per_unit
2351 ||', v_source_line_id -> ' || v_source_line_id||', v_source_header_id -> ' || v_source_header_id
2352 );
2353 lv_statement_no := '60';
2354
2355 jai_cmn_rg_23d_trxs_pkg.upd_receipt_qty_matched(match_rec.receipt_id, match_rec.quantity_applied, v_qty_to_adjust);
2356 -- END IF; --2002/02/20 Vijay
2357 --added for bug#5735284 ,start
2358 OPEN get_duty_amt_cur(match_rec.receipt_id);
2359 FETCH get_duty_amt_cur INTO rec_get_duty_amt;
2360 CLOSE get_duty_amt_cur ;
2361 v_tot_duty_amt := v_tot_duty_amt + round((nvl(rec_get_duty_amt.duty_amount,0)* match_rec.quantity_applied/v_qnty_received),2);
2362 v_tot_cvd_amt := v_tot_cvd_amt + round((nvl(rec_get_duty_amt.cvd,0)* match_rec.quantity_applied/v_qnty_received),2);
2363 v_tot_addl_cvd_amt := v_tot_addl_cvd_amt + round((nvl(rec_get_duty_amt.additional_cvd,0)* match_rec.quantity_applied/v_qnty_received),2);
2364 v_tot_basic_ed_amt := v_tot_basic_ed_amt + round((nvl(rec_get_duty_amt.basic_ed,0)* match_rec.quantity_applied/v_qnty_received),2);
2365 v_tot_addl_ed_amt := v_tot_addl_ed_amt + round((nvl(rec_get_duty_amt.additional_ed,0)* match_rec.quantity_applied/v_qnty_received),2);
2366 v_tot_oth_ed_amt := v_tot_oth_ed_amt + round((nvl(rec_get_duty_amt.other_ed,0)* match_rec.quantity_applied/v_qnty_received),2);
2367 --added for bug#5735284 ,end
2368
2369 END LOOP;
2370
2371 -- Start, cbabu for Bug# 2736191
2372 /*
2373 || bug#7043292, JMEENA
2374 || if v_total_base_duty_amount is zero then divide by zero exception is raised.
2375 || To avoid this exception a check is made here so that if v_tota_base_duty_amount
2376 || is zero then v_proportionate_edr is not calculated and if v_total_quantity_applied is zero then v_proportionate_rpu is not calculated.
2377 */
2378
2379 IF v_total_base_duty_amount <> 0 THEN
2380 v_proportionate_edr := v_total_rate / v_total_base_duty_amount;
2381 END IF;
2382
2383 IF v_total_quantity_applied <> 0 THEN
2384 v_proportionate_rpu := v_total_rate / v_total_quantity_applied;
2385 END IF;
2386
2387 --END 7043292
2388 lv_statement_no := '54';
2389 SELECT JAI_CMN_RG_23D_TRXS_S.NEXTVAL INTO v_register_id FROM Dual;
2390
2391 lv_statement_no := '59';
2392
2393 jai_om_rg_pkg.Ja_In_Rg23d_Entry(
2394 v_register_id ,
2395 v_organization_id ,
2396 v_location_id ,
2397 v_fin_year ,
2398 'I' ,
2399 v_inventory_item_id ,
2400 each_record.delivery_detail_id ,
2401 v_uom_code ,
2402 v_uom_code ,
2403 v_customer_id ,
2404 v_invoice_to_site_use_id ,
2405 v_ship_to_site_use_id ,
2406 v_total_quantity_applied ,
2407 v_register_code ,
2408 v_proportionate_rpu ,
2409 v_proportionate_edr ,
2410 -- v_excise_amount * v_converted_rate , commented for bug#9839132
2411 v_tot_duty_amt,--added for bug#9839132
2412
2413 NULL ,
2414 v_source_name ,
2415 v_category_name ,
2416 NULL ,
2417 NULL ,
2418 /* Bug 4562791. Added by Lakshmi Gopalsami
2419 Commented creation_date and last_update_date
2420 and passing v_date
2421 v_creation_date ,*/
2422 v_date ,
2423 v_created_by ,
2424 --v_last_update_date ,
2425 v_date ,
2426 v_last_update_login ,
2427 v_last_updated_by ,
2428 --added for bug#6199766 ,start
2429 v_tot_basic_ed_amt ,
2430 v_tot_addl_ed_amt ,
2431 v_tot_oth_ed_amt ,
2432 --added for bug#6199766 ,end
2433 v_exc_invoice_no ,
2434 TRUNC(v_actual_shipment_date) ,--replaced v_date for bug#7354983
2435 v_ref_10 , -- bug # 2769440
2436 v_ref_23 , -- bug # 2769440
2437 v_ref_24 , -- bug # 2769440
2438 v_ref_25 , -- bug # 2769440
2439 v_ref_26 , -- bug # 2769440
2440 v_tot_cvd_amt,
2441 - v_tot_addl_cvd_amt /*Added by nprashar for bug # 5735284 */
2442 );
2443
2444
2445
2446 -- End , cbabu for Bug# 2736191
2447
2448 END IF;
2449 END IF;
2450 END IF; --y999
2451 -- cbabu for Bug# 2803409
2452 -- This statement got deleted with Bug# 2736191
2453 lv_statement_no := '61';
2454 DELETE JAI_OM_OE_GEN_TAXINV_T WHERE delivery_detail_id = each_record.delivery_detail_id;
2455
2456 v_bonded_flag := 'N' ;
2457 v_trading_flag := 'N' ;
2458 v_rg23a_balance := 0 ;
2459 v_rg23c_balance := 0 ;
2460 v_pla_balance := 0 ;
2461
2462 IF NVL(v_old_delivery_id,0) <> NVL(each_record.delivery_id,-1) THEN
2463 v_old_delivery_id := each_record.delivery_id;
2464 END IF;
2465 END IF; --z999
2466 v_no_records_fetched := v_no_records_fetched + 1;
2467
2468 END LOOP;
2469 --added by cbabu 27/03/02
2470 lv_statement_no := '62';
2471 --COMMIT;
2472 fnd_file.put_line( fnd_file.log,
2473 p_delivery_id||', '||'61 v_no_records_fetched = '||v_no_records_fetched
2474 ||', END OF deliver_id = '||p_delivery_id
2475 );
2476
2477 EXCEPTION
2478 WHEN OTHERS THEN
2479 DECLARE
2480 ln_created_by NUMBER ; /* added by aiyer for the bug 4765347*/
2481 ln_last_update_login NUMBER ; /* added by aiyer for the bug 4765347*/
2482
2483 BEGIN
2484 /*
2485 ||added by aiyer for the bug 4765347
2486 */
2487 ln_created_by := fnd_global.user_id ;
2488 ln_last_update_login := fnd_global.conc_login_id ;
2489
2490 ROLLBACK;
2491 lv_error_mesg := SQLERRM;
2492 ERRBUF := lv_error_mesg;
2493 RETCODE := '2';
2494 Fnd_File.PUT_LINE(Fnd_File.LOG, p_delivery_id||' Error occured = ' || lv_error_mesg);
2495 INSERT INTO JAI_CMN_ERRORS_T (
2496 APPLICATION_SOURCE ,
2497 error_message ,
2498 additional_error_mesg ,
2499 creation_date ,
2500 created_by ,
2501 last_updated_by ,
2502 last_update_date ,
2503 last_update_login
2504 )
2505 VALUES (
2506 lv_procedure_name ,
2507 lv_error_mesg ,
2508 'EXCEPTION captured BY WHEN OTHERS IN the PROCEDURE. BLOCK No/STATEMENT No:' || lv_block_no || '/' || lv_statement_no,
2509 v_date ,
2510 ln_created_by ,
2511 ln_created_by , /* added by aiyer for the bug 4765347*/
2512 v_date ,/* added by aiyer for the bug 4765347*/
2513 ln_last_update_login /* added by aiyer for the bug 4765347*/
2514 );
2515 COMMIT;
2516 END ;
2517 END process_delivery;
2518
2519
2520 FUNCTION get_excise_register_with_bal
2521 (
2522 p_pref_rg23a NUMBER ,
2523 p_pref_rg23c NUMBER ,
2524 p_pref_pla in NUMBER ,
2525 p_ssi_unit_flag VARCHAR2 ,
2526 p_exempt_amt NUMBER ,
2527 p_rg23a_balance NUMBER ,
2528 p_rg23c_balance NUMBER ,
2529 p_pla_balance NUMBER ,
2530 p_basic_pla_balance NUMBER ,
2531 p_additional_pla_balance NUMBER ,
2532 p_other_pla_balance NUMBER ,
2533 p_basic_excise_duty_amount NUMBER ,
2534 p_add_excise_duty_amount NUMBER ,
2535 p_oth_excise_duty_amount NUMBER ,
2536 p_export_oriented_unit VARCHAR2 ,
2537 p_register_code VARCHAR2 ,
2538 p_delivery_id NUMBER ,
2539 p_organization_id NUMBER ,
2540 p_location_id NUMBER ,
2541 p_cess_amount NUMBER ,
2542 p_sh_cess_amount NUMBER, /* added by ssawant for bug 5989740 */
2543 p_process_flag OUT NOCOPY VARCHAR2 ,
2544 p_process_msg OUT NOCOPY VARCHAR2
2545 )
2546 RETURN VARCHAR2
2547 is
2548
2549 --Variable Declaration starts here................
2550 v_pref_rg23a JAI_CMN_INVENTORY_ORGS.pref_rg23a%type;
2551 v_pref_rg23c JAI_CMN_INVENTORY_ORGS.pref_rg23c%type;
2552 v_pref_pla JAI_CMN_INVENTORY_ORGS.pref_pla%type;
2553 v_ssi_unit_flag JAI_CMN_INVENTORY_ORGS.ssi_unit_flag%type;
2554 v_reg_type varchar2(10);
2555 v_exempt_amt number;
2556 v_rg23a_balance number;
2557 v_rg23c_balance number;
2558 v_pla_balance number;
2559 v_output number;
2560 v_basic_pla_balance number;
2561 v_additional_pla_balance number;
2562 v_other_pla_balance number;
2563 v_basic_excise_duty_amount number;
2564 v_add_excise_duty_amount number;
2565 v_oth_excise_duty_amount number;
2566 v_export_oriented_unit JAI_CMN_INVENTORY_ORGS.export_oriented_unit%type;
2567 v_register_code JAI_OM_OE_BOND_REG_HDRS.register_code%type;
2568 v_debug_flag varchar2(1); -- := 'N'; --Ramananda for File.Sql.35
2569 v_utl_location VARCHAR2(512); --For Log file.
2570 v_myfilehandle UTL_FILE.FILE_TYPE; -- This is for File handling
2571 v_trip_id wsh_delivery_trips_v.trip_id%type;
2572 lv_process_flag VARCHAR2(2);
2573 lv_process_message VARCHAR2(1996);
2574 lv_register_type VARCHAR2(5);
2575 lv_rg23a_cess_avlbl VARCHAR2(10);
2576 lv_rg23c_cess_avlbl VARCHAR2(10);
2577 lv_pla_cess_avlbl VARCHAR2(10);
2578 lv_rg23a_sh_cess_avlbl VARCHAR2(10); /* added by ssawant for bug 5989740 */
2579 lv_rg23c_sh_cess_avlbl VARCHAR2(10); /* added by ssawant for bug 5989740 */
2580 lv_pla_sh_cess_avlbl VARCHAR2(10); /* added by ssawant for bug 5989740 */
2581
2582 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_om_wsh_pkg.get_excise_register_with_bal';
2583
2584
2585 --Variable Declaration Ends here......................
2586
2587 BEGIN
2588 /*------------------------------------------------------------------------------------------
2589 FILENAME: get_excise_register_with_bal_F.sql
2590 CHANGE HISTORY:
2591
2592 1. 2002/07/03 Nagaraj.s - For Enh#2415656.
2593 Function created for checking the register preferences in case of an Non-
2594 Export Oriented Unit and in case of an Export Oriented Unit, the component
2595 balances are checked and if balances does not exist, the function will raise an
2596 application error and if balances exists, then the function will return the register
2597 type. This Function is called from ja_in_wsh_dlry_dtls_au_trg.sql and jai_om_wsh_pkg.process_delivery.sql.
2598 This Function is a prerequisite patch with the above mentioned trigger and procedure.
2599 Also Alter table scripts with this patch should be available before sending this patch.
2600 Otherwise the patch would certainly fail.
2601
2602 2. 2003/03/13 Sriram . Bug # 2796717
2603 Cenvat Reversal Accounting was not happening correctly.The reason was that if PLA is the
2604 preference , the call to the ja_in_pla_entry procedure of the jai_om_rg_pkg is being called.
2605 The PLA Entry does not consider different accounting for Excise Exempted transaction.
2606 This is the desired functionality . Hence added code to see that , if balance is available in
2607 the RG23A register , then hit the register and the accounting happens correctly for
2608 excise exempted transactions.If balances are not available for RG23A register , then
2609 take the normal code path , if PLA is hit , then accounting for exempted transaction does not
2610 happen as it is documented.
2611
2612 3. 2005/02/11 ssumaith - bug# 4171272 - File version 115.1
2613
2614 Shipment needs to be stopped if education cess is not available. This has been
2615 coded in this function. Five new parameters have been added to the function , hence it introduces
2616 dependency.
2617
2618 The basic business logic validation is that both cess and excise should be available as
2619 part of the same register type and the precedence setup at the organization additional information
2620 needs to be considered for picking up the correct register order.
2621
2622 These functions returns the register only if excise balance and cess balance is enough to
2623 cover the current transaction.
2624 Signature of the function has been changed because we needed to pass the additional
2625 parameters fo comparision.
2626
2627 Dependency Due to this bug:
2628 Please include all objects of the patch 4171272 along with this object whenever changed,
2629 because of change in object signature.
2630
2631 Future Dependencies For the release Of this Object:-
2632 (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/
2633 A datamodel change )
2634
2635 ----------------------------------------------------------------------------------------------------------------------------------------------------
2636 Current Version Current Bug Dependent Files Version Author Date Remarks
2637 Of File On Bug/Patchset Dependent On
2638 get_excise_register_with_bal_f.sql
2639 ----------------------------------------------------------------------------------------------------------------------------------------------------
2640
2641
2642 --------------------------------------------------------------------------------------------*/
2643 v_pref_rg23a := p_pref_rg23a;
2644 v_pref_rg23c := p_pref_rg23c;
2645 v_pref_pla := p_pref_pla;
2646 v_ssi_unit_flag := p_ssi_unit_flag;
2647 v_exempt_amt := p_exempt_amt;
2648 v_rg23a_balance := p_rg23a_balance;
2649 v_rg23c_balance := p_rg23c_balance;
2650 v_pla_balance := p_pla_balance;
2651 v_basic_pla_balance := p_basic_pla_balance;
2652 v_additional_pla_balance := p_additional_pla_balance;
2653 v_other_pla_balance := p_other_pla_balance;
2654 v_basic_excise_duty_amount := p_basic_excise_duty_amount;
2655 v_add_excise_duty_amount := p_add_excise_duty_amount;
2656 v_oth_excise_duty_amount := p_oth_excise_duty_amount;
2657 v_export_oriented_unit := p_export_oriented_unit;
2658 v_register_code := p_register_code;
2659
2660 v_debug_flag := jai_constants.no; --Ramananda for File.Sql.35
2661
2662 If v_debug_flag = 'Y' THEN
2663 --For Fetching UTIL File.......
2664 BEGIN
2665 SELECT DECODE(SUBSTR (value,1,INSTR(value,',') -1),NULL,
2666 Value,SUBSTR (value,1,INSTR(value,',') -1))
2667 INTO v_utl_location
2668 FROM v$parameter
2669 WHERE name = 'utl_file_dir';
2670 EXCEPTION
2671 WHEN OTHERS THEN
2672 v_debug_flag := 'N';
2673 END;
2674 END IF;
2675
2676 IF v_debug_flag = 'Y' THEN
2677 v_myfilehandle := UTL_FILE.FOPEN(v_utl_location,'get_excise_register_with_bal_f.log','A');
2678 UTL_FILE.PUT_LINE(v_myfilehandle,'************************Start************************************');
2679 UTL_FILE.PUT_LINE(v_myfilehandle,'The Time Stamp this Entry is Created is ' ||TO_CHAR(SYSDATE,'DD/MM/RRRR HH24:MI:SS'));
2680 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_pref_rg23a is ' || v_pref_rg23a);
2681 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_pref_rg23c is ' || v_pref_rg23c);
2682 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_pref_pla is ' || v_pref_pla);
2683 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_rg23a_balance is ' ||v_rg23a_balance);
2684 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_rg23c_balance is ' ||v_rg23c_balance);
2685 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_pla_balance is ' ||v_pla_balance);
2686 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_ssi_unit_flag is ' ||v_ssi_unit_flag);
2687 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_exempt_amt is ' ||v_exempt_amt);
2688 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_basic_pla_balance is ' ||v_basic_pla_balance);
2689 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_additional_pla_balance is ' ||v_additional_pla_balance);
2690 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_other_pla_balance is ' ||v_other_pla_balance);
2691 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_basic_excise_duty_amount is ' ||v_basic_excise_duty_amount);
2692 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_add_excise_duty_amount is ' ||v_add_excise_duty_amount);
2693 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_oth_excise_duty_amount is ' ||v_oth_excise_duty_amount);
2694 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_export_oriented_unit is ' || v_export_oriented_unit);
2695 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_register_code is ' || v_register_code);
2696 END IF;
2697 -----------------------------------------------------------------------------------------------------------------
2698 BEGIN
2699 --Balance Validations if Eou is No.....
2700 -- code written by sriram - for cenvat reversals in case of excise exempted transaction bug # 2796717
2701
2702 lv_register_type := 'RG23A';
2703
2704 /*
2705 Check Balances procedure returns 'SS' if balance is available for organization + location + register type
2706 combination for the input cess amount.
2707 */
2708 jai_cmn_rg_others_pkg.check_balances(
2709 p_organization_id => p_organization_id ,
2710 p_location_id => p_location_id ,
2711 p_register_type => lv_register_type ,
2712 p_trx_amount => p_cess_amount ,
2713 p_process_flag => lv_process_flag ,
2714 p_process_message => lv_process_message
2715 );
2716
2717 if lv_process_flag <> jai_constants.successful then
2718 lv_rg23a_cess_avlbl := 'FALSE';
2719 else
2720 lv_rg23a_cess_avlbl := 'TRUE';
2721 end if;
2722 /* added by ssawant for bug 5989740 */
2723 jai_cmn_rg_others_pkg .check_sh_balances(
2724 p_organization_id => p_organization_id ,
2725 p_location_id => p_location_id ,
2726 p_register_type => lv_register_type ,
2727 p_trx_amount => p_sh_cess_amount ,
2728 p_process_flag => lv_process_flag ,
2729 p_process_message => lv_process_message
2730 );
2731
2732 if lv_process_flag <> jai_constants.successful then
2733 lv_rg23a_sh_cess_avlbl := 'FALSE';
2734 else
2735 lv_rg23a_sh_cess_avlbl := 'TRUE';
2736 end if;
2737
2738
2739 lv_register_type := 'RG23C';
2740 jai_cmn_rg_others_pkg.check_balances(
2741 p_organization_id => p_organization_id ,
2742 p_location_id => p_location_id ,
2743 p_register_type => lv_register_type ,
2744 p_trx_amount => p_cess_amount ,
2745 p_process_flag => lv_process_flag ,
2746 p_process_message => lv_process_message
2747 );
2748
2749 if lv_process_flag <> jai_constants.successful then
2750 lv_rg23c_cess_avlbl := 'FALSE';
2751 else
2752 lv_rg23c_cess_avlbl := 'TRUE';
2753 end if;
2754
2755 /* added by ssawant for bug 5989740 */
2756 jai_cmn_rg_others_pkg .check_sh_balances(
2757 p_organization_id => p_organization_id ,
2758 p_location_id => p_location_id ,
2759 p_register_type => lv_register_type ,
2760 p_trx_amount => p_sh_cess_amount ,
2761 p_process_flag => lv_process_flag ,
2762 p_process_message => lv_process_message
2763 );
2764
2765 if lv_process_flag <> jai_constants.successful then
2766 lv_rg23a_sh_cess_avlbl := 'FALSE';
2767 else
2768 lv_rg23a_sh_cess_avlbl := 'TRUE';
2769 end if;
2770
2771
2772 lv_register_type := 'PLA';
2773 jai_cmn_rg_others_pkg.check_balances(
2774 p_organization_id => p_organization_id ,
2775 p_location_id => p_location_id ,
2776 p_register_type => lv_register_type ,
2777 p_trx_amount => p_cess_amount ,
2778 p_process_flag => lv_process_flag ,
2779 p_process_message => lv_process_message
2780 );
2781
2782 if lv_process_flag <> jai_constants.successful then
2783 lv_pla_cess_avlbl := 'FALSE';
2784 else
2785 lv_pla_cess_avlbl := 'TRUE';
2786 end if;
2787 /* added by ssawant for bug 5989740 */
2788 jai_cmn_rg_others_pkg .check_sh_balances(
2789 p_organization_id => p_organization_id ,
2790 p_location_id => p_location_id ,
2791 p_register_type => lv_register_type ,
2792 p_trx_amount => p_sh_cess_amount ,
2793 p_process_flag => lv_process_flag ,
2794 p_process_message => lv_process_message
2795 );
2796
2797 if lv_process_flag <> jai_constants.successful then
2798 lv_rg23a_sh_cess_avlbl := 'FALSE';
2799 else
2800 lv_rg23a_sh_cess_avlbl := 'TRUE';
2801 end if;
2802
2803 IF v_rg23a_balance >= NVL(v_exempt_amt,0) THEN
2804 if lv_rg23a_cess_avlbl = 'TRUE' and lv_rg23a_sh_cess_avlbl = 'TRUE' then
2805 v_reg_type := 'RG23A';
2806 RETURN(v_reg_type);
2807 end if;
2808 END IF;
2809
2810 -- ends here code by added by sriram .- bug # 2796717
2811 IF v_export_oriented_unit = 'N' Then
2812 IF v_pref_rg23a = 1 THEN -------------------------------------------------------7
2813 IF v_rg23a_balance >= NVL(v_exempt_amt,0) AND lv_rg23a_cess_avlbl = 'TRUE' AND lv_rg23a_sh_cess_avlbl = 'TRUE' THEN ---------------------------8/* added by ssawant for bug 5989740 */
2814 v_reg_type := 'RG23A';
2815 ELSIF v_pref_rg23c = 2 THEN
2816 IF v_rg23c_balance >= NVL(v_exempt_amt,0) AND lv_rg23c_cess_avlbl = 'TRUE' and lv_rg23c_sh_cess_avlbl='TRUE' THEN ------------------9/* added by ssawant for bug 5989740 */
2817 v_reg_type := 'RG23C';
2818 ELSIF v_pref_pla =3 THEN
2819 IF v_pla_balance >= NVL(v_exempt_amt,0) AND lv_pla_cess_avlbl = 'TRUE' AND lv_pla_sh_cess_avlbl = 'TRUE' THEN --------------10/* added by ssawant for bug 5989740 */
2820 v_reg_type := 'PLA';
2821 ELSIF NVL(v_ssi_unit_flag,'N') = 'Y' THEN
2822 v_reg_type := 'PLA';
2823 ELSE
2824 v_reg_type := 'ERROR';
2825 END IF;--------------------------------------------------------10
2826 ELSE
2827 v_reg_type := 'ERROR';
2828 END IF;---------------------------------------------------------------9
2829 ELSIF v_pref_pla = 2 THEN
2830 IF v_pla_balance >= NVL(v_exempt_amt,0) AND lv_pla_cess_avlbl = 'TRUE' AND lv_pla_sh_cess_avlbl = 'TRUE' THEN -------------------11/* added by ssawant for bug 5989740 */
2831 v_reg_type := 'PLA';
2832 ELSIF NVL(v_ssi_unit_flag,'N') = 'Y' THEN
2833 v_reg_type := 'PLA';
2834 ELSIF v_pref_rg23c = 3 THEN
2835 IF v_rg23c_balance >= NVL(v_exempt_amt,0) AND lv_rg23c_cess_avlbl = 'TRUE' AND lv_rg23c_sh_cess_avlbl = 'TRUE' THEN ----------12/* added by ssawant for bug 5989740 */
2836 v_reg_type := 'RG23C';
2837 ELSE
2838 v_reg_type := 'ERROR';
2839 END IF;--------------------------------------------------------12
2840 ELSE
2841 v_reg_type := 'ERROR';
2842 END IF;------------------------------------------------------------ 11
2843 ELSE
2844 v_reg_type :='ERROR';
2845 END IF;------------------------------------------------------------------8
2846 -------------------------------------------------------------------------------------------------------------------
2847 ELSIF v_pref_rg23c = 1 THEN -------------------------------------------------------7
2848 IF v_rg23c_balance >= NVL(v_exempt_amt,0) AND lv_rg23c_cess_avlbl = 'TRUE' AND lv_rg23c_sh_cess_avlbl = 'TRUE' THEN ---------------------------8/* added by ssawant for bug 5989740 */
2849 v_reg_type := 'RG23C';
2850 ELSIF v_pref_rg23a = 2 THEN
2851 IF v_rg23a_balance >= NVL(v_exempt_amt,0) AND lv_rg23a_cess_avlbl = 'TRUE' AND lv_rg23a_sh_cess_avlbl = 'TRUE' THEN ------------------9/* added by ssawant for bug 5989740 */
2852 v_reg_type := 'RG23A';
2853 ELSIF v_pref_pla =3 THEN
2854 IF v_pla_balance >= NVL(v_exempt_amt,0) AND lv_pla_cess_avlbl = 'TRUE' AND lv_pla_sh_cess_avlbl = 'TRUE' THEN --------------10/* added by ssawant for bug 5989740 */
2855 v_reg_type := 'PLA';
2856 ELSIF NVL(v_ssi_unit_flag,'N') = 'Y' THEN
2857 v_reg_type := 'PLA';
2858 ELSE
2859 v_reg_type := 'ERROR';
2860 END IF;--------------------------------------------------------10
2861 ELSE
2862 v_reg_type := 'ERROR';
2863 END IF;---------------------------------------------------------------9
2864 ELSIF v_pref_pla = 2 THEN
2865 IF v_pla_balance >= NVL(v_exempt_amt,0) AND lv_pla_cess_avlbl = 'TRUE' AND lv_pla_sh_cess_avlbl = 'TRUE' THEN -------------------11/* added by ssawant for bug 5989740 */
2866 v_reg_type := 'PLA';
2867 ELSIF NVL(v_ssi_unit_flag,'N') = 'Y' THEN
2868 v_reg_type := 'PLA';
2869 ELSIF v_pref_rg23a = 3 THEN
2870 IF v_rg23a_balance >= NVL(v_exempt_amt,0) AND lv_rg23a_cess_avlbl = 'TRUE' AND lv_rg23a_sh_cess_avlbl = 'TRUE' THEN ----------12/* added by ssawant for bug 5989740 */
2871 v_reg_type := 'RG23A';
2872 ELSE
2873 v_reg_type := 'ERROR';
2874 END IF;--------------------------------------------------------12
2875 ELSE v_reg_type := 'ERROR';
2876 END IF;------------------------------------------------------------ 11
2877 ELSE
2878 v_reg_type :='ERROR';
2879 END IF;------------------------------------------------------------------8
2880 -------------------------------------------------------------------------------------------------------------------
2881 ELSIF v_pref_pla = 1 THEN
2882 IF v_pla_balance >= NVL(v_exempt_amt,0) AND lv_pla_cess_avlbl = 'TRUE' AND lv_pla_sh_cess_avlbl = 'TRUE' THEN ---------------------------13/* added by ssawant for bug 5989740 */
2883 v_reg_type := 'PLA';
2884 ELSIF NVL(v_ssi_unit_flag,'N') = 'Y' THEN
2885 v_reg_type := 'PLA';
2886 ELSIF v_pref_rg23a = 2 THEN
2887 IF v_rg23a_balance >= NVL(v_exempt_amt,0) AND lv_rg23a_cess_avlbl = 'TRUE' AND lv_rg23a_sh_cess_avlbl = 'TRUE' THEN ------------------14/* added by ssawant for bug 5989740 */
2888 v_reg_type := 'RG23A';
2889 ELSIF v_pref_rg23c =3 THEN
2890 IF v_rg23c_balance >= NVL(v_exempt_amt,0) AND lv_rg23c_cess_avlbl = 'TRUE' AND lv_rg23c_sh_cess_avlbl = 'TRUE' THEN --------------15/* added by ssawant for bug 5989740 */
2891 v_reg_type := 'RG23C';
2892 ELSE
2893 v_reg_type := 'ERROR';
2894 END IF;--------------------------------------------------------15
2895 ELSE
2896 v_reg_type := 'ERROR';
2897 END IF;---------------------------------------------------------------14
2898 ELSIF v_pref_rg23c = 2 THEN
2899 IF v_rg23c_balance >= NVL(v_exempt_amt,0) AND lv_rg23c_cess_avlbl = 'TRUE' AND lv_rg23c_sh_cess_avlbl = 'TRUE' THEN -------------------16/* added by ssawant for bug 5989740 */
2900 v_reg_type := 'RG23C';
2901 ELSIF v_pref_rg23a = 3 THEN
2902 IF v_rg23a_balance >= NVL(v_exempt_amt,0) AND lv_rg23a_cess_avlbl = 'TRUE' AND lv_rg23a_sh_cess_avlbl = 'TRUE' THEN ----------17/* added by ssawant for bug 5989740 */
2903 v_reg_type := 'RG23A';
2904 ELSE
2905 v_reg_type := 'ERROR';
2906 END IF;--------------------------------------------------------17
2907 ELSE
2908 v_reg_type := 'ERROR';
2909 END IF;------------------------------------------------------------ 16
2910 ELSE
2911 v_reg_type :='ERROR';
2912 END IF;------------------------------------------------------------------13
2913 ELSE
2914 v_reg_type :='ERROR';
2915 END IF;---------------------------------------------------------------------------7
2916
2917 --Balance Validations if EOU is Yes.....
2918 ELSIF v_export_oriented_unit ='Y' and v_register_code='EXPORT_EXCISE' THEN
2919
2920 --Validation for Basic Excise Duty Amount.
2921 IF nvl(v_basic_excise_duty_amount,0) >0 THEN
2922 IF v_basic_pla_balance >= v_basic_excise_duty_amount AND lv_pla_cess_avlbl = 'TRUE' AND lv_pla_sh_cess_avlbl = 'TRUE' THEN/* added by ssawant for bug 5989740 */
2923 v_reg_type := 'PLA';
2924 ELSE
2925 v_reg_type := 'ERROR';
2926 END IF;
2927 END IF;
2928
2929
2930 --Validation for Additional Excise Duty Amount.
2931 IF v_reg_type<>'ERROR' THEN
2932 IF nvl(v_add_excise_duty_amount,0) >0 THEN
2933 IF v_additional_pla_balance >= v_add_excise_duty_amount AND lv_pla_cess_avlbl = 'TRUE' AND lv_pla_sh_cess_avlbl ='TRUE' THEN/* added by ssawant for bug 5989740 */
2934 v_reg_type := 'PLA';
2935 ELSE
2936 v_reg_type := 'ERROR';
2937 END IF;
2938 END IF;
2939 END IF;
2940
2941 --Validation for Other Excise Duty Amount.
2942 IF v_reg_type<>'ERROR' THEN
2943 IF nvl(v_oth_excise_duty_amount,0) >0 THEN
2944 IF v_other_pla_balance >= v_oth_excise_duty_amount AND lv_pla_cess_avlbl = 'TRUE' AND lv_pla_sh_cess_avlbl ='TRUE' THEN/* added by ssawant for bug 5989740 */
2945 v_reg_type := 'PLA';
2946 ELSE
2947 v_reg_type := 'ERROR';
2948 END IF;
2949 END IF;
2950 END IF;
2951 END IF; --End of Export Oriented Check......
2952 -----------------------------------------------------------------------------------------------------------------
2953 EXCEPTION
2954 WHEN others THEN
2955 RAISE_APPLICATION_ERROR(-20001,'Error Raised in get_excise_register_with_bal function');
2956 END;
2957
2958 --To Raise an Application Error in the Function only rather than in the Trigger or Procedure........
2959 IF v_reg_type='ERROR' THEN
2960 BEGIN
2961 SELECT trip_id
2962 INTO v_trip_id
2963 FROM wsh_delivery_trips_v
2964 WHERE delivery_id=p_delivery_id;
2965 EXCEPTION
2966 WHEN OTHERS THEN
2967 NULL;
2968 END;
2969
2970 IF v_debug_flag = 'Y' THEN
2971 UTL_FILE.PUT_LINE(v_myfilehandle,'Transaction failed as balances are not sufficient');
2972 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_trip_id for which transaction failed is ' || v_trip_id);
2973 UTL_FILE.PUT_LINE(v_myfilehandle,'************************END************************************');
2974 UTL_FILE.FCLOSE(v_myfileHandle);
2975 END IF;
2976 IF v_export_oriented_unit ='N' THEN
2977 RAISE_APPLICATION_ERROR(-20120, 'None of the Register Have Balances Greater OR Equal TO the Excisable Amount ->'
2978 || TO_CHAR(v_exempt_amt) || ' OR Cess Amount => ' || to_char(p_cess_amount) );
2979 ELSIF v_export_oriented_unit ='Y' THEN
2980 RAISE_APPLICATION_ERROR(-20120, 'The Excise Component Balances are not sufficient');
2981 END IF;
2982 END IF;
2983 --p_reg_type := v_reg_type;
2984 RETURN(v_reg_type);
2985
2986 EXCEPTION
2987 WHEN OTHERS THEN
2988 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
2989 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
2990 app_exception.raise_exception;
2991
2992 END get_excise_register_with_bal;
2993
2994 PROCEDURE process_deliveries
2995 (
2996 errbuf OUT NOCOPY VARCHAR2 ,
2997 retcode OUT NOCOPY VARCHAR2 ,
2998 pn_delivery_id IN NUMBER
2999 )
3000 IS
3001 ln_error_occured number := 0;
3002 ln_success number := 0;
3003
3004 /**********************************************************************
3005 CREATED BY : ssumaith
3006 CREATED DATE : 11-JAN-2005
3007 ENHANCEMENT BUG : 4136981
3008 PURPOSE : To Pass Excise Entries when called from Excise Invoice Generation Program
3009 CALLED FROM : Called from the Concurrent - India Excise Invoice Generation Program.
3010
3011 **********************************************************************/
3012
3013 BEGIN
3014
3015 /** begin processing
3016 A delivery id was passed , So just call the procedure once , ascertain the return status and signal completion
3017 **/
3018
3019 IF pn_delivery_id IS NOT NULL THEN
3020 jai_om_wsh_pkg.process_delivery ( errbuf ,
3021 retcode ,
3022 pn_delivery_id
3023 ) ;
3024 fnd_file.put_line ( fnd_file.log , ' After call to jai_om_wsh_pkg.process_delivery with delivery id => ' || pn_delivery_id || ' return code => ' || retcode);
3025 IF nvl(retcode,'0') <> '0' THEN
3026 ln_error_occured := 1;
3027 ln_success := 0;
3028 Rollback;
3029 fnd_file.put_line ( fnd_file.log , 'delivery_id => ' || pn_delivery_id || 'Error is ' || errbuf ) ;
3030 else
3031
3032 commit;
3033 ln_success := 1;
3034
3035 END IF ;
3036
3037 ELSE
3038 FOR temp_rec IN ( Select distinct joogtt.delivery_id
3039 From JAI_OM_OE_GEN_TAXINV_T joogtt,
3040 jai_om_wsh_lines_all jowla
3041 where joogtt.delivery_id = jowla.delivery_id
3042 and mo_global.check_access(jowla.org_id) = 'Y'
3043 ) /*Added join of table jai_om_wsh_lines_all and check_access by mmurtuza for bug 16308603*/
3044 LOOP
3045 BEGIN
3046 fnd_file.put_line ( fnd_file.log , ' Calling jai_om_wsh_pkg.process_delivery with delivery id => ' || temp_rec.delivery_id );
3047 jai_om_wsh_pkg.process_delivery(errbuf ,
3048 retcode ,
3049 temp_rec.delivery_id
3050 ) ;
3051 fnd_file.put_line ( fnd_file.log , ' After call to jai_om_wsh_pkg.process_delivery with delivery id => ' || temp_rec.delivery_id || ' return code => ' || retcode);
3052 IF nvl(retcode,'0') <> '0' THEN
3053 /* An Error has occured - rollback the changes done by the changes and proceed with the next one*/
3054 fnd_file.put_line ( fnd_file.log , ' After call to jai_om_wsh_pkg.process_delivery with delivery id => ' || temp_rec.delivery_id || ' Error is => ' || errbuf);
3055 ln_error_occured := 1;
3056 Rollback;
3057 else
3058 ln_success := 1; /* It will hold the status whether atleast one delivery was successfully processed*/
3059 commit;
3060
3061 END IF ;
3062 EXCEPTION
3063 WHEN OTHERS THEN
3064 ln_error_occured := 1; /* It will hold the status whether atleast one delivery was errored */
3065 END;
3066 END LOOP ;
3067 END IF ;
3068
3069
3070 /* Final Concurent program Completion status setting */
3071 if ln_error_occured = 1 and ln_success = 1 then
3072 retcode := '1'; /* Signal a warning , Atleast one delivery went into exception and atleast one delivery was processed successfully*/
3073 elsif ln_error_occured = 1 and ln_success = 0 then
3074 retcode := '2'; /* Signal an errror , Atleast one delivery went into exception and no delivery was processed successfully*/
3075 elsif ln_error_occured = 0 and ln_success = 1 then
3076 retcode := '0'; /* Signal success , All deliveries was processed successfully and none errored out*/
3077
3078 end if;
3079
3080 EXCEPTION
3081 WHEN others THEN
3082 if ln_success = 1 then
3083 retcode := '1';
3084 else
3085 retcode := '2';
3086 end if;
3087 errbuf := substr(sqlerrm,1,1999);
3088 END process_deliveries;
3089
3090 END jai_om_wsh_pkg;
[Home] [Help]