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