1 PACKAGE BODY JAI_RCV_TAX_PKG AS
2 /* $Header: jai_rcv_tax.plb 120.27 2008/04/08 09:25:00 jmeena ship $ */
3
4
5 PROCEDURE default_taxes_onto_line
6 (
7 p_transaction_id NUMBER,
8 p_parent_transaction_id NUMBER,
9 p_shipment_header_id NUMBER,
10 p_shipment_line_id NUMBER,
11 p_organization_id NUMBER,
12 p_requisition_line_id NUMBER,
13 p_qty_received NUMBER,
14 p_primary_quantity NUMBER,
15 p_line_location_id NUMBER,
16 p_transaction_type VARCHAR2,
17 p_source_document_code VARCHAR2,
18 p_destination_type_code VARCHAR2,
19 p_subinventory VARCHAR2,
20 p_vendor_id NUMBER,
21 p_vendor_site_id NUMBER,
22 p_po_header_id NUMBER,
23 p_po_line_id NUMBER,
24 p_location_id NUMBER,
25 p_transaction_date DATE,
26 p_uom_code VARCHAR2,
27 --Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. p_attribute1 VARCHAR2,
28 --p_attribute2 DATE,
29 --p_attribute3 VARCHAR2,
30 --p_attribute4 VARCHAR2,
31 p_attribute15 VARCHAR2,
32 p_currency_code VARCHAR2,
33 p_currency_conversion_type VARCHAR2,
34 p_currency_conversion_date DATE,
35 p_currency_conversion_rate NUMBER,
36 p_creation_date DATE,
37 p_created_by NUMBER,
38 p_last_update_date DATE,
39 p_last_updated_by NUMBER,
40 p_last_update_login NUMBER,
41 p_unit_of_measure VARCHAR2,
42 p_po_distribution_id NUMBER,
43 p_oe_order_header_id NUMBER,
44 p_oe_order_line_id NUMBER,
45 p_routing_header_id NUMBER,
46 -- Vijay Shankar for Bug#3940588 RECEIPTS DEPLUG
47 /* R12-PADDR p_chk_form OUT NOCOPY VARCHAR2, */
48 -- Vijay Shankar for Bug#4159557
49 p_interface_source_code VARCHAR2,
50 p_interface_transaction_id VARCHAR2,
51 p_allow_tax_change_hook VARCHAR2
52 ) IS
53
54 /* Added by Ramananda for bug# exc_objects */
55 lv_object_name CONSTANT VARCHAR2(61) := 'jai_rcv_tax_pkg.default_taxes_onto_line';
56
57 -- LAST MODIFIED BY SRIHARI on 25-JUN-2000
58 v_receipt_num rcv_shipment_headers.receipt_num % TYPE;
59 v_loc_quantity po_line_locations_all.quantity % TYPE;
60 v_cor_amount JAI_PO_LINE_LOCATIONS.tax_amount % TYPE;
61 -- v_form_id VARCHAR2(30); --File.Sql.35 Cbabu := 'JAINPORE';
62 v_chk_form VARCHAR2(30);
63 -- v_rowid JAI_CMN_LOCATORS_T.row_id % TYPE;
64 v_conf NUMBER;
65 v_receipt_modify_flag JAI_CMN_INVENTORY_ORGS.receipt_modify_flag % TYPE;
66 v_cor_quantity NUMBER;
67 v_rg_location_id JAI_INV_SUBINV_DTLS.location_id % TYPE;
68 v_po_header_date DATE;
69 v_vendor_site_id po_headers_all.vendor_site_id % TYPE;
70 v_item_id rcv_shipment_lines.item_id % TYPE;
71 v_organization_id rcv_shipment_lines.to_organization_id % TYPE;
72 v_item_modvat_flag JAI_INV_ITM_SETUPS.modvat_flag % TYPE;
73 v_item_trading_flag JAI_INV_ITM_SETUPS.item_trading_flag % TYPE;
74 v_receipt_source_code rcv_shipment_headers.receipt_source_code % TYPE;
75 -- v_paddr v$session.paddr % TYPE;
76 -- v_temp_status ja_in_temp_receipt.status % TYPE; --Commented by Nagaraj.s for Bug#2692052
77 v_paddr RAW(32); /*Bug 4644524 bduvarag*/
78 v_modvat_flag JAI_INV_ITM_SETUPS.modvat_flag % TYPE; --Changed the %type by Nagaraj.s for Bug#2692052
79 v_tax_total NUMBER; --File.Sql.35 Cbabu := 0;
80 -- v_line_id so_lines_all.line_id % type; --commented by GSri and Jagdish on 5-may-01
81 v_line_id oe_order_lines_all.line_id % TYPE; --added by GSri and Jagdish on 5-may-01
82 v_rg_type VARCHAR2(30);
83 v_so_currency oe_order_headers_all.transactional_curr_code % TYPE; -- added
84 v_req_id NUMBER;
85 v_result BOOLEAN;
86 v_currency_code rcv_transactions.currency_code % TYPE;
87 v_currency_conversion_rate rcv_transactions.currency_conversion_rate % TYPE;
88 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. v_attribute1 VARCHAR2(150);
89 --v_attribute2 DATE;
90 --v_attribute3 VARCHAR2(150);
91 v_range_no JAI_CMN_VENDOR_SITES.excise_duty_range % TYPE;
92 v_division_no JAI_CMN_VENDOR_SITES.excise_duty_division % TYPE;
93 v_claim_modvat_flag JAI_RCV_LINES.claim_modvat_flag % TYPE;
94 v_func_currency gl_sets_of_books.currency_code % TYPE;
95 v_gl_set_of_books_id gl_sets_of_books.set_of_books_id % TYPE;
96 v_conv_factor NUMBER;
97 v_register_type JAI_CMN_RG_23AC_I_TRXS.register_type % TYPE;
98 v_duplicate_ship VARCHAR2(1); --File.Sql.35 Cbabu := 'N';
99 v_picking_line_id JAI_OM_WSH_LINES_ALL.delivery_detail_id % TYPE; --added
100 v_rsh_organization_id rcv_shipment_headers.organization_id % TYPE;
101 v_internal_vendor JAI_CMN_TAXES_ALL.vendor_id % TYPE;
102 v_current_tax NUMBER;
103 v_trading JAI_CMN_INVENTORY_ORGS.trading % TYPE;
104 v_manufacturing JAI_CMN_INVENTORY_ORGS.manufacturing % TYPE;
105 v_bonded JAI_INV_SUBINV_DTLS.bonded % TYPE;
106 v_claimable_amount JAI_RCV_LINE_TAXES.claimable_amount % TYPE;
107 -- Start of addition by Srihari on 30-NOV-99
108 v_uom_rate NUMBER;
109 v_po_uom mtl_units_of_measure.unit_of_measure % TYPE;
110 v_po_uom_code mtl_units_of_measure.uom_code % TYPE;
111 v_rcv_uom_code mtl_units_of_measure.unit_of_measure % TYPE;
112 v_tax_modvat_flag JAI_RCV_LINE_TAXES.modvat_flag % TYPE;
113 v_chk_excise NUMBER;
114 v_chk_receipt_lines NUMBER; --File.Sql.35 Cbabu :=0;
115 v_chk_receipt_tax_lines NUMBER; --File.Sql.35 Cbabu :=0;
116 --v_receipt_routing NUMBER; --Added by Nagaraj.s for Bug#2499017
117 -- Variables added by Aparajita on 17th june for bug#2415767
118 v_po_currency po_headers_all.CURRENCY_CODE%TYPE;
119 v_po_rate po_headers_all.RATE%TYPE;
120 v_tax_currency po_headers_all.CURRENCY_CODE%TYPE;
121
122 v_precedence_0 NUMBER; --File.Sql.35 Cbabu :=0;
123 v_precedence_non_0 NUMBER; --File.Sql.35 Cbabu :=0;
124
125 v_tax_base NUMBER; --File.Sql.35 Cbabu :=0;
126 v_receipt_tax NUMBER; -- this has not been assigned intentionally.
127 v_debug_flag varchar2(1); --File.Sql.35 Cbabu := 'N';
128 v_utl_location VARCHAR2(512); --For Log file.
129 v_myfilehandle UTL_FILE.FILE_TYPE; -- This is for File handling
130 -- Variables added by Aparajita on 17th june for bug#2415767
131 --Variables added by Nagaraj.s for Bug2991872.
132 v_price_override NUMBER; --File.Sql.35 Cbabu :=0;
133 v_po_quantity NUMBER; --File.Sql.35 Cbabu :=0;
134 v_assessable_value NUMBER; --File.Sql.35 Cbabu :=0;
135
136 /* Vijay Shankar for Bug#4250236(4245089). VAT Impl. */
137 ln_vat_assess_value NUMBER; --File.Sql.35 Cbabu :=0;
138 ln_chk_vat number ;
139 lv_vat_recoverable_for_item JAI_RGM_ITM_TMPL_ATTRS.ATTRIBUTE_VALUE%TYPE;
140 lv_process_flag VARCHAR2(2);
141 lv_process_msg VARCHAR2(1000);
142 ln_vat_setup_chk NUMBER;
143 ln_test_delivery_id JAI_OM_WSH_LINES_ALL.delivery_id%TYPE; --Added by Ramananda for Bug#4533114
144 --Added by walton for inclusive tax
145 ---------------------------------------------
146 TYPE num_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
147 TYPE tax_amt_num_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
148 tax_amt_tab TAX_AMT_NUM_TAB;
149 round_factor_tab TAX_AMT_NUM_TAB;
150 base_tax_amt_tab TAX_AMT_NUM_TAB;
151 func_tax_amt_tab TAX_AMT_NUM_TAB;
152
153 TYPE char_tab IS TABLE OF VARCHAR2(10)
154 INDEX BY BINARY_INTEGER;
155
156 lt_adhoc_tax_tab CHAR_TAB;
157 lt_inclusive_tax_tab CHAR_TAB;
158 lt_tax_modvat_flag CHAR_TAB;
159 lt_third_party_flag CHAR_TAB;
160 ln_exclusive_price NUMBER;
161 lt_tax_rate_per_rupee NUM_TAB;
162 lt_cumul_tax_rate_per_rupee NUM_TAB;
163 ln_total_non_rate_tax NUMBER :=0;
164 ln_total_inclusive_factor NUMBER;
165 lt_tax_amt_rate_tax_tab TAX_AMT_NUM_TAB;
166 lt_tax_amt_non_rate_tab TAX_AMT_NUM_TAB;
167 ln_bsln_amt_nr NUMBER :=0;
168 ln_tax_amt_nr NUMBER(38,10) :=0;
169 ln_vamt_nr NUMBER(38,10) :=0;
170 ln_total_tax_per_rupee NUMBER;
171 ln_assessable_value NUMBER;
172 ln_vat_assessable_value NUMBER;
173 ln_curflag NUMBER; --Add by Kevin Cheng for bug 6853787 Mar 5, 2008
174 lv_valid_date DATE;
175
176 p1 NUM_TAB;
177 p2 NUM_TAB;
178 p3 NUM_TAB;
179 p4 NUM_TAB;
180 p5 NUM_TAB;
181
182 p6 NUM_TAB;
183 p7 NUM_TAB;
184 p8 NUM_TAB;
185 p9 NUM_TAB;
186 p10 NUM_TAB;
187 end_date_tab NUM_TAB;
188 tax_rate_tab NUM_TAB;
189 tax_type_tab NUM_TAB;
190 tax_rate_zero_tab NUM_TAB;
191 tax_target_tab NUM_TAB;
192
193 bsln_amt NUMBER := 0;
194 vamt NUMBER := 0;
195 row_count NUMBER := 1;
196 v_tax_amt NUMBER := 0;
197 max_iter NUMBER := 10;
198 v_func_tax_amt NUMBER := 0;
199 v_amt NUMBER := 0;
200
201 errormsg VARCHAR2(500);
202
203 cursor c_get_inclusive_flag
204 ( pn_tax_id number
205 )
206 is
207 select NVL(inclusive_tax_flag,'N'), end_date
208 from jai_cmn_taxes_all
209 where tax_id=pn_tax_id;
210 -----------------------------------------------
211
212 CURSOR c_rgm_setup_for_orgn_loc(cp_regime_code varchar2, cp_organization_type varchar2,
213 cp_organization_id number, cp_location_id number) IS
214 SELECT 1 FROM jai_rgm_parties a, JAI_RGM_DEFINITIONS b
215 WHERE a.regime_id = b.regime_id
216 AND b.regime_code=cp_regime_code
217 AND a.organization_type = cp_organization_type
218 AND a.organization_id = cp_organization_id
219 AND (cp_location_id is null or a.location_id=cp_location_id);
220
221 CURSOR c_rcv_rgm_dtl(cp_regime_code VARCHAR2, cp_shipment_line_id NUMBER) IS
222 SELECT nvl(process_status_flag, jai_constants.no) process_status_flag,
223 regime_item_class,
224 invoice_no
225 FROM jai_rcv_rgm_lines
226 WHERE shipment_line_id = cp_shipment_line_id
227 AND regime_code = cp_regime_code;
228
229 r_rcv_rgm_dtl c_rcv_rgm_dtl%ROWTYPE;
230 /* End of VAT Impl. */
231
232 v_tax_vendor_site_id JAI_CMN_VENDOR_SITES.vendor_site_id%type; --Added by Nagaraj.s for Bug3037075
233 v_third_party_flag JAI_RCV_LINE_TAXES.third_party_flag%type; --Added by Nagaraj.s for Bug3037075
234 --Ends over here.
235 v_item_class JAI_INV_ITM_SETUPS.item_class%type; --3202319
236
237 CURSOR uom_cur(c_uom VARCHAR2) IS
238 SELECT uom_code
239 FROM mtl_units_of_measure
240 WHERE unit_of_measure = c_uom;
241 -- End of addition by Srihari on 30-NOV-99.
242
243 -- Added by GSRI on 21-OCT-01
244 CURSOR get_paddr IS SELECT paddr
245 FROM v$session vs, v$mystat vm
246 WHERE vs.sid = vm.sid
247 AND ROWNUM = 1;
248 -- End of Addition on 21-OCT-01
249
250 -- cbabu for Bug# 3028040
251 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. CURSOR c_hdr_attribute5(p_shipment_header_id IN NUMBER) IS
252 CURSOR c_hdr_dtl(p_shipment_header_id IN NUMBER) IS
253 SELECT -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. attribute5,
254 shipment_num
255 FROM rcv_shipment_headers
256 WHERE shipment_header_id = p_shipment_header_id;
257
258 --Added by Sanjikum for Bug#4533114
259 CURSOR c_hdr_attribute5_1(p_delivery_name VARCHAR2)
260 IS
261 SELECT delivery_id
262 FROM wsh_new_deliveries
263 WHERE name = p_delivery_name ;
264
265 CURSOR c_fetch_unclaim_cenvat is
266 SELECT nvl(unclaim_cenvat_flag,'N'),
267 nvl(cenvat_claimed_ptg,0),
268 nvl(non_bonded_delivery_flag,'N'), --3655330
269 nvl(cenvat_amount,0) -- Bug 4516678. Added by Lakshmi Gopalsami
270 FROM JAI_RCV_CENVAT_CLAIMS
271 WHERE shipment_line_id = p_shipment_line_id;
272
273 v_unclaim_cenvat_flag JAI_RCV_CENVAT_CLAIMS.unclaim_cenvat_flag%type;
274 v_cenvat_claimed_ptg JAI_RCV_CENVAT_CLAIMS.cenvat_claimed_ptg%type;
275 v_non_bonded_delivery_flag JAI_RCV_CENVAT_CLAIMS.non_bonded_delivery_flag%type;
276 -- bug 4516678. Added by Lakshmi Gopalsami
277 v_cenvat_amount JAI_RCV_CENVAT_CLAIMS.cenvat_amount%type;
278
279 v_express VARCHAR2(100);
280 v_shipment_num rcv_shipment_headers.shipment_num%type; -- ssumaith - bug# 3657662
281 v_order_header_id oe_order_headers_all.header_id%type; -- ssumaith - bug# 3657662
282
283 cursor c_order_cur (p_shipment_num rcv_shipment_headers.shipment_num%type) is
284 select order_header_id
285 from JAI_OM_WSH_LINES_ALL
286 where delivery_id = p_shipment_num;
287
288 /* bug 4516678. Added by Lakshmi Gopalsami */
289
290 cursor c_fetch_receive_quantity(p_shipment_header_id number,
291 p_shipment_line_id number) is
292 select qty_received
293 from JAI_RCV_LINES
294 where shipment_header_id = p_shipment_header_id
295 and shipment_line_id = p_shipment_line_id;
296
297 cursor c_fetch_transaction_quantity(p_shipment_header_id number,
298 p_shipment_line_id number ,
299 p_transaction_type varchar2) is
300 select sum(quantity)
301 from JAI_RCV_TRANSACTIONS
302 where shipment_header_id = p_shipment_header_id
303 and shipment_line_id = p_shipment_line_id
304 and transaction_type = p_transaction_type;
305
306 v_receipt_quantity number;
307 v_sum_rtv_quantity number;
308
309 -- End for bug4516678.
310
311 /* Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. */
312 cursor c_ja_rcv_interface(cp_interface_trx_id in number) is
313 select interface_transaction_id, excise_invoice_no, excise_invoice_date, online_claim_flag
314 from jai_rcv_interface_trxs
315 where interface_transaction_id = cp_interface_trx_id;
316
317 r_ja_rcv_interface c_ja_rcv_interface%ROWTYPE;
318 lv_excise_invoice_no JAI_RCV_LINES.excise_invoice_no%TYPE;
319 lv_excise_invoice_date JAI_RCV_LINES.excise_invoice_date%TYPE;
320 lv_online_claim_flag JAI_RCV_LINES.online_claim_flag%TYPE;
321
322 /* Bug 5243532. Added by Lakshmi Gopalsami
323 * Defined variable for implementing caching logic.
324 */
325 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
326 -- End for bug 5243532
327 /*Bug 4644524 start bduvarag*/
328 CURSOR c_rcv_shipment_lines(cp_shipment_line_id rcv_shipment_lines.shipment_line_id%TYPE)
329 IS
330 SELECT item_id
331 FROM rcv_shipment_lines
332 WHERE shipment_line_id = cp_shipment_line_id;
333
334 r_rcv_shipment_lines c_rcv_shipment_lines%ROWTYPE;
335 /*Bug 4644524 End bduvarag*/
336
337 -- For iSupp Porting
338 /*
339 || Start additions by ssumaith - Iprocurement.
340 */
341
342 CURSOR check_rcpt_source IS
343 SELECT apps_source_code
344 FROM po_requisition_headers_all WHERE requisition_header_id IN
345 (SELECT requisition_header_id
346 FROM po_requisition_lines_all
347 WHERE line_location_id = p_line_location_id
348 );
349
350 lv_apps_source_code VARCHAR2(30);
351
352 /*
353 || End additions by ssumaith - iprocurement
354 */
355
356
357
358 /*
359 start for ASBN -- ssumaith
360 */
361
362
363 cursor c_check_asbn is
364 SELECT '1' , shipment_num
365 from rcv_shipment_headers
366 where shipment_header_id = p_shipment_header_id
367 And asn_type = 'ASBN';
368
369
370 lv_asbn_type varchar2(10);
371 lv_shipment_num VARCHAR2(100);
372 ln_po_unit_price NUMBER;
373
374
375
376 TYPE PO_TAX_CUR IS RECORD(
377 tax_line_no JAI_PO_TAXES.TAX_LINE_NO%TYPE,
378 Tax_Id JAI_PO_TAXES.TAX_ID%TYPE,
379 Tax_rate JAI_PO_TAXES.TAX_RATE%TYPE,
380 Qty_Rate JAI_PO_TAXES.QTY_RATE%TYPE,
381 Uom JAI_PO_TAXES.UOM%TYPE,
382 Tax_Amount JAI_PO_TAXES.TAX_AMOUNT%TYPE,
383 tax_type JAI_PO_TAXES.TAX_TYPE%TYPE,
384 tax_name JAI_CMN_TAXES_ALL.TAX_NAME%TYPE,
385 modvat_flag JAI_PO_TAXES.modvat_flag%TYPE,
386 vendor_id JAI_CMN_TAXES_ALL.vendor_id%type,
387 tax_vendor_id JAI_CMN_TAXES_ALL.vendor_id%type,
388 vendor_site_id JAI_CMN_TAXES_ALL.vendor_site_id%type,
389 currency JAI_PO_TAXES.currency%type,
390 rounding_factor JAI_CMN_TAXES_ALL.rounding_factor%type,
391 duty JAI_CMN_TAXES_ALL.duty_drawback_percentage%type,
392 Precedence_1 JAI_PO_TAXES.PRECEDENCE_1%TYPE,
393 Precedence_2 JAI_PO_TAXES.PRECEDENCE_2%TYPE,
394 Precedence_3 JAI_PO_TAXES.PRECEDENCE_3%TYPE,
395 Precedence_4 JAI_PO_TAXES.PRECEDENCE_4%TYPE,
396 Precedence_5 JAI_PO_TAXES.PRECEDENCE_5%TYPE,
397 regime_code varchar2(30),
398 Precedence_6 JAI_PO_TAXES.PRECEDENCE_6%TYPE,
399 Precedence_7 JAI_PO_TAXES.PRECEDENCE_7%TYPE,
400 Precedence_8 JAI_PO_TAXES.PRECEDENCE_8%TYPE,
401 Precedence_9 JAI_PO_TAXES.PRECEDENCE_9%TYPE,
402 Precedence_10 JAI_PO_TAXES.PRECEDENCE_10%TYPE
403 );
404 TYPE tax_cur_type IS REF CURSOR RETURN PO_TAX_CUR;
405 c_po_tax_cur TAX_CUR_TYPE;
406 po_lines_rec c_po_tax_cur%rowtype;
407 --Added by walton for inclusive tax
408 ---------------------------------------
409 type tax_table_typ is
410 table of PO_TAX_CUR index by binary_integer;
411 lt_tax_table tax_table_typ;
412 -----------------------------------------
413 -- end ssumaith - asbn
414
415 -- rchandan start - 6030615(INTERORG_XFER FP )
416
417 Cursor c_rec_ship_txn(cp_ship_line_id IN NUMBER) is
418 select mmt_transaction_id
419 from rcv_shipment_lines
420 where shipment_line_id = cp_ship_line_id;
421
422 r_rec_ship_txn c_rec_ship_txn%rowtype;
423
424 cursor c_get_inv_trx_info(Cp_transaction_id IN NUMBER) IS
425 select abs(transaction_quantity) , transaction_uom , original_transaction_temp_id , prior_cost
426 from mtl_material_transactions
427 where transaction_id = cp_transaction_id ;
428
429 cursor c_jai_mtl_Trxs(cp_trx_temp_id NUMBER) IS
430 select *
431 from jai_mtl_trxs
432 where transaction_temp_id = cp_trx_temp_id;
433
434 r_jai_mtl_Trxs c_jai_mtl_Trxs%rowtype;
435
436 cursor c_jai_cmn_lines(cp_shipment_num VARCHAR2) IS
437 SELECT *
438 FROM jai_cmn_lines
439 WHERE po_line_location_id = p_line_location_id
440 AND shipment_number = cp_shipment_num;
441
442 r_jai_cmn_lines c_jai_cmn_lines%rowtype;
443
444 ln_trx_qty number;
445 lv_trx_uom varchar2(20);
446 ln_orig_id number;
447 ln_item_cost number;
448
449 -- rchandan end - 6030615(INTERORG_XFER FP )
450
451 --------------------------- Procedure For inserting a record in JAI_RCV_LINES ---------
452 PROCEDURE insert_receipt_line IS
453
454 lv_mfg_trading JAI_RCV_LINES.mfg_trading%type ;
455 BEGIN
456
457 --Added by GSRI on 21-OCT-01
458 SELECT COUNT(*) INTO v_chk_receipt_lines
459 FROM JAI_RCV_LINES
460 WHERE shipment_line_id = p_shipment_line_id AND
461 shipment_header_id = p_shipment_header_id;
462 IF v_chk_receipt_lines = 0 THEN
463 /*DELETE FROM JAI_RCV_LINES
464 WHERE shipment_line_id = p_shipment_line_id AND
465 shipment_header_id = p_shipment_header_id;*/
466 --End Addition by GSRI on 21-OCT-01
467
468 lv_mfg_trading := NVL(v_manufacturing, 'N')|| NVL(v_trading, 'N') ; -- Removed minus sign (-) for bug#4519697
469
470 INSERT INTO JAI_RCV_LINES
471 (shipment_line_id,
472 shipment_header_id,
473 receipt_num,
474 qty_received,
475 boe_number,
476 excise_invoice_no,
477 excise_invoice_date,
478 online_claim_flag, -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
479 tax_modified_flag,
480 line_location_id,
481 tax_amount,
482 MFG_TRADING,
483 transaction_id, --Added by Nagaraj.s for Bug#2692052.
484 organization_id,--Added by Nagaraj.s for Bug#2692052.
485 inventory_item_id, -- added by Aparajita for Bug#2813244
486 creation_date,
487 created_by,
488 last_update_date,
489 last_updated_by,
490 last_update_login)
491 VALUES (p_shipment_line_id,
492 p_shipment_header_id,
493 v_receipt_num,
494 p_qty_received,
495 NULL,
496 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. v_attribute1,
497 -- v_attribute2,
498 lv_excise_invoice_no,
499 lv_excise_invoice_date,
500 lv_online_claim_flag,
501 v_receipt_modify_flag,
502 p_line_location_id,
503 NULL,
504 lv_mfg_trading, --NVL(v_manufacturing, 'N')||NVL(v_trading, 'N'), /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
505 p_transaction_id, --Added by Nagaraj.s for Bug#2692052.
506 p_organization_id, --Added by Nagaraj.s for Bug#2692052. This is made as per RCV_TRANSACTIONS.ORGANIZATION_ID so that Joins can be avoided with RCV_TRANSACTIONS
507 v_item_id,-- added by Aparajita for Bug#2813244
508 p_creation_date,
509 p_created_by,
510 p_last_update_date,
511 p_last_updated_by,
512 p_last_update_login);
513 END IF;
514 END insert_receipt_line;
515
516 --------------------------- Procedure For updating tax amount in JAI_RCV_LINES --------
517 PROCEDURE update_receipt_line IS
518 BEGIN
519 IF v_tax_total <> 0
520 THEN
521 UPDATE JAI_RCV_LINES
522 SET tax_amount = v_tax_total
523 WHERE shipment_line_id = p_shipment_line_id;
524 END IF;
525 END update_receipt_line;
526
527 ------------------------------ For picking po_header_date from po_headers -------------------
528 /* Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
529 PROCEDURE pick_po_header_date IS
530 BEGIN
531 FOR date_rec IN (SELECT creation_date,
532 vendor_site_id
533 FROM po_headers_all
534 WHERE po_header_id = p_po_header_id)
535 LOOP
536 v_po_header_date := date_rec.creation_date;
537 v_vendor_site_id := date_rec.vendor_site_id;
538 END LOOP;
539 jai_rcv_utils_pkg.get_div_range(p_vendor_id,
540 v_vendor_site_id,
541 v_range_no,
542 v_division_no);
543 END pick_po_header_date;
544 */
545
546 ------------------ Procedure For updating tax amount in case of internal order ---------------
547 PROCEDURE duplicate_shipment_update IS
548 BEGIN
549 IF p_transaction_type = 'RECEIVE'
550 THEN
551 FOR conf_rec IN (SELECT shipment_line_id,
552 qty_received
553 FROM JAI_RCV_LINES
554 WHERE shipment_line_id = p_shipment_line_id)
555 LOOP
556 v_conf := conf_rec.shipment_line_id;
557 IF NVL(conf_rec.qty_received, 0) <> 0
558 THEN
559 v_cor_quantity := 1 + (p_qty_received / conf_rec.qty_received);
560 END IF;
561 END LOOP;
562 IF v_conf IS NOT NULL
563 THEN
564 v_duplicate_ship := 'Y';
565 FOR lines_rec IN (SELECT rtl.tax_amount,
566 rtl.tax_type,
567 rtl.tax_line_no,
568 jtc.rounding_factor
569 FROM JAI_RCV_LINE_TAXES rtl,
570 JAI_CMN_TAXES_ALL jtc
571 WHERE rtl.shipment_line_id = p_shipment_line_id
572 AND jtc.tax_id = rtl.tax_id)
573 LOOP
574 v_current_tax := ROUND((lines_rec.tax_amount * v_cor_quantity),
575 NVL(lines_rec.rounding_factor, 0));
576 UPDATE JAI_RCV_LINE_TAXES
577 SET tax_amount = v_current_tax
578 WHERE shipment_line_id = p_shipment_line_id
579 AND tax_line_no = lines_rec.tax_line_no;
580 IF lines_rec.tax_type NOT IN ('TDS', 'Modvat Recovery')
581 THEN
582 v_tax_total := NVL(v_tax_total, 0) + NVL(v_current_tax, 0);
583 END IF;
584 END LOOP;
585 UPDATE JAI_RCV_LINES
586 SET qty_received = NVL(qty_received, 0) + p_qty_received,
587 tax_amount = v_tax_total
588 WHERE shipment_line_id = p_shipment_line_id;
589 END IF;
590 END IF;
591 END duplicate_shipment_update;
592
593 --------------------------------- rg i entry -----------------------------------------------
594 /* Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
595 PROCEDURE rg23_i_entry IS
596 BEGIN
597 Ja_In_Receipt_Rg_Pkg.rg23_i_entry (p_shipment_line_id,
598 p_location_id,
599 p_subinventory,
600 p_vendor_id,
601 p_vendor_site_id,
602 p_po_header_id,
603 p_created_by,
604 p_creation_date,
605 p_last_update_date,
606 p_last_updated_by,
607 p_last_update_login,
608 p_qty_received,
609 p_transaction_id,
610 p_transaction_type,
611 p_transaction_date,
612 p_po_line_id,
613 p_line_location_id,
614 v_attribute1,
615 v_attribute2,
616 p_uom_code,
617 p_primary_quantity,
618 v_rg_type);
619 END rg23_i_entry;
620 */
621 --------------------------------- Deciding tax modified flag --------------------------------
622 PROCEDURE set_receipt_flag IS
623
624 /* Start Vijay Shankar for Bug#4199929 */
625 lv_tax_change_on_roi_recpts JAI_CMN_INVENTORY_ORGS.tax_change_on_open_int_recpts%TYPE;
626 lv_tax_change_on_wms_recpts JAI_CMN_INVENTORY_ORGS.tax_change_on_wms_recpts%TYPE;
627 /*lv_mobile_txn_flag rcv_transactions_interface.mobile_txn%TYPE;
628 above declaration fails incase client has a lower BASE VERSION like 11.5.5
629 So modified as below so that the procedure gets compiled. 4252036(4245089)
630 */
631 lv_mobile_txn_flag VARCHAR2(2);
632 lv_open_interace_receipt_flag rcv_transactions_interface.validation_flag%TYPE;
633 lv_dynamic_sql VARCHAR2(1000);
634 lv_profile_val VARCHAR2(10);
635
636 CURSOR c_interface_trx_dtl(cp_interface_transaction_id IN NUMBER) IS
637 /* SELECT nvl(validation_flag, 'N') validation_flag commented by Vijay Shankar for bug#4240265 */
638 SELECT decode(header_interface_id, null, 'N', 'Y') imported_receipt_flag
639 FROM rcv_transactions_interface
640 WHERE interface_transaction_id = cp_interface_transaction_id;
641 /* End Vijay Shankar for Bug#4199929 */
642
643 CURSOR c_iproc_profile IS
644 SELECT fnd_profile.value('JA_ACCESS_IPROC_TAX')
645 FROM DUAL;
646
647 ln_user_id NUMBER := fnd_global.user_id;
648
649
650 BEGIN
651 jai_rcv_utils_pkg.get_rg1_location (p_location_id,
652 p_organization_id,
653 p_subinventory,
654 v_rg_location_id);
655 FOR rec_upd_rec IN (SELECT nvl(receipt_modify_flag, 'N') receipt_modify_flag,
656 nvl(trading, 'N') trading,
657 nvl(manufacturing, 'N') manufacturing
658 /* following added by Vijay Shankar for Bug#4199929 */
659 , nvl(tax_change_on_open_int_recpts, 'N') tax_change_on_open_int_recpts
660 , nvl(tax_change_on_wms_recpts, 'N') tax_change_on_wms_recpts
661 FROM JAI_CMN_INVENTORY_ORGS
662 WHERE organization_id = p_organization_id
663 AND location_id = v_rg_location_id)
664 LOOP
665 v_receipt_modify_flag := rec_upd_rec.receipt_modify_flag;
666 v_trading := rec_upd_rec.trading;
667 v_manufacturing := rec_upd_rec.manufacturing;
668 /* following added by Vijay Shankar for Bug#4199929 */
669 lv_tax_change_on_roi_recpts := rec_upd_rec.tax_change_on_open_int_recpts;
670 lv_tax_change_on_wms_recpts := rec_upd_rec.tax_change_on_wms_recpts;
671 END LOOP;
672
673 /* Start, Vijay Shankar for Bug#4199929 */
674 /* following is written to give control to clients so that for open interface receipts the value returned is 'Y' */
675 lv_dynamic_sql := 'select nvl(mobile_txn, ''N'') mobile_txn FROM rcv_transactions_interface WHERE interface_transaction_id = :1';
676 BEGIN
677 execute immediate lv_dynamic_sql into lv_mobile_txn_flag using p_interface_transaction_id;
678 EXCEPTION
679 WHEN OTHERS THEN
680 lv_mobile_txn_flag := 'N';
681 END;
682
683 OPEN c_interface_trx_dtl(p_interface_transaction_id);
684 FETCH c_interface_trx_dtl INTO lv_open_interace_receipt_flag;
685 CLOSE c_interface_trx_dtl;
686
687 OPEN c_iproc_profile;
688 FETCH c_iproc_profile INTO lv_profile_val;
689 CLOSE c_iproc_profile;
690
691 IF v_debug_flag = 'Y' THEN
692 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of lv_profile_val (profile value) is ' || lv_profile_val);
693 END IF;
694
695 lv_profile_val := fnd_profile.value_specific(NAME =>'JA_ACCESS_IPROC_TAX',user_id=>ln_user_id);
696
697 IF v_debug_flag = 'Y' THEN
698 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of lv_profile_val (for userid) is ' || lv_profile_val);
699 END IF;
700
701 IF NVL(lv_profile_val,'2') = '2' Then
702 lv_profile_val := 'N';
703 ELSE
704 lv_profile_val :='Y';
705 END IF;
706
707 IF v_debug_flag = 'Y' THEN
708 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of lv_profile_val (as Y/N) is ' || lv_profile_val);
709 END IF;
710
711 FND_FILE.put_line(fnd_file.log, 'ROI:'|| lv_tax_change_on_roi_recpts
712 ||', WMS:'||lv_tax_change_on_wms_recpts
713 ||', Receipts:'||v_receipt_modify_flag
714 ||', TxnROI Flg:'||lv_open_interace_receipt_flag
715 ||', TxnMobile Flg:'||lv_mobile_txn_flag
716 );
717
718 /* following if condition is for WMS Receipts */
719 IF lv_mobile_txn_flag = 'Y' THEN
720 IF lv_tax_change_on_wms_recpts = 'Y' THEN
721 v_receipt_modify_flag := 'Y';
722 ELSE
723 --v_receipt_modify_flag := 'X';
724 --commented the above and added the below by Ramananda for Bug#4519697
725 v_receipt_modify_flag := 'N';
726 END IF;
727
728 IF v_debug_flag = 'Y' THEN
729 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_receipt_modify_flag after WMS Receipts loop is ' || v_receipt_modify_flag);
730 END IF;
731
732 /* following if condition is for Open Interface Receipts */
733 ELSIF lv_open_interace_receipt_flag = 'Y' THEN
734 IF lv_tax_change_on_roi_recpts = 'Y' THEN
735 v_receipt_modify_flag := 'Y';
736 ELSE
737 --v_receipt_modify_flag := 'X';
738 --commented the above and added the below by Ramananda for Bug#4519697
739 v_receipt_modify_flag := 'N';
740 END IF;
741
742 IF v_debug_flag = 'Y' THEN
743 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_receipt_modify_flag after Open Interface Receipts loop is ' || v_receipt_modify_flag);
744 END IF;
745
746 ELSE
747
748 IF v_receipt_modify_flag = 'N' THEN
749 -- added, CSahoo for Bug 5344225
750 -- iSupplier Porting
751 /*
752 || Start additions by ssumaith for Iprocurement.
753 */
754 IF lv_apps_source_code = 'POR' AND p_transaction_type = 'RECEIVE'
755 AND lv_profile_val = 'N' then
756 /* Rcpt Created thru Iproc and user does not have change the taxes on rcpt
757 || In such a case setting the receipt_modify_flag to 'N'
758 */
759 v_receipt_modify_flag := 'N';
760
761 END IF;
762 /*
763 || End additions by ssumaith - Iprocurement.
764 */
765 -- iSupplier Porting
766 -- v_receipt_modify_flag := 'N';
767 /* added by csahoo for bug#6209911 */
768 IF v_trading = 'Y' THEN
769 v_receipt_modify_flag := 'Y';
770 END IF;
771 ELSE
772 /*
773 || Start additions by ssumaith for Iprocurement.
774 || user did not navigate from the receipts localised form because
775 || the rcpt was created from iproc and user has right to change the taxes
776 */
777
778 IF lv_apps_source_code = 'POR' AND p_transaction_type = 'RECEIVE'
779 THEN
780 IF lv_profile_val = 'Y' then
781 /* iSupplier Porting
782 || End additions by ssumaith - Iprocurement.
783 */
784 v_receipt_modify_flag := 'Y';
785 ELSE
786 v_receipt_modify_flag := 'N';
787 END IF;
788 END IF;
789 /* iSupplier Porting
790 || End additions by ssumaith - Iprocurement.
791 */
792
793 /* R12-PADDR
794 IF v_chk_form IS NOT NULL THEN
795 v_receipt_modify_flag := 'Y';
796 ELSE
797 v_receipt_modify_flag := 'N';
798 END IF;
799 R12-PADDR */
800 -- v_receipt_modify_flag := 'Y';
801 END IF;
802
803 END IF;
804 /* END, Vijay Shankar for Bug#4199929 */
805
806 FND_FILE.put_line(fnd_file.log, 'Final ReceiptModifyFlag:'||v_receipt_modify_flag);
807
808 /* following is commented by Vijay Shankar for Bug#4159557
809 Added by Nagaraj.s for Bug2915829.
810 IF v_receipt_modify_flag ='N' THEN
811 v_receipt_modify_flag :='X'; --To Indicate that the Flag Value is to be Processed.
812 END IF;
813 */
814
815 END set_receipt_flag;
816
817 ---------------------------------------------------------------------------
818 PROCEDURE pick_register_type (p_organization_id number,
819 p_item_id number,
820 p_register_type out nocopy varchar2) IS
821 v_register_type JAI_CMN_RG_23AC_I_TRXS.register_type % TYPE;
822 v_item_class JAI_INV_ITM_SETUPS.item_class % type;
823 BEGIN
824 For reg_rec IN (SELECT item_class
825 FROM JAI_INV_ITM_SETUPS
826 WHERE organization_id = p_organization_id
827 AND inventory_item_id = p_item_id)
828 LOOP
829 v_item_class := reg_rec.item_class;
830 END LOOP;
831 IF v_item_class IN ('RMIN', 'RMEX', 'CCIN', 'CCEX','FGIN','FGEX')
832 THEN
833 v_register_type := 'A';
834 ELSIF v_item_class IN ('CGIN', 'CGEX')
835 THEN
836 v_register_type := 'C';
837
838 END IF;
839 p_register_type := v_register_type;
840 END pick_register_type;
841 ---------------------------------------------------------------------------
842
843 /* ***************************** MAIN BEGIN ***************************** */
844
845 BEGIN
846 /*----------------------------------------------------------------------------------------------------------------------
847 FILENAME: ja_in_receipts_p.sql
848 CHANGE HISTORY:
849
850 S.No Date Author and Details
851 -----------------------------------------------------------------------------------------------------------------------
852 1 17/06/02 Changed by Nagaraj.s on 17/06/2002 for Bug#2417290...............
853 Previously the Coding was- AND sla.orig_sys_line_ref = prla.line_num)
854 Changed Coding is AND sla.orig_sys_line_ref = to_char(prla.line_num))
855 Since the LHS of the expression was a varchar2 and Right Hand side of the Expression was a Number.
856 Hence the Invalid Number exception would occur when sla.orig_sys_line_ref is not a number........
857 Hence the RHS of the expression is also converted into To_Char before the comparison is made.
858
859
860 2. 18/06/2002 Aparajita for bug 2415767
861 When currency conversion rate is changes at receipt level from the rate defined at PO,
862 all the taxes that are with precedences need to be recalculated as the tax amount changes.
863 Added the code for recalculation of receipt tax in such situation.
864
865 3. 22/07/2002 Additional documentation for bug # 2415767 by Aparajita on 22/07/2002.
866 This procedure is dependent on the modified structure of table JA_IN_TEMP_RECEIPT. Ensure that
867 SHIPMENT_HEADER_ID NUMBER (15) and
868 PROCESS_FLAG VARCHAR2 (1)
869 columns are present in the table JA_IN_TEMP_RECEIPT in the client instance.
870
871 4. 18/08/2002 In Case of an Direct Delivery, the receipt_routing column in Purchase Order
872 is stored with an Value of 3. The For Passing Accounting Entries will fire
873 and takes care of receiving and delivery entries and hence it is
874 necessary that, the to handle Deliver RTR RTV should not fire
875 when it is a direct delivery and this is handled with the condition(V_RECEIPT_ROUTING<>3)
876 and To handle Deliver RTR RTV should fire only in case
877 of standard and inspection routing(Receipt_routing is 1 or 2).
878
879 5. 21/08/2002 Changed by Sriram for Bug # 2514719 . In the procedure , changed the else condition that was the else for RMA
880 Because of the Else , if an internal sales order cycle is done , the control flows into the
881 else ,which should happen only in case of a PO cycle. Hence the else was replaced by
882 elsif with PO
883
884 6. 23/08/2002 Changed by Nagaraj.s for Bug#2525910
885 Incorporated an parameter P_ROUTING_HEADER_ID which is referred to before the
886 concurrent call for To Handle Deliver RTR RTV. Also removed the cursor get_receipt_routing
887 as this is referred in parameters and there is no need to fetch the same.
888
889
890 7 27/08/2002 Changed by Nagaraj.s for Bug2531667
891 The condition for fetching UOM has been incorporated with one more "and" condition
892 AND pla.po_line_id = p_po_line_id
893 which gets the UOM for that PO Line as previously the join was not proper and hence
894 the UOM conversion was having a value of zero which resulted in tax amounts being
895 calculated as zero.
896
897 8 25/09/2002 Changed by Nagaraj.s for Bug2588096
898 In case of an Unordered Receipt, there was a call to rg23_part_i_entry procedure
899 which in turn makes a call to ja_in_receipt_rg_pkg which had a select statement
900 from RCV_Transactions, which resulted in Mutation Error. Also, according to the URM
901 no RG Entries should happen at the point of Unordered Receipts and hence the coding
902 which was previously calling rg23_part_i_entry is commented and this has resulted in
903 Mutation Trigger being avoided. And also the same coding has been written in
904 the case where Transaction Type is Match. And also previously Tax Lines were inserted
905 for source_document_code='PO' but this can also go into an error as in case of UNORDERED
906 receipt, source_document_code will be PO, but Transaction Type will be Unordered and hence
907 this condition is also incorporated.
908
909 9. 21/11/2002 Changed by Nagaraj.s for Bug # 2659628 Version - 615.8
910 Receipt Taxes are recalculated irrespective of whether
911 there is a currency change or not so that rounding does
912 not happen on the higher side in both PO and Receipt.
913
914
915 10. 04/03/2003 By Nagaraj.s Version - 615.9
916
917 1. For Bug # 2692052
918 Commented the calls to ja_in_temp_receipt
919 Changed the calls to "TO HANDLE DELIVER RTR RTV"
920 Changed the Insert of JAI_RCV_LINES with 2 columns ( organization_id,transaction_id).
921
922 2. For Bug # 2808110
923 Added an Order by Clause in the Fetching
924 of Tax related Information from JAI_PO_TAXES
925 as this is very critical in the Precedence Logic Incorporated.
926
927 3. For Bug # 2798999 - Generic Fix for the One Off Patch.
928 Added the call to jai_rcv_utils_pkg.get_organization_id
929 to ensure that Organization Id and Item Id is picked up as this
930 is critical in INV_CONVERT.INV_UM_CONVERSION to calculate the UOM
931 Rate.
932
933 11. 10/03/2003 Aparajita for bug # 2813244. Version 615.11
934
935 For 'INTERNAL SALES ORDER', if the requisition has lines in uom other than the primary uom, then after the shipment when receipt is made in the destination organization, the taxes are not getting calculated properly.
936
937 The reason was that the uom conversion was as follows,
938 - uom from requisition and quantity from delivery
939 - uom from receipt and quanitity from receipt.
940
941 The quantity in delivery is always in primary uom where as the requisition is not in primary uom. The apportionment was hence going wrong.
942
943 Version 615.10 is obsoleted as wrong file was checked in.
944
945 12. 22/03/2003 Nagaraj.s for Bug # 2915829. Version : 615.12
946
947 The Tax Modified Flag is set through set_receipt_flag Procedure.
948 This is changed to populate the value as 'X' in case it is 'N'.
949
950 Hence Now the Tax Modified Flag will have 3 possible Values.
951
952 1. 'Y' - Which Indicates that Taxes can be changed at Receipt Level.
953 This Value will Invoke For Passing Accounting Entries Program
954
955 2. 'N' - Which Indicates that Taxes can no longer be changed at Receipt Level.
956 This Value will ensure that For Passing Accounting Entries will not be Invoked.
957
958 3. 'X' - Which Indicates that Taxes cannot be changed at Receipt Level,
959 But still this Ensures that For Passing Accounting Entries Program is Invoked
960 on Closing the Receipts Localized screen.
961
962 Hence, For Passing Accounting Entries Concurrent gets Invoked as Long as this Flag
963 has an value of 'X' or 'Y'. And For Passing Procedure Updates this to 'N' after
964 Processing. This Prevents Invoking of For Passing Accounting Entries Concurrent
965 Multiple Times.
966
967 13. 02/05/2003 Aparajita for bug # 2929171. Version#615.13
968 Taxes were not getting calculated properly when,
969 - uom is changed at receipt
970 - taxes have non zero precedences, that is tax on tax.
971
972 The problem was because of uom conversion being applied to non zero precedence taxes.
973 The tax lines are always calculated in the order of tax line number. When uom is changed,
974 only the tax precedence 0 undergoes the change. This conversion should not be applied to
975 precedence 1 as, when tax line 1 was calculated, that is already taken care of.
976
977 14. 08/05/2003 Nagaraj.s for Bug#2915783. Version#615.14
978 The Initialization of v_item_modvat_flag and v_item_trading_flag was not done at the
979 proper place as a result of which Modvat_flag of JAI_RCV_LINE_TAXES
980 was not populated properly. This has been moved to a location which is proper,
981 so that Proper comparison happens.
982
983 15. 13/05/2003 Vijay Shankar for bug # 2943558. Version#615.15
984 When a Receipt is made against a SCHEDULED Release (partial quantity) of
985 PLANNED PO, then taxes are calculated for whole of the PLANNED PO line quantity
986 instead of SCHEDULED Release quantity.
987 The issue is occuring because, precedence_0 is calculated as unit_price * quantity
988 of PO_LINES_ALL instead of PO_LINE_LOCATIONS_ALL. Code is modified to calculate
989 precedence_0 as PRICE_OVERRIDE * QUANTITY of PO_LINE_LOCATIONS_ALL instead of using
990 PO_LINES_ALL
991
992 16. 06/06/2003 Nagaraj.s for Bug #2991872. Version : 616.1
993 Code is added to ensure that if Assessable price is defined for Excise Taxes, then
994 the Assessable Price is picked up instead of the unit price from po_line_locations_all
995 table.
996
997 17. 21/07/2003 Vijay Shankar for Bug# 3028040, Version : 616.2
998 Code is added to check for EXPRESS transaction and then stop 'For Passing' to fire for every RECEIVE transaction
999 of EXPRESS Receipt. This change is made as the PADDR concept is removed in RCV_SHIPMENT_HEADERS DFF.
1000
1001 18. 29/07/2003 Nagaraj.s for Bug #2993865 . Version#:616.3
1002 The two queries which were written earlier to fetch line_id,transaction_curr_code,
1003 delivery_detail_id have been merged to form one query.
1004 The join is now changed to
1005 order. source document id = requisition.requisition header id
1006 order line.source document line id = requisition line. requisition line id.
1007
1008 19. 30/07/2003 Nagaraj.s for Bug#3037075. Version#:616.4
1009 The Vendor site id is now fetched in the lines_rec cursor and
1010 the same is populated into JAI_RCV_LINE_TAXES table.
1011 This change is applicable for only PO type of Transaction. Huge Dependency
1012
1013 20. 22/08/2003 Nagaraj.s for Bug#3057752. Version#:616.5
1014 The changes are as below:
1015 An Raise Application Error is written to ensure that RTR Transactions should not happen
1016 if neither claim nor unclaim is done.
1017
1018 21. 31/10/2003 Nagaraj.s for Bug # 3123778, File Version : 616.6
1019 The check for CGIN,CGEX items for 100% claim is removed as per
1020 the Functional Requirement.
1021
1022 22. 03/11/2003 Nagaraj.s for Bug # 3202319 File Version : 616.7
1023 In case of FGIN,FGEX Item Classes, the Claim percentage should not hold good.
1024 Hence the cursor mod_rec is added with Item class FGIN,FGEX and the same
1025 is incorporated in the IF Condition, which validates for 100% cenvat claim.
1026
1027 23. 05/11/2003 Nagaraj.s for Bug3237536 File Version : 616.8 (IN60105D2)
1028 Added the condition v_tax_vendor_site_id := null as this was leading to Vendor
1029 site id being populated in all cases irrespective of the type of Tax.
1030
1031 24. 02/06/2004 ssumaith - bug# 3657662 - File Version 115.1
1032
1033 There was a performance problem with a query . The issue was it was taking time because
1034 of a full table scan on the oe_order_headers_all table.
1035
1036 Issue was resolved by retreiving the order_header_id from the JAI_OM_WSH_LINES_ALL
1037 based on delivery id and adding a where clause to qualify the order_header_id.
1038
1039
1040 25. 04/06/2004 Nagaraj.s - Bug # 3655330 - File Version : 115.1
1041 In case of RTR and RTV scenarios, check has been made for either
1042 claim or unclaim but in this case neither claim or unclaim happens and hence,
1043 added one more condition :v_non_bonded_delivery_flag = 'N'
1044 so that RTR and RTV will be done without any problems.
1045
1046 26. 18/06/2004 ssumaith - bug# 3683666 - File Version 115.2
1047
1048 When a RMA receipt is done without navigating from Receipts-localised form and not entering
1049 the values in the DFF of RCV_SHIPMENT_HEADERS or RCV_TRANSACTIONS, it was observed that
1050 taxes are not defaulted into localization tables (JAI_RCV_LINE_TAXES).
1051 The reason this was happening is because the if condition which was checking this to be a
1052 RMA receipt was also checking the chk_form to be not null with an "AND" to other required
1053 conditions. Hence the problem
1054
1055 This issue has been fixed by doing making the v_chk_form check with 'OR' rather than 'AND'
1056
1057 Dependecny due to this bug - None
1058
1059 27. 22/07/2004 ssumaith - bug# 3772135 file version 115.3
1060
1061 When two internal internal orders created out of two internal requisitions are
1062 merged into a single delivery ,and a receipt created for the delivery, it was
1063 causing the taxes only for the first line to be populated.
1064
1065 28. 28/08/2004 Nagaraj.s for Bug#3858917 File Version : 115.4
1066 In valid number error was occuring as the shipment number was entered as
1067 alpha numeric and the condition was entering into the ISO route the comparison
1068 for the shipment num with the delivery id in the cursor c_order_cur
1069 was going into this error.
1070
1071 Ideally the condition :
1072 ========================================================
1073 v_receipt_source_code = 'INTERNAL ORDER' OR -- AND
1074 v_chk_form IS NOT NULL AND
1075 ========================================================
1076 is wrong as in case of PO and RMA also this enters the code
1077 and as the shipment num can be entered as any value the comparison is wrong.
1078 In any case, this code should get executed for Internal Order
1079 and hence the condition of v_chk_form is not needed.
1080 Hence this is commented. This will be fixed as part of the generic
1081 fix for the bug3848133 as this object is being changed.
1082
1083 29. 24/08/2004 Nagaraj.s - Bug# 3848133 (BaseBug# 3496408). File version : 115.4
1084
1085 The code now fetches the Precedences from the following points for the following sources:
1086 ----------------------------------------------------------------------------------------
1087 source type Source Table
1088 RMA JAI_OM_OE_RMA_TAXES
1089 PO JAI_PO_TAXES
1090 ISO JAI_OM_WSH_LINE_TAXES
1091 ----------------------------------------------------------------------------------------
1092 In these 3 cases a insert into JAI_RCV_LINE_TAXES is present and this is,
1093 changed to incorporate precedences as well as transaction id.
1094
1095 30. 05/11/2004 Vijay Shankar - Bugs#3949408. File version : 115.5
1096 Commented the redundant code for MATCH processing
1097
1098 31. 14/10/2004 ssumaith - Bug# 3878439 File Version - 115.6
1099
1100 When a delivery which consists of multiple sales orders is split into multiple delivery
1101 details at the shipment level only and not at sales order level, for each delivery detail
1102 a shipment line is being created at the receipt level.
1103
1104 As there is no link available between the receipt line and delivery detail at the delivery
1105 detail level, we are unable to exactly apportion the taxes.
1106
1107 devised approach to get the delivery detail based on the shipment line id also ini addition
1108 to the other clauses.
1109
1110 This change has been done in the cursor which fetches the delivery details based on the order and
1111 receipt details.
1112
1113 32 03/01/2005 Vijay Shankar for Bug# 3940588, Version:115.7
1114 Modified the code to execute only for RECEIVE, UNORDERED, MATCH and RETURN TO VENDOR transactions.
1115 Functionality for all the other transactions will be handled through ja_in_receipt_transaction_pkg, which is
1116 coded for Receipts Corrections functionality and from now on will be used for all Localization Receipts
1117 functionality as a gateway.
1118 - added a new parameter p_chk_form (OUT Variable) to return back a value, if the receipt transaction that is
1119 begin processed is created after navigating through Localization form
1120 - Instead of submitting the concurrents JAINRVCTP is submitted for processing
1121 - JAI_RCV_LINES.tax_modified_flag will be set now from above mentioned package when submitted from JAINPORE
1122 - Added Validation at the end of Procedure to error out if this is an RTV and Cenvat is not yet Claimed
1123
1124 ** Please refer to Old Version of the Object incase commented code needs to be looked at **
1125
1126 33 09/02/2005 Vijay Shankar for Bug# 4159557, Version:115.8
1127 Modified the code to assign proper value to JAI_RCV_LINES.tax_modified_flag column based on Receipts
1128 tax modification Value returned by Localization Hook given to customers. the hook is called in ja_in_receipt_tax_insert_trg
1129 trigger and returned value is passed as parameter to this procedure which is used for tax_modified_flag value
1130 determination
1131
1132 * This is a dependancy for Future Versions of the procedure *
1133
1134 34 22/02/2005 Vijay Shankar for Bug# 4199929, Version:115.9
1135 Changes made in the previous version are modified to use new setup at Organization Addl Information instead of
1136 value returned by Hook
1137 Changes are made in internal procedure set_receipt_flag
1138
1139 * This is a dependancy for Future Versions of the procedure *
1140
1141 35 12/03/2005 Bug 4210102. resolved by LGOPALSA Version: 115.10
1142 (1) Added CVD, Excise and customs education cess
1143
1144 36 19/03/2005 Vijay Shankar for Bug#4250236(4245089). FileVersion: 115.12
1145 .Implemented VAT Tax Calculation based on VAT Assessable value by making a call to jai_general_pkg.ja_in_vat_assessable_value
1146 .JAI_RCV_LINE_TAXES.modvat_flag is set to proper value for VAT Taxes based on RECOVERABLE item attribute
1147 of item. If item setup is not done, then Default value is taken as "Y" (meaning, tax is recoverable)
1148 .modified the main SQL's to fetch taxes from various sources to use jai_regime_tax_types_v to fetch regime_code
1149 against each tax so that the information can be used for MODVAT_FLAG setting of tax
1150 .RTV will raise an exception incase recoverable VAT exists in the receipt line and it is neither Claimed or
1151 Unclaimed
1152
1153 37 07/04/2005 Harshita for Bug #4285064 Version : 116.0(115.13)
1154
1155 When a user creates a new receipt against a purchase order, he needs to enter the following information
1156 through a DFF : invoice no, invoice_date, Claim Cenvat On Receipt etc.
1157 This DFF is provided at two places, header and line.
1158 Information from the header DFF is captured into the rcv_shipment_headers table.
1159 Information from the lines DFF is captured into the rcv_transactions table.
1160 This information is retrieved into our base tables JAI_RCV_TRANSACTIONS and JAI_RCV_LINES.
1161 At this time, a facility has been provided for the user to default the information
1162 given at the header level DFF to all the lines only if these columns are null at the
1163 line level. Else the information in the line level DFF is sustained.
1164 For this NVL conditions have been added where this information gets defaulted.
1165
1166 38. 10/05/2005 Vijay Shankar for Bug#4346453. Version: 116.1
1167 Code is modified due to the Impact of Receiving Transactions DFF Elimination
1168
1169 * High Dependancy for future Versions of this object *
1170
1171 39. 08-Jun-2005 File Version 116.2. Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
1172 as required for CASE COMPLAINCE.
1173
1174 40 13-Jun-2005 Ramananda for bug#4428980. File Version: 116.3
1175 Removal of SQL LITERALs is done
1176
1177 41 27-Jul-2005 Bug 4516678. Added by Lakshmi Gopalsami, File Version 120.2
1178 Issue :
1179 a.Whenever a user creates a receipt for a CGIN or
1180 CGEX item, 50% cenvat is claimed. If he/she intends to
1181 return the entire quantity in the receipt, he/she must
1182 claim the remaining 50% cenvat first and then do the
1183 RTV. Else, the system should throw an error.
1184
1185 b.After creating a receipt for a CGIN or CGEX item,
1186 if the user does a partial RTV on that receipt,
1187 the system should allow it although the remaining
1188 50% CENVAT has not been claimed.
1189
1190 Fix :
1191 a. Added code to check this in Package jai_rcv_tax_pkg
1192 (1) Created new procedure pick_register_type to get the
1193 register_type depending on the item_class
1194 (2)Created two new cursors c_fetch_receive_quantity
1195 and c_fetch_transaction_Quantity to get the
1196 quantity received for the receipt and RTV transactions
1197 (3) Added nvl(cenvat_amount,0) in
1198 cursor c_fetch_unclaim_cenvat
1199 b. The cenvat receivable accounts were not getting passed
1200 in case of a CGIN/CGEX item.
1201 Fixed this by passing these values.
1202 Commented the generic assignment for cenvat
1203 accounting entries and added the condition for
1204 CGIN and CGEX item class in procedure
1205 accounting_entries.
1206
1207 42 01-Aug-2005 Ramananda for bug#4519697, File Version 120.3
1208 Changed the value being assigned to variable - v_receipt_modify_flag from X to N at 2 places
1209 As a part of this bug, the minus sign which got introducted during 'Removal of SQL Literals' is removed
1210
1211 Dependency due to this Bug
1212 --------------------------
1213 jai_rcv_trx_prc.plb (120.3)
1214 jai_rcv_rt_t1.sql (120.2)
1215
1216 43 01-Aug-2005 Ramananda for bug#4530112. File Version 120.4
1217 Problem
1218 -------
1219 In case of RTV, if VAT Claim is not done, system is giving error
1220
1221 Fix
1222 ---
1223 1) Commented the Condition -
1224 "IF lv_vat_recoverable_for_item = jai_constants.yes
1225 AND NVL(ln_chk_vat, 0) <> 0
1226 AND r_rcv_rgm_dtl.process_status_flag <> 'U' --Not Unclaimed
1227 AND r_rcv_rgm_dtl.invoice_no IS NULL"
1228
1229 Dependency Due to this Bug -
1230 File jai_rcv_rgm_clm.plb (120.2) is changed as part of this Bug,
1231 so this object is dependent on current Bug and object jai_rcv_rgm_clm.plb (120.2)
1232
1233 44 05-Aug-2005 Ramananda for Bug#4533114, File Version 120.5
1234
1235 1) Added a new cursor - c_hdr_attribute5_1
1236 2) Added a new begin end part and fetched the values from cursor - c_hdr_attribute5_1
1237 3) In the cursor - line_rec, changed the condition -
1238 "and spl.delivery_id = rsh.shipment_num"
1239 to
1240 "and spl.delivery_id = decode(ltrim(translate(shipment_num,'0123456789','~'),'~'),NULL,
1241 rsh.shipment_num,(select delivery_id from wsh_new_deliveries where name=rsh.shipment_num))"
1242
1243 44 19-Aug-2005 Ramananda for Bug#4562844, File Version 120.6
1244 Problem
1245 -------
1246 System is creating receiving accounting entry and generating tax invoice against disable taxes
1247
1248 Fix
1249 ---
1250 1) In the cursor for selecting the taxes from JAI_PO_TAXES,
1251 changed the columns tax_rate, qty_rate and tax_amount
1252
1253 Dependency due to this Bug-
1254 None
1255
1256 45. 24-Aug-2005 Bug4568090. Added by Lakshmi Gopalsami Version 120.7
1257 Added check for trading items to set the modvat_flag
1258 For trading items the modvat flag should be set to 'Y'
1259 on tax lines if the item_trading_flag at item level is 'Y'
1260 and modvat percentage is specified in the taxes
1261
1262 46. 02/11/2006 For Bug 5228046, File version 120.9
1263 Forward porting the change in 11i bug 5365523 (Additional CVD Enhancement).
1264 This bug has datamodel and spec changes.
1265
1266 47. 21/02/2007 CSahoo for Bug#5344225, File Version 120.10
1267 Forward Porting of 11i Bug#5343848.
1268 Issue :
1269 India - Receiving Transaction Processor Concurrent Program was called
1270 for each transaction on a shipment line.
1271 Fix :
1272 In the procedure jai_rcv_tax_pkg,
1273 Added code to set the Tax Modified Flag based on whether the Call was
1274 made from India - Receipts Localized Form or not.
1275 48. 13/04/2007 bduvarag for the Bug#5989740, file version 120.11
1276 Forward porting the changes done in 11i bug#5907436
1277 49. 13/04/2007 bduvarag for the Bug#4644524, file version 120.11
1278 Forward porting the changes done in 11i bug#4593273
1279
1280 50. 27/06/2007 CSahoo for bug#6154234, File Version 120.12
1281 commmented the line v_receipt_modify_flag = 'N'
1282
1283
1284 Dependency Due to this Bug : Yes.
1285 51. 16-07-2007 iSuppleir forward porting
1286 Changed shipment_num to shipment_number
1287 excise_inv_num to excise_inv_number in jai_cmn_lines table
1288
1289 52. 01-08-2007 rchandan for bug#6030615 , Version 120.17
1290 Issue : Inter org Forward porting
1291
1292 53. 09/10/2007 CSahoo for bug#6209911, File Version 120.20
1293 Added a IF block in the set_receipt_flag procedure.
1294
1295 54. 15/10/2007 bgowrava for Bug#6459894, File Version 120.22
1296 Uncommented statements which were wrongly commented.
1297
1298 55. 16/10/2007 rchandan for bug#6504410, File Version 120.23
1299 Issue : R12RUP04.I/ORG.QA.ST1:RTP GOING INTO ERROR FOR MFG-MFG INTRANSIT RCPT WITH VAT
1300 Fix : Few utl_file debugs were added in previous version without checking for the v_debug_flag
1301 Added the check now.
1302
1303 56. 16/10/2007 bgowrava for Bug#6459894, File Version 120.24
1304 removed the lv_object_name from the JAI_PROCESS_MESSAGE of JAI_EXCEPTION
1305 to avoid truncation of the error message text.
1306 57. 01/01/2007 Walton for Inclusive Tax Computation
1307
1308 58. 05/03/2008 Kevin Cheng for bug 6853787
1309 Add a condition to prevent receipt type like RMA from trapping into the tax calculation loop.
1310
1311 59. 08/04/2008 JMEENA for Bug#6917520, File Version 120.27
1312 Assigned the error message to variable 'errormsg' and printed the message in exception section.
1313
1314
1315
1316 ===============================================================================================================
1317 Bug Number Dependency
1318 3037075 JAI_RCV_LINE_TAXES has been altered.
1319
1320 3057752 Tables : JAI_RCV_CENVAT_CLAIMS, ja_in_temp_mod_params, ja_in_batch_claim_modvat have been
1321 altered.
1322
1323 3123778 JAI_RCV_CENVAT_CLAIMS has been altered to add the column partial_cenvat_claim
1324
1325 4210102 IN60106 + 4239736 + 4245089
1326 ==============================================================================================================
1327
1328
1329 Dependencies For Future Bugs
1330 -------------------------------------
1331 IN60104d + 3037075
1332 IN60104d + 3037075 + 3057752
1333 IN60104d + 3037075 + 3057752 + 3123778
1334
1335 IN60105D2 + 3655330 + 3848133
1336
1337 IN60106 + 3940588 + 4239736 + 4245089 + 4346453
1338
1339 ----------------------------------------------------------------------------------------------------------------------*/
1340
1341 --Added by Nagaraj.s for Bug#2499017
1342 /*OPEN get_receipt_routing;
1343 FETCH get_receipt_routing into v_receipt_routing;
1344 CLOSE get_receipt_routing;*/
1345 --Ends here........
1346
1347 --File.Sql.35 Cbabu
1348 -- v_form_id := 'JAINPORE';
1349 v_tax_total := 0;
1350 v_duplicate_ship := jai_constants.no;
1351 v_chk_receipt_lines :=0;
1352 v_chk_receipt_tax_lines :=0;
1353 v_precedence_0 :=0;
1354 v_precedence_non_0 :=0;
1355 v_tax_base :=0;
1356 v_debug_flag := jai_constants.no;
1357 v_price_override :=0;
1358 v_po_quantity :=0;
1359 v_assessable_value :=0;
1360 ln_vat_assess_value :=0;
1361
1362
1363 BEGIN
1364 SELECT DECODE(SUBSTR (value,1,INSTR(value,',') -1),NULL,
1365 Value,SUBSTR (value,1,INSTR(value,',') -1))
1366 INTO v_utl_location
1367 FROM v$parameter
1368 WHERE name = 'utl_file_dir';
1369 EXCEPTION
1370 WHEN OTHERS THEN
1371 v_debug_flag := 'N';
1372 END;
1373
1374
1375 IF v_debug_flag = 'Y' THEN
1376 v_myfilehandle := UTL_FILE.FOPEN(v_utl_location,'ja_in_receipts_p3.log','A');
1377 UTL_FILE.PUT_LINE(v_myfilehandle,'************************Start************************************');
1378 UTL_FILE.PUT_LINE(v_myfilehandle,'The Time Stamp this Entry is Created is ' ||TO_CHAR(SYSDATE,'DD/MM/RRRR HH24:MI:SS'));
1379 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of p_transaction_type is ' || p_transaction_type);
1380 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of p_transaction_id is ' || p_transaction_id);
1381 END IF;
1382
1383 IF p_currency_code IS NULL THEN
1384 /* Bug 5243532. Added by Lakshmi Gopalsami
1385 * Removed cursor org_rec and implemented caching logic.
1386 */
1387 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
1388 (p_org_id => p_organization_id );
1389 v_currency_code := l_func_curr_det.currency_code;
1390
1391 ELSIF p_transaction_type = 'MATCH' THEN
1392
1393 FOR po_rec IN (SELECT currency_code,
1394 rate
1395 FROM po_headers_all
1396 WHERE po_header_id = p_po_header_id)
1397 LOOP
1398 v_currency_code := po_rec.currency_code;
1399 v_currency_conversion_rate := po_rec.rate;
1400 END LOOP;
1401
1402 ELSE
1403
1404 v_currency_code := p_currency_code;
1405
1406 END IF;
1407
1408 IF v_debug_flag = 'Y' THEN
1409 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_currency_code is ' || v_currency_code);
1410 END IF;
1411
1412 -- iSupplier porting
1413 If p_line_location_id IS NOT NULL then
1414 OPEN check_rcpt_source;
1415 FETCH check_rcpt_source INTO lv_apps_source_code;
1416 CLOSE check_rcpt_source;
1417 END IF;
1418 -- iSupplier porting
1419
1420 jai_rcv_utils_pkg.get_func_curr(p_organization_id,
1421 v_func_currency,
1422 v_gl_set_of_books_id);
1423 IF v_debug_flag = 'Y' THEN
1424 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_func_currency is ' || v_func_currency);
1425 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_gl_set_of_books_id is ' || v_gl_set_of_books_id);
1426 END IF;
1427
1428 IF v_func_currency <> v_currency_code AND p_transaction_type <> 'MATCH' THEN
1429 v_currency_conversion_rate := p_currency_conversion_rate;
1430 END IF;
1431
1432 FOR row_rec IN (SELECT ROWID,
1433 organization_id
1434 FROM rcv_shipment_headers
1435 WHERE shipment_header_id = p_shipment_header_id)
1436 LOOP
1437 -- v_rowid := row_rec.ROWID;
1438 v_rsh_organization_id := row_rec.organization_id;
1439 END LOOP;
1440
1441 -- added, Harshita for bug #4285064
1442 /* Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
1443 v_attribute1 := p_attribute1;
1444 v_attribute2 := p_attribute2;
1445 v_attribute3 := p_attribute3;
1446 */
1447 -- ended, Harshita for bug #4285064
1448
1449 /* Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. */
1450 open c_ja_rcv_interface(p_interface_transaction_id);
1451 fetch c_ja_rcv_interface into r_ja_rcv_interface;
1452 close c_ja_rcv_interface;
1453 if r_ja_rcv_interface.interface_transaction_id is not null then
1454 lv_excise_invoice_no := r_ja_rcv_interface.excise_invoice_no;
1455 lv_excise_invoice_date := r_ja_rcv_interface.excise_invoice_date;
1456 lv_online_claim_flag := r_ja_rcv_interface.online_claim_flag;
1457 end if;
1458
1459 /* R12-PADDR
1460 IF p_transaction_type IN ('RECEIVE', 'DELIVER', 'UNORDERED') THEN
1461
1462
1463 -- Vijay Shankar for Bug# 3028040
1464 -- IF loop added by vijay shankar to add EXPRESS receipt functionality by Removing PADDR
1465 if p_attribute15 = 'EXPRESS' then
1466 v_paddr := NULL;
1467 else
1468 v_paddr := HEXTORAW(p_attribute15);
1469 end if;
1470
1471 IF v_debug_flag = 'Y' THEN
1472 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_paddr is ' || v_paddr);
1473 END IF;
1474
1475 END IF;
1476
1477 IF v_rowid IS NOT NULL AND v_paddr IS NOT NULL THEN
1478 FOR loc_rec IN (SELECT form_id_drop
1479 FROM JAI_CMN_LOCATORS_T
1480 WHERE form_id_drop = v_form_id
1481 AND paddr = v_paddr)
1482 LOOP
1483 v_chk_form := loc_rec.form_id_drop;
1484 END LOOP;
1485
1486 IF v_debug_flag = 'Y' THEN
1487 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_chk_form is ' || v_chk_form);
1488 END IF;
1489
1490 END IF;
1491
1492 IF v_chk_form IS NOT NULL THEN
1493 UPDATE JAI_CMN_LOCATORS_T
1494 SET row_id = v_rowid
1495 WHERE FORM_NAME = v_form_id
1496 AND paddr = v_paddr;
1497 END IF;
1498
1499
1500 IF v_debug_flag = 'Y' THEN
1501 UTL_FILE.PUT_LINE(v_myfilehandle,'VIJAY2 v_express->' || v_express||', v_chk_form->'||v_chk_form);
1502 END IF;
1503
1504 -- following if changed by Vijay Shankar for Bug# 3028040
1505 IF (v_chk_form IS NULL AND nvl(v_express, 'NONEXPRESS') = 'EXPRESS') THEN
1506 v_chk_form := 'JAINPORE';
1507 END IF;
1508 R12-PADDR */
1509
1510 -- Vijay Shankar for Bug# 3028040
1511 OPEN c_hdr_dtl(p_shipment_header_id);
1512 FETCH c_hdr_dtl INTO -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. v_express,
1513 v_shipment_num ; -- ssumaith - bug# 3657662 v_shipment_num added
1514 CLOSE c_hdr_dtl;
1515
1516 --Start Added by Ramananda for Bug#4533114
1517 BEGIN
1518 --checking whether the v_shipment_num is number
1519 ln_test_delivery_id := NULL;
1520 ln_test_delivery_id := v_shipment_num;
1521 EXCEPTION
1522 WHEN VALUE_ERROR THEN
1523 OPEN c_hdr_attribute5_1(v_shipment_num);
1524 FETCH c_hdr_attribute5_1 INTO v_shipment_num;
1525 CLOSE c_hdr_attribute5_1;
1526 END;
1527 --End Added by Ramananda for Bug#4533114
1528
1529 -------------------------------- To retrieve receipt number ---------------------------------
1530 FOR header_rec IN (SELECT receipt_num
1531 FROM rcv_shipment_headers
1532 WHERE shipment_header_id = p_shipment_header_id)
1533 LOOP
1534 v_receipt_num := header_rec.receipt_num;
1535 END LOOP;
1536 ----------------------------- to retrieve receipt_source_code -------------------------------
1537 IF v_debug_flag = 'Y' THEN
1538 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_receipt_num is ' || v_receipt_num);
1539 END IF;
1540
1541 IF p_transaction_type = 'RECEIVE' THEN
1542 FOR head_rec IN (SELECT receipt_source_code
1543 FROM rcv_shipment_headers
1544 WHERE shipment_header_id = p_shipment_header_id)
1545 LOOP
1546 v_receipt_source_code := head_rec.receipt_source_code;
1547 END LOOP;
1548 END IF;
1549 IF v_debug_flag = 'Y' THEN
1550 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_receipt_source_code is ' || v_receipt_source_code);
1551 END IF;
1552 -------------------------------------------------------------------------------------------------gsr
1553 IF p_source_document_code IN ('PO', 'REQ','RMA','INVENTORY') AND /*rchandan for bug#6030615..added INVENTORY*/
1554 p_destination_type_code IN ('RECEIVING', 'INVENTORY')
1555 THEN
1556
1557 -- Start of addition by Srihari on 04-APR-2000
1558
1559
1560 IF p_transaction_type IN ('RECEIVE', 'UNORDERED') AND
1561 p_source_document_code = 'PO'
1562 AND lv_excise_invoice_no IS NOT NULL -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.. If this condition is satisfied, it means it is a interface receipt
1563 THEN
1564
1565 FOR excise_rec IN
1566 (SELECT rsl.shipment_header_id
1567 FROM rcv_shipment_lines rsl
1568 Where rsl.to_organization_id = p_organization_id
1569 and exists -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. rsl.po_header_id in
1570 (select pha.po_header_id
1571 from po_headers_all pha
1572 Where pha.vendor_site_id=p_vendor_site_id
1573 and po_header_id = rsl.po_header_id -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
1574 )
1575 and rsl.shipment_line_id in
1576 (select jrl.shipment_line_id
1577 from JAI_RCV_LINES jrl
1578 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. WHERE jrl.excise_invoice_no = p_attribute1
1579 -- AND jrl.excise_invoice_date = p_attribute2
1580 WHERE jrl.excise_invoice_no = lv_excise_invoice_no
1581 AND jrl.excise_invoice_date = lv_excise_invoice_date
1582 )
1583 )
1584 LOOP
1585 IF p_shipment_header_id <> excise_rec.shipment_header_id THEN
1586 IF v_debug_flag = 'Y' THEN
1587 UTL_FILE.PUT_LINE(v_myfilehandle,'error 1 dup exc inv ' );
1588 END IF;
1589 errormsg:='Duplicate Excise invoice NUMBER FOR the same supplier site';
1590 RAISE_APPLICATION_ERROR (-20501, 'Duplicate Excise invoice NUMBER FOR the same supplier site');
1591 END IF;
1592 END LOOP;
1593 END IF; --End if for Transaction Type in RECEIVE, UNORDERED.
1594
1595 IF v_debug_flag = 'Y' THEN
1596 UTL_FILE.PUT_LINE(v_myfilehandle,'Before UOM Cursor for Receipt ');
1597 END IF;
1598 -- End of addition by Srihari on 04-APR-2000
1599 -- Start of addition by Srihari on 30-NOV-99
1600 OPEN uom_cur(p_unit_of_measure);
1601 FETCH uom_cur INTO v_rcv_uom_code;
1602 CLOSE uom_cur;
1603
1604 IF v_debug_flag = 'Y' THEN
1605 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_rcv_uom_code is ' || v_rcv_uom_code);
1606 END IF;
1607
1608 IF p_source_document_code = 'PO' THEN
1609 --Changed by Nagaraj.s for Bug2531667
1610 FOR ll_rec IN (SELECT plla.unit_meas_lookup_code ll_uom,
1611 pla.unit_meas_lookup_code l_uom
1612 FROM po_line_locations_all plla,
1613 po_lines_all pla
1614 WHERE plla.line_location_id = p_line_location_id
1615 AND pla.po_header_id = plla.po_header_id
1616 AND pla.po_line_id = p_po_line_id)
1617 LOOP
1618 IF ll_rec.ll_uom IS NOT NULL THEN
1619 v_po_uom := ll_rec.ll_uom;
1620 ELSE
1621 v_po_uom := ll_rec.l_uom;
1622 END IF;
1623
1624 END LOOP;
1625
1626 ELSIF p_source_document_code = 'REQ' THEN
1627
1628 FOR req_rec IN (SELECT unit_meas_lookup_code r_uom
1629 FROM po_requisition_lines_all
1630 WHERE requisition_line_id = p_requisition_line_id)
1631 LOOP
1632 v_po_uom := req_rec.r_uom;
1633 END LOOP;
1634 --Gsr
1635 ELSIF p_source_document_code = 'RMA' THEN
1636
1637 FOR rma_rec IN (SELECT order_quantity_uom rma_uom
1638 FROM oe_order_lines_all
1639 WHERE HEADER_ID = p_oe_order_header_id)
1640 LOOP
1641 v_po_uom := rma_rec.rma_uom;
1642 END LOOP;
1643
1644 ELSIF p_source_document_code = 'INVENTORY' THEN /*rchandan for bug#6030615...start*/
1645
1646 open c_rec_ship_txn(p_shipment_line_id);
1647 fetch c_rec_ship_txn into r_rec_ship_txn;
1648 close c_rec_ship_txn;
1649
1650 OPEN c_get_inv_trx_info(r_rec_ship_txn.mmt_transaction_id);
1651 FETCH c_get_inv_trx_info INTO ln_trx_qty , lv_trx_uom , ln_orig_id , ln_item_cost;
1652 CLOSE c_get_inv_trx_info;
1653
1654 /*
1655 ln_orig_id : this field has the original_transaction_Temp_id
1656 */
1657
1658 OPEN c_jai_mtl_Trxs(ln_orig_id);
1659 FETCH c_jai_mtl_Trxs INTO r_jai_mtl_Trxs;
1660 CLOSE c_jai_mtl_Trxs;
1661
1662 v_po_uom := lv_trx_uom; /*rchandan for bug#6030615...end*/
1663
1664 END IF; --End if for p_source_document_code
1665 --Gsr
1666
1667 OPEN uom_cur(v_po_uom);
1668 FETCH uom_cur INTO v_po_uom_code;
1669 CLOSE uom_cur;
1670
1671 IF v_debug_flag = 'Y' THEN
1672 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_po_uom_code is ' || v_po_uom_code);
1673 END IF;
1674
1675
1676 jai_rcv_utils_pkg.get_organization(p_shipment_line_id,
1677 v_organization_id,
1678 v_item_id);
1679 IF v_debug_flag = 'Y' THEN
1680 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_organization_id is ' || v_organization_id);
1681 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_item_id is ' || v_item_id);
1682 END IF;
1683
1684 --Pick Item Modvat Flag---------------------------------------------------------------------
1685 --Moved this Piece of Code from the Bottom to Ensure that Item Modvat Flag and Trading Flag
1686 --are picked well in advance by Nagaraj.s for Bug2915783..................................
1687 FOR mod_rec IN (SELECT
1688 modvat_flag,
1689 item_trading_flag,
1690 item_class --Added by Nagaraj.s for Bug3202319
1691 FROM JAI_INV_ITM_SETUPS
1692 WHERE organization_id = v_organization_id
1693 AND inventory_item_id = v_item_id)
1694 LOOP
1695 v_item_modvat_flag := NVL(mod_rec.modvat_flag, 'N');
1696 v_item_trading_flag := NVL(mod_rec.item_trading_flag, 'N');
1697 v_item_class := mod_rec.item_class; --Added by Nagaraj.s for Bug3202319
1698 END LOOP;
1699
1700 IF v_debug_flag = 'Y' THEN
1701 UTL_FILE.PUT_LINE(v_myfilehandle,'The value of v_item_modvat_flag is ' || v_item_modvat_flag);
1702 UTL_FILE.PUT_LINE(v_myfilehandle,'The value of v_item_trading_flag is ' || v_item_trading_flag);
1703 END IF;
1704 v_item_modvat_flag := NVL(v_item_modvat_flag, 'N');
1705 /* Bug 4568090. Added by Lakshmi Gopalsami
1706 Value should be 'N' and not 'M' if the value is null */
1707 v_item_trading_flag := NVL(v_item_trading_flag, 'N');
1708 --Pick Item Modvat Flag---------------------------------------------------------------------
1709
1710 /* Start, following call added by Vijay Shankar for Bug#4250236(4245089). VAT Impl. */
1711 jai_inv_items_pkg.jai_get_attrib(
1712 p_regime_code => jai_constants.vat_regime,
1713 p_organization_id => p_organization_id,
1714 p_inventory_item_id => v_item_id,
1715 p_attribute_code => jai_constants.rgm_attr_item_recoverable,
1716 p_attribute_value => lv_vat_recoverable_for_item,
1717 p_process_flag => lv_process_flag,
1718 p_process_msg => lv_process_msg
1719 );
1720
1721 IF lv_process_flag = jai_constants.unexpected_error THEN
1722 errormsg:='Error from jai_inv_items_pkg.jai_get_attrib: Error:'||lv_process_msg;
1723 RAISE_APPLICATION_ERROR( -20099, 'Error from jai_inv_items_pkg.jai_get_attrib: Error:'||lv_process_msg);
1724 END IF;
1725
1726 -- Default value for following variable is set as YES
1727 lv_vat_recoverable_for_item := nvl(lv_vat_recoverable_for_item, jai_constants.yes); -- CHK
1728 /* End, Vijay Shankar for Bug#4250236(4245089) */
1729
1730 IF v_rcv_uom_code <> v_po_uom_code THEN
1731
1732 Inv_Convert.inv_um_conversion(v_rcv_uom_code,
1733 v_po_uom_code,
1734 v_item_id,
1735 v_uom_rate);
1736 IF v_uom_rate = -99999 THEN
1737 v_uom_rate := 0;
1738 END IF;
1739
1740 ELSE
1741
1742 v_uom_rate := 1;
1743
1744 END IF;
1745
1746 v_uom_rate := NVL(v_uom_rate, 1);
1747
1748 IF v_debug_flag = 'Y' THEN
1749 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_uom_rate is ' || v_uom_rate);
1750 END IF;
1751
1752 -- End of addition by Srihari on 30-NOV-99
1753 -- Not an unordered receipt --
1754
1755 Duplicate_shipment_update;
1756
1757 IF v_debug_flag = 'Y' THEN
1758 UTL_FILE.PUT_LINE(v_myfilehandle,'After Duplicate Shipment Update, v_receipt_source_code ->'||v_receipt_source_code);
1759 UTL_FILE.PUT_LINE(v_myfilehandle,'After Duplicate Shipment Update, p_transaction_type ->'||p_transaction_type);
1760 UTL_FILE.PUT_LINE(v_myfilehandle,'After Duplicate Shipment Update, v_duplicate_ship ->'||v_duplicate_ship);
1761 End IF;
1762 /* R12-PADDR
1763 IF v_debug_flag = 'Y' THEN
1764 UTL_FILE.PUT_LINE(v_myfilehandle,'After Duplicate Shipment Update, v_chk_form ->'||v_chk_form);
1765 END IF;
1766 R12-PADDR */
1767 ---------- First Receipt for this line location ------------
1768
1769 IF (
1770 ( v_receipt_source_code IS NOT NULL AND
1771 p_transaction_type = 'RECEIVE' AND
1772 v_receipt_source_code in ('VENDOR', 'INVENTORY') -- AND was commented by GSRI on 21-OCT-01 and OR was added
1773 /* R12-PADDR or v_chk_form IS NOT NULL */
1774 )
1775 OR
1776 ( v_receipt_source_code IS NOT NULL AND
1777 p_transaction_type = 'RECEIVE' AND
1778 v_receipt_source_code = 'CUSTOMER' -- AND changed to OR - ssumaith - bug# 3683666
1779 /* R12-PADDR or v_chk_form IS NOT NULL */
1780 )
1781 OR
1782 p_transaction_type = 'MATCH'
1783 )
1784 AND nvl(v_duplicate_ship, 'N') = 'N'
1785 THEN
1786
1787
1788 IF v_debug_flag = 'Y' THEN
1789 UTL_FILE.PUT_LINE(v_myfilehandle,'Inside the main If Condition');
1790 UTL_FILE.PUT_LINE(v_myfilehandle,'p_source_document_code = ' || p_source_document_code);
1791 END IF;
1792
1793 -------------------------- To retrieve po quantity -------------------------------------
1794
1795 FOR qty_rec IN (SELECT quantity
1796 FROM po_line_locations_all
1797 WHERE line_location_id = p_line_location_id)
1798 LOOP
1799 v_loc_quantity := qty_rec.quantity;
1800 END LOOP;
1801
1802 IF p_transaction_type = 'RECEIVE' -- AND was commented by GSRI on 21-OCT-01 and OR was added
1803 /* R12-PADDR OR v_chk_form IS NOT NULL */
1804 THEN
1805 set_receipt_flag;
1806 insert_receipt_line;
1807
1808 ELSIF p_transaction_type = 'MATCH' THEN
1809 set_receipt_flag;
1810 v_receipt_modify_flag := 'N';
1811 END IF;
1812
1813
1814 IF p_source_document_code = 'RMA' THEN
1815 --Gsr
1816 IF v_debug_flag = 'Y' THEN
1817 UTL_FILE.PUT_LINE(v_myfilehandle,'Inside the RMA Condition');
1818 END IF;
1819
1820
1821
1822 FOR lines_rec IN (SELECT
1823 rtl.tax_line_no,
1824 rtl.tax_id,
1825 rtl.tax_rate,
1826 rtl.qty_rate,
1827 rtl.uom,
1828 rtl.tax_amount,
1829 jtc.tax_type,
1830 jtc.tax_name,
1831 jtc.vendor_id,
1832 NVL(jtc.mod_cr_percentage, 0) modcp,
1833 NVL(jtc.rounding_factor, 0) rounding_factor,
1834 jtc.duty_drawback_percentage duty,
1835 --3848133
1836 rtl.precedence_1,
1837 rtl.precedence_2,
1838 rtl.precedence_3,
1839 rtl.precedence_4,
1840 rtl.precedence_5,
1841 rtl.precedence_6, -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
1842 rtl.precedence_7,
1843 rtl.precedence_8,
1844 rtl.precedence_9,
1845 rtl.precedence_10,
1846 tax_types.regime_code regime_code
1847 --3848133
1848 FROM JAI_OM_OE_RMA_TAXES rtl,
1849 JAI_CMN_TAXES_ALL jtc
1850 , jai_regime_tax_types_v tax_types
1851 WHERE rtl.rma_line_id = p_oe_order_line_id
1852 AND jtc.tax_id = rtl.tax_id
1853 AND tax_types.tax_type(+) = jtc.tax_type
1854 )
1855 LOOP
1856
1857 -- Start of addition by Srihari on 30-NOV-99
1858 --Gsr
1859 /* Added by LGOPALSa. Bug 4210102.
1860 * ADded Excise and CVD education cess */
1861
1862 /* Bug 4568090. Added by LGOPALSA
1863 Added check for trading flag to ensure that
1864 recoverable flag is properly set for trading items*/
1865
1866 IF v_item_modvat_flag = 'N' AND
1867 v_item_trading_flag = 'N' AND
1868 upper(lines_rec.tax_type) IN ('EXCISE', 'ADDL. EXCISE', 'OTHER EXCISE', 'CVD',
1869 JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS,jai_constants.tax_type_exc_edu_cess,
1870 JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS,jai_constants.tax_type_cvd_edu_cess)/*Bug 5989740 bduvarag*/
1871 THEN
1872 v_tax_modvat_flag := 'N';
1873
1874 /* following elsif added by Vijay Shankar for Bug#4250236(4245089). VAT Impl. */
1875 ELSIF lv_vat_recoverable_for_item <> jai_constants.yes
1876 AND lines_rec.regime_code = jai_constants.vat_regime
1877 THEN
1878 v_tax_modvat_flag := jai_constants.no;
1879
1880 ELSIF lines_rec.modcp > 0 THEN
1881 v_tax_modvat_flag := 'Y';
1882 END IF;
1883 --Gsr
1884 -- End of addition by Srihari on 30-NOV-99
1885 IF p_currency_code <> v_func_currency THEN
1886 v_conv_factor := NVL(v_currency_conversion_rate, 1);
1887 ELSE
1888 v_conv_factor := 1;
1889 END IF;
1890
1891 FOR pick_rec IN (SELECT quantity
1892 FROM JAI_OM_OE_RMA_LINES rel
1893 WHERE rel.rma_line_id = p_oe_order_line_id)
1894 LOOP
1895 v_loc_quantity := pick_rec.quantity;
1896 END LOOP;
1897
1898 IF NVL(v_loc_quantity, 0) <> 0 THEN
1899 v_cor_amount := ROUND((P_qty_received * lines_rec.tax_amount * v_uom_rate / v_loc_quantity),
1900 NVL(lines_rec.rounding_factor, 0));
1901 END IF;
1902 /* Added by LGOPALSA. Bug 4210102
1903 * Added CVD, Excise and Customs edcuation cess */
1904
1905 IF upper(lines_rec.tax_type) IN ('EXCISE', 'ADDL. EXCISE', 'OTHER EXCISE', 'CUSTOMS', 'CVD',
1906 JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS,jai_constants.tax_type_Exc_edu_cess,
1907 JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS,jai_constants.tax_type_cvd_Edu_cess,
1908 JAI_CONSTANTS.TAX_TYPE_SH_CUSTOMS_EDU_CESS,jai_constants.tax_type_customs_edu_cess)/*Bug5989740 bduvarag*/
1909 THEN
1910 v_claimable_amount := NVL(v_cor_amount * v_conv_factor, 0) * NVL(lines_rec.duty, 0) / 100;
1911 ELSE
1912 v_claimable_amount := 0;
1913 END IF;
1914
1915 --Added by GSRI on 21-OCT-01
1916 SELECT COUNT(*)
1917 INTO v_chk_receipt_tax_lines
1918 FROM JAI_RCV_LINE_TAXES
1919 WHERE shipment_line_id = p_shipment_line_id
1920 AND shipment_header_id = p_shipment_header_id
1921 AND tax_id = lines_rec.tax_id;
1922
1923 IF v_chk_receipt_tax_lines = 0 THEN
1924 /*
1925 DELETE FROM JAI_RCV_LINE_TAXES
1926 WHERE shipment_line_id = p_shipment_line_id AND
1927 shipment_header_id = p_shipment_header_id AND
1928 tax_id = lines_rec.tax_id;*/
1929 --End Addition by on GSRI 21-OCT-01
1930
1931 v_tax_modvat_flag := NVL(v_tax_modvat_flag,'N') ;
1932 INSERT INTO JAI_RCV_LINE_TAXES
1933 (
1934 shipment_line_id,
1935 tax_line_no,
1936 shipment_header_id,
1937 tax_id,
1938 tax_name,
1939 currency,
1940 tax_rate,
1941 qty_rate,
1942 uom,
1943 tax_amount,
1944 tax_type,
1945 modvat_flag,
1946 creation_date,
1947 created_by,
1948 last_update_date,
1949 last_updated_by,
1950 last_update_login,
1951 vendor_id,
1952 claimable_amount,
1953 --3848133
1954 precedence_1,
1955 precedence_2,
1956 precedence_3,
1957 precedence_4,
1958 precedence_5,
1959 precedence_6, -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
1960 precedence_7,
1961 precedence_8,
1962 precedence_9,
1963 precedence_10,
1964 transaction_id
1965 --3848133
1966 )
1967 VALUES
1968 (
1969 p_shipment_line_id,
1970 lines_rec.tax_line_no,
1971 p_shipment_header_id,
1972 lines_rec.tax_id,
1973 lines_rec.tax_name,
1974 p_currency_code,
1975 lines_rec.tax_rate,
1976 lines_rec.qty_rate,
1977 lines_rec.uom,
1978 v_cor_amount,
1979 lines_rec.tax_type,
1980 v_tax_modvat_flag, --NVL(v_tax_modvat_flag,'N'),
1981 p_creation_date,
1982 p_created_by,
1983 p_last_update_date,
1984 p_last_updated_by,
1985 p_last_update_login,
1986 lines_rec.vendor_id,
1987 v_claimable_amount,
1988 --3848133
1989 lines_rec.precedence_1,
1990 lines_rec.precedence_2,
1991 lines_rec.precedence_3,
1992 lines_rec.precedence_4,
1993 lines_rec.precedence_5,
1994 lines_rec.precedence_6, -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
1995 lines_rec.precedence_7,
1996 lines_rec.precedence_8,
1997 lines_rec.precedence_9,
1998 lines_rec.precedence_10,
1999 p_transaction_id
2000 --3848133
2001 );
2002
2003 END IF;
2004
2005 IF lines_rec.tax_type NOT IN ('TDS', 'Modvat Recovery') THEN
2006 v_tax_total := v_tax_total + NVL(v_cor_amount * v_conv_factor, 0);
2007 END IF;
2008
2009 END LOOP;
2010 --Gsr
2011 --ELSE -- commented by sriram bug # 2514719
2012 --Changed by Nagaraj.s for Bug # 2588096
2013
2014 ELSIF p_source_document_code = 'PO' AND p_transaction_type <> 'UNORDERED' THEN
2015
2016 -- the above elsif added by sriram bug # 2514719 on aug 20th
2017 -- ISO CYCLE AS the following STATEMENT IS ONLY applicable FOR PO transactions
2018 -- Start addition by Aparajita for bug#2415767 on 17th june 2002
2019
2020 BEGIN
2021 SELECT currency_code,
2022 rate
2023 INTO v_po_currency,
2024 v_po_rate
2025 FROM po_headers_all
2026 WHERE po_header_id = p_po_header_id;
2027 EXCEPTION
2028 WHEN OTHERS THEN
2029 IF v_debug_flag = 'Y' THEN
2030 UTL_FILE.PUT_LINE(v_myfilehandle,'error 2 fetch po curr ' );
2031 END IF;
2032 errormsg:='Error while fetching PO currency details :' || SQLERRM;
2033 RAISE_APPLICATION_ERROR (-20501, 'Error while fetching PO currency details :' || SQLERRM);
2034 END;
2035 /*Bug 4644524 start bduvarag*/
2036 OPEN c_rcv_shipment_lines(p_shipment_line_id);
2037 FETCH c_rcv_shipment_lines INTO r_rcv_shipment_lines;
2038 CLOSE c_rcv_shipment_lines;
2039 /*Bug 4644524 End bduvarag*/
2040
2041 IF v_debug_flag = 'Y' THEN
2042 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_po_currency is ' || v_po_currency);
2043 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_po_rate is ' || v_po_rate);
2044 END IF;
2045 -- end addition by Aparajita for bug#2415767 on 17th june 2002
2046
2047
2048 /* iSupplier porting
2049 || start - ssumaith - ASBN
2050 */
2051 OPEN c_check_asbn;
2052 FETCH c_check_asbn INTO lv_asbn_type , lv_shipment_num;
2053 CLOSE c_check_asbn;
2054
2055 IF lv_asbn_type = '1' THEN
2056 lv_asbn_type := 'TRUE';
2057 ELSE
2058 lv_asbn_type := 'FALSE';
2059 END IF;
2060
2061 IF p_source_document_code = 'PO' AND p_transaction_type <> 'UNORDERED' AND lv_asbn_type <> 'TRUE' THEN
2062
2063 OPEN c_po_tax_cur FOR
2064 SELECT tax_line_no,
2065 llt.tax_id,
2066 DECODE(SIGN(jtc.end_date - SYSDATE), -1, 0, llt.tax_rate) tax_rate,
2067 DECODE(SIGN(jtc.end_date - SYSDATE), -1, 0, llt.qty_rate) qty_rate,
2068 uom,
2069 DECODE(SIGN(jtc.end_date - SYSDATE), -1, 0, llt.tax_amount) tax_amount,
2070 llt.tax_type,
2071 jtc.tax_name,
2072 llt.modvat_flag,
2073 llt.vendor_id,
2074 jtc.vendor_id tax_vendor_id,
2075 jtc.vendor_site_id,
2076 llt.currency,
2077 jtc.rounding_factor,
2078 jtc.duty_drawback_percentage duty,
2079 llt.precedence_1,
2080 llt.precedence_2,
2081 llt.precedence_3,
2082 llt.precedence_4,
2083 llt.precedence_5
2084 , tax_types.regime_code regime_code,
2085 llt.precedence_6,
2086 llt.precedence_7,
2087 llt.precedence_8,
2088 llt.precedence_9,
2089 llt.precedence_10
2090 FROM JAI_PO_TAXES llt,
2091 JAI_CMN_TAXES_ALL jtc
2092 , jai_regime_tax_types_v tax_types
2093 WHERE line_location_id = p_line_location_id
2094 AND jtc.tax_id = llt.tax_id
2095 AND jtc.tax_type = tax_types.tax_type (+)
2096 order by tax_line_no;
2097
2098 ELSIF p_source_document_code = 'PO' AND lv_asbn_type = 'TRUE' THEN
2099
2100
2101 /*
2102 Code to populate the excise invoice number and date into the
2103 ja_in_Receipt_lines procedure in case of an asbn receipt.
2104 */
2105
2106
2107 OPEN c_jai_cmn_lines(v_shipment_num);
2108 FETCH c_jai_cmn_lines INTO r_jai_cmn_lines;
2109 CLOSE c_jai_cmn_lines;
2110
2111 v_loc_quantity := r_jai_cmn_lines.quantity;
2112
2113 update JAI_RCV_LINES
2114 set excise_invoice_no = r_jai_cmn_lines.excise_inv_number,
2115 excise_invoice_date=r_jai_cmn_lines.excise_inv_Date
2116 where shipment_line_id = p_shipment_line_id;
2117
2118 OPEN c_po_tax_cur FOR
2119 SELECT tax_line_no,
2120 llt.tax_id,
2121 DECODE(SIGN(jtc.end_date - SYSDATE), -1, 0, llt.tax_rate) tax_rate,
2122 DECODE(SIGN(jtc.end_date - SYSDATE), -1, 0, llt.qty_rate) qty_rate,
2123 llt.uom,
2124 DECODE(SIGN(jtc.end_date - SYSDATE), -1, 0, llt.tax_amt) tax_amount,
2125 llt.tax_type,
2126 jtc.tax_name,
2127 llt.modvat_flag,
2128 nvl(jtc.vendor_id,p_vendor_id) vendor_id, /*rchandan for bug#6030615*/
2129 jtc.vendor_id tax_vendor_id,
2130 jtc.vendor_site_id,
2131 llt.currency_code currency ,
2132 jtc.rounding_factor,
2133 jtc.duty_drawback_percentage duty,
2134 llt.precedence_1,
2135 llt.precedence_2,
2136 llt.precedence_3,
2137 llt.precedence_4,
2138 llt.precedence_5
2139 , tax_types.regime_code regime_code,
2140 llt.precedence_6,
2141 llt.precedence_7,
2142 llt.precedence_8,
2143 llt.precedence_9,
2144 llt.precedence_10
2145 FROM jai_cmn_document_taxes llt,
2146 jai_cmn_lines cml,
2147 JAI_CMN_TAXES_ALL jtc ,
2148 jai_regime_tax_types_v tax_types
2149 WHERE cml.po_line_location_id = p_line_location_id
2150 AND cml.cmn_line_id = llt.source_doc_line_id
2151 AND cml.shipment_number = lv_shipment_num
2152 AND llt.source_doc_type = 'ASBN'
2153 AND jtc.tax_id = llt.tax_id
2154 AND jtc.tax_type = tax_types.tax_type (+)
2155 order by tax_line_no;
2156 --iSupplier porting
2157
2158 END IF ; /* end if for
2159 p_source_document_code = 'PO' AND p_transaction_type <> 'UNORDERED' AND lv_asbn_type <> 'TRUE'
2160 */
2161 ELSIF p_source_document_code = 'INVENTORY' THEN
2162
2163 v_po_currency := 'INR';
2164 v_po_rate := 1;
2165
2166 open c_rec_ship_txn(p_shipment_line_id);
2167 fetch c_rec_ship_txn into r_rec_ship_txn;
2168 close c_rec_ship_txn;
2169
2170 OPEN c_rcv_shipment_lines(p_shipment_line_id);
2171 FETCH c_rcv_shipment_lines INTO r_rcv_shipment_lines;
2172 CLOSE c_rcv_shipment_lines;
2173
2174 OPEN c_get_inv_trx_info(r_rec_ship_txn.mmt_transaction_id);
2175 FETCH c_get_inv_trx_info INTO ln_trx_qty , lv_trx_uom , ln_orig_id , ln_item_cost;
2176 CLOSE c_get_inv_trx_info;
2177
2178 OPEN c_jai_mtl_Trxs(ln_orig_id);
2179 FETCH c_jai_mtl_Trxs INTO r_jai_mtl_Trxs;
2180 CLOSE c_jai_mtl_Trxs;
2181
2182 v_loc_quantity := ln_trx_qty;
2183
2184 update jai_rcv_lines
2185 set excise_invoice_no = r_jai_mtl_trxs.excise_invoice_no ,
2186 excise_invoice_Date = r_jai_mtl_trxs.creation_Date
2187 where shipment_line_id = p_shipment_line_id;
2188
2189 OPEN c_po_tax_cur FOR
2190 SELECT tax_line_no,
2191 llt.tax_id,
2192 DECODE(SIGN(jtc.end_date - SYSDATE), -1, 0, llt.tax_rate) tax_rate,
2193 DECODE(SIGN(jtc.end_date - SYSDATE), -1, 0, llt.qty_rate) qty_rate,
2194 llt.uom,
2195 DECODE(SIGN(jtc.end_date - SYSDATE), -1, 0, llt.tax_amt) tax_amount,
2196 llt.tax_type,
2197 jtc.tax_name,
2198 llt.modvat_flag,
2199 NVL(jtc.vendor_id,r_jai_mtl_Trxs.from_organization) vendor_id, /* 6030615*/
2200 jtc.vendor_id tax_vendor_id,
2201 jtc.vendor_site_id,
2202 llt.currency_code currency ,
2203 jtc.rounding_factor,
2204 jtc.duty_drawback_percentage duty,
2205 llt.precedence_1,
2206 llt.precedence_2,
2207 llt.precedence_3,
2208 llt.precedence_4,
2209 llt.precedence_5,
2210 tax_types.regime_code regime_code,
2211 llt.precedence_6,
2212 llt.precedence_7,
2213 llt.precedence_8,
2214 llt.precedence_9,
2215 llt.precedence_10
2216 FROM jai_cmn_document_taxes llt,
2217 JAI_CMN_TAXES_ALL jtc ,
2218 jai_regime_tax_types_v tax_types,
2219 mtl_material_transactions mtl
2220 WHERE llt.source_doc_line_id = mtl.original_transaction_temp_id
2221 AND llt.source_doc_type = 'INTERORG_XFER'
2222 AND jtc.tax_id = llt.tax_id
2223 AND mtl.transaction_id = r_rec_ship_txn.MMT_TRANSACTION_ID
2224 AND jtc.tax_type = tax_types.tax_type (+)
2225 order by tax_line_no;
2226 END IF; -- RMA
2227 --Added/Modified by walton for inclusive tax
2228 -------------------------------------------------------------
2229 IF p_source_document_code = 'PO' AND lv_asbn_type <> 'TRUE'
2230 THEN
2231 SELECT price_override , quantity
2232 INTO v_price_override,v_po_quantity
2233 FROM po_line_locations_all
2234 WHERE line_location_id = p_line_location_id;
2235 ELSIF p_source_document_code = 'PO' AND lv_asbn_type = 'TRUE'
2236 THEN
2237 v_po_quantity := r_jai_cmn_lines.quantity;
2238 v_price_override := r_jai_cmn_lines.po_unit_price;
2239 ELSIF p_source_document_code = 'INVENTORY' THEN
2240 v_price_override := ln_item_cost; /* currently hard coded */
2241 v_po_quantity := ln_trx_qty; /* currently hard coded */
2242 END IF;
2243
2244 v_assessable_value := NVL(jai_cmn_setup_pkg.get_po_assessable_value
2245 ( p_vendor_id, p_vendor_site_id, v_item_id, p_uom_code ),v_price_override);
2246
2247 IF p_source_document_code = 'INVENTORY'
2248 THEN
2249 v_price_override:=r_jai_mtl_Trxs.selling_price;
2250 v_assessable_value:=NVL(r_jai_mtl_Trxs.assessable_Value,v_price_override);
2251 END IF;
2252
2253
2254 OPEN c_jai_cmn_lines(v_shipment_num);
2255 FETCH c_jai_cmn_lines INTO r_jai_cmn_lines;
2256 CLOSE c_jai_cmn_lines;
2257
2258 ln_po_unit_price := 0;
2259 IF lv_asbn_type = 'TRUE'
2260 THEN
2261 ln_po_unit_price := r_jai_cmn_lines.po_unit_price;
2262 END IF ;
2263 If p_source_document_code <> 'INVENTORY' THEN /*rchandan - 6030615*/
2264 ln_vat_assess_value := jai_general_pkg.ja_in_vat_assessable_value (
2265 p_party_id => p_vendor_id,
2266 p_party_site_id => p_vendor_site_id,
2267 p_inventory_item_id => v_item_id,
2268 p_uom_code => p_uom_code,
2269 p_default_price => ln_po_unit_price * r_jai_cmn_lines.quantity,
2270 p_ass_value_date => trunc(sysdate) ,
2271 p_party_type => 'V'
2272 );
2273 IF ln_vat_assess_value=0
2274 THEN
2275 ln_vat_assess_value:=v_price_override;
2276 END IF;
2277
2278 else /*rchandan - 6030615*/
2279 v_price_override := r_jai_mtl_Trxs.selling_price;
2280 ln_vat_assess_value:=NVL(r_jai_mtl_Trxs.vat_assessable_Value,v_price_override);
2281 end if;
2282
2283 If p_source_document_code = 'INVENTORY'/*rchandan - 6030615..start*/
2284 and po_lines_rec.regime_code <> jai_constants.vat_regime
2285 and upper(po_lines_rec.tax_type) NOT IN ('EXCISE', 'ADDL. EXCISE', 'OTHER EXCISE')
2286 THEN
2287 v_price_override := r_jai_mtl_Trxs.selling_price;
2288 END IF;
2289 -- bug 6488829 uncommented the if condition */
2290 IF p_source_document_code = 'INVENTORY' AND po_lines_rec.regime_code = jai_constants.vat_regime THEN
2291 v_precedence_0 := v_price_override;
2292 ELSE
2293 ln_vat_assess_value:=ln_vat_assess_value*v_po_quantity;
2294 v_assessable_value:=v_assessable_value*v_po_quantity;
2295 v_precedence_0 := v_price_override * v_po_quantity;
2296 END IF; /*rchandan - 6030615 end*/
2297
2298 IF v_po_currency <> po_lines_rec.currency THEN
2299 v_precedence_0 := v_precedence_0 * p_currency_conversion_rate;
2300 ln_vat_assess_value:=ln_vat_assess_value*p_currency_conversion_rate;
2301 v_assessable_value:=v_assessable_value*p_currency_conversion_rate;
2302 END IF;
2303
2304 -- proportionate the tax amount based on quantity in PO and receipt
2305 v_precedence_0 :=(v_precedence_0 / v_loc_quantity) * P_qty_received * v_uom_rate;
2306 -- v_uom_rate is added in the above line by Aparajita for bug#2929171
2307
2308 ln_vat_assess_value:=(ln_vat_assess_value / v_loc_quantity) * P_qty_received * v_uom_rate;
2309
2310 v_assessable_value:=(v_assessable_value / v_loc_quantity) * P_qty_received * v_uom_rate;
2311 -------------------------------------------------------------------------------------------
2312
2313 ln_curflag := 0; --Add by Kevin Cheng for bug 6853787 Mar 5, 2008
2314
2315 IF c_po_tax_cur%ISOPEN THEN /*rchandan for bug#6030615*/
2316 ln_curflag := 1; --Add by Kevin Cheng for bug 6853787 Mar 5, 2008
2317 LOOP
2318 fetch c_po_tax_cur INTO po_lines_rec;
2319 exit when c_po_tax_cur%notFOUND;
2320 --added by walton for inclusive tax on 01-Jan-08
2321 -----------------------------------------------------------------
2322 lt_tax_table(lt_tax_table.count+1) := po_lines_rec;
2323 p1(row_count) := nvl(po_lines_rec.precedence_1,-1);
2324 p2(row_count) := nvl(po_lines_rec.precedence_2,-1);
2325 p3(row_count) := nvl(po_lines_rec.precedence_3,-1);
2326 p4(row_count) := nvl(po_lines_rec.precedence_4,-1);
2327 p5(row_count) := nvl(po_lines_rec.precedence_5,-1);
2328 p6(row_count) := nvl(po_lines_rec.precedence_6,-1);
2329 p7(row_count) := nvl(po_lines_rec.precedence_7,-1);
2330 p8(row_count) := nvl(po_lines_rec.precedence_8,-1);
2331 p9(row_count) := nvl(po_lines_rec.precedence_9,-1);
2332 p10(row_count):= nvl(po_lines_rec.precedence_10,-1);
2333 tax_rate_tab(row_count) := NVL(po_lines_rec.tax_rate,0);
2334
2335 IF po_lines_rec.tax_rate is null
2336 THEN
2337 tax_rate_zero_tab(row_count) := 0;
2338 ELSIF po_lines_rec.tax_rate = 0
2339 THEN
2340 tax_rate_zero_tab(row_count) := -9999;
2341 ELSE
2342 tax_rate_zero_tab(row_count) := po_lines_rec.tax_rate;
2343 END IF; --End of po_lines_rec.tax_rate is null
2344
2345 round_factor_tab(row_count):=NVL(po_lines_rec.rounding_factor,0);
2346
2347 lt_tax_rate_per_rupee(row_count):=NVL(po_lines_rec.tax_rate,0)/100;
2348 ln_total_tax_per_rupee:=0;
2349 tax_amt_tab(row_count) := 0;
2350 base_tax_amt_tab(row_count) := 0;
2351 lt_tax_amt_rate_tax_tab(row_count):=0;
2352 lt_tax_amt_non_rate_tab(row_count):=0;
2353 OPEN c_get_inclusive_flag ( po_lines_rec.tax_id);
2354 FETCH c_get_inclusive_flag
2355 INTO lt_inclusive_tax_tab(row_count),lv_valid_date;
2356 CLOSE c_get_inclusive_flag;
2357
2358 IF lv_valid_date IS NULL OR lv_valid_date >= SYSDATE THEN
2359 end_date_tab(row_count) := 1;
2360 ELSE
2361 end_date_tab(row_count) := 0;
2362 END IF;
2363
2364 IF upper(po_lines_rec.tax_type) IN('EXCISE', 'ADDL. EXCISE', 'OTHER EXCISE', 'CVD',
2365 JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS,jai_constants.tax_type_exc_edu_cess,
2366 JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS,jai_constants.tax_type_cvd_edu_cess)
2367 THEN
2368 tax_type_tab(row_count) := 1;
2369 ELSIF po_lines_rec.regime_code=jai_constants.vat_regime
2370 THEN
2371 tax_type_tab(row_count) := 4;
2372 ELSE
2373 tax_type_tab(row_count) := 0;
2374 END IF;
2375 ------------------------------------------------------------------
2376
2377 IF v_item_modvat_flag = 'N' AND
2378 v_item_trading_flag = 'N' AND
2379 upper(po_lines_rec.tax_type) IN ('EXCISE', 'ADDL. EXCISE', 'OTHER EXCISE', 'CVD',
2380 JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS,jai_constants.tax_type_exc_edu_cess,
2381 JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS,jai_constants.tax_type_cvd_edu_cess)/*bug5989740 bduvarag*/
2382 THEN
2383 v_tax_modvat_flag := 'N';
2384 /* following elsif added by Vijay Shankar for Bug#4250236(4245089). VAT Impl. */
2385 /*Commented for Bug 4644524 bduvarag*/
2386 -- ELSIF lv_vat_recoverable_for_item <> jai_constants.yes
2387 ELSIF (lv_vat_recoverable_for_item <> jai_constants.yes OR r_rcv_shipment_lines.item_id IS NULL)
2388 AND po_lines_rec.regime_code = jai_constants.vat_regime
2389 THEN
2390 v_tax_modvat_flag := jai_constants.no;
2391
2392 ELSE
2393 v_tax_modvat_flag := po_lines_rec.modvat_flag;
2394 END IF;
2395 -- End of addition by Srihari on 30-NOV-99
2396
2397 IF v_debug_flag = 'Y' THEN
2398 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_tax_modvat_flag is ' || v_tax_modvat_flag);
2399 END IF;
2400 lt_tax_modvat_flag(row_count):=v_tax_modvat_flag; --Added by walton for inclusive
2401
2402 IF po_lines_rec.currency <> v_func_currency THEN
2403 v_conv_factor := NVL(v_currency_conversion_rate, 1);
2404 ELSE
2405 v_conv_factor := 1;
2406 END IF;
2407
2408 --Added by Nagaraj.s for Bug3037075
2409 --This is to set the Third Party Flag for proper value.
2410 if po_lines_rec.vendor_id <> p_vendor_id
2411 and upper(po_lines_rec.tax_type) not in ('TDS', 'MODVAT RECOVERY')
2412 and po_lines_rec.vendor_id > 0
2413 then
2414 v_third_party_flag := 'Y';
2415 --To ensure that proper vendor site id is populated into default_taxes_onto_line
2416 if po_lines_rec.vendor_id = po_lines_rec.tax_vendor_id then
2417 v_tax_vendor_site_id := po_lines_rec.vendor_site_id;
2418 else
2419 v_tax_vendor_site_id := null;
2420 end if;
2421 else
2422 v_third_party_flag := 'N';
2423 v_tax_vendor_site_id := null; --Added by Nagaraj.s for Bug3237536.
2424 --This was to be done as a part of Bug3037075
2425 --And as this variable was not reinitialized, hence in case of Receipts
2426 --where Excise was present after Adhoc, the Vendor site id was populated
2427 --for the Non Third Party line also.
2428
2429 end if; -- End of po_lines_rec.vendor_id <> p_vendor_id
2430
2431 lt_third_party_flag(row_count):=v_third_party_flag;--Added by walton for inclusive
2432
2433
2434 IF po_lines_rec.precedence_1 IS NOT NULL OR
2435 po_lines_rec.precedence_2 IS NOT NULL OR
2436 po_lines_rec.precedence_3 IS NOT NULL OR
2437 po_lines_rec.precedence_4 IS NOT NULL OR
2438 po_lines_rec.precedence_5 IS NOT NULL OR
2439 po_lines_rec.precedence_6 IS NOT NULL OR -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
2440 po_lines_rec.precedence_7 IS NOT NULL OR
2441 po_lines_rec.precedence_8 IS NOT NULL OR
2442 po_lines_rec.precedence_9 IS NOT NULL OR
2443 po_lines_rec.precedence_10 IS NOT NULL
2444 THEN
2445 lt_tax_amt_non_rate_tab(row_count):=0;
2446 ELSE
2447 lt_tax_amt_non_rate_tab(row_count) := (po_lines_rec.tax_amount / v_loc_quantity) * P_qty_received * v_uom_rate ;
2448 END IF; --End of po_lines_rec.precedence_1 IS NOT NULL OR
2449
2450 row_count := row_count + 1;
2451 END LOOP;
2452 CLOSE c_po_tax_cur; /*rchandan for bug#6030615*/
2453 row_count := row_count - 1;
2454 END IF; /* OF if ISOPEN*/
2455
2456 IF ln_curflag = 1 THEN--Add by Kevin Cheng for bug 6853787 Mar 5, 2008
2457 IF ln_vat_assess_value<>v_precedence_0
2458 THEN
2459 ln_vat_assessable_value:=ln_vat_assess_value;
2460 ELSE
2461 ln_vat_assessable_value:=1;
2462 END IF; --End p_vat_assessable_value<>p_tax_amount
2463
2464 IF v_assessable_value<>v_precedence_0
2465 THEN
2466 ln_assessable_value:=v_assessable_value;
2467 ELSE
2468 ln_assessable_value:=1;
2469 END IF; --End p_assessable_value<>p_tax_amount
2470 --Added by walton for inclusive tax computation
2471 -----------------------------------------------
2472 FOR I IN 1..row_count LOOP
2473 IF end_date_tab(I) <> 0 THEN
2474 IF tax_type_tab(I) = 1 THEN
2475 IF ln_assessable_value =1
2476 THEN
2477 bsln_amt:=1;
2478 ln_bsln_amt_nr :=0;
2479 ELSE
2480 bsln_amt :=0;
2481 ln_bsln_amt_nr :=ln_assessable_value;
2482 END IF;
2483 ELSIF tax_type_tab(I) = 4 THEN
2484 IF ln_vat_assessable_value =1
2485 THEN
2486 bsln_amt:=1;
2487 ln_bsln_amt_nr :=0;
2488 ELSE
2489 bsln_amt :=0;
2490 ln_bsln_amt_nr :=ln_vat_assessable_value;
2491 END IF;
2492 ELSIF tax_type_tab(I) = 6 THEN
2493 bsln_amt:=0;
2494 ln_bsln_amt_nr :=0;
2495 ELSE
2496 bsln_amt:=1;
2497 ln_bsln_amt_nr :=0;
2498 END IF;
2499
2500 IF tax_rate_tab(I) <> 0 THEN
2501 IF P1(I) < I AND P1(I) NOT IN (-1,0) THEN
2502 vamt := vamt + NVL(tax_amt_tab(P1(I)),0);
2503 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P1(I)),0);
2504 ELSIF P1(I) = 0 THEN
2505 vamt := vamt + bsln_amt;
2506 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
2507 END IF;
2508 IF p2(I) < I AND p2(I) NOT IN (-1,0) THEN
2509 vamt := vamt + NVL(tax_amt_tab(p2(I)),0);
2510 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P2(I)),0);
2511 ELSIF p2(I) = 0 THEN
2512 vamt := vamt + bsln_amt;
2513 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
2514 END IF;
2515 IF p3(I) < I AND p3(I) NOT IN (-1,0) THEN
2516 vamt := vamt + NVL(tax_amt_tab(p3(I)),0);
2517 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P3(I)),0);
2518 ELSIF p3(I) = 0 THEN
2519 vamt := vamt + bsln_amt;
2520 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
2521 END IF;
2522 IF p4(I) < I AND p4(I) NOT IN (-1,0) THEN
2523 vamt := vamt + NVL(tax_amt_tab(p4(I)),0);
2524 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P4(I)),0);
2525 ELSIF p4(I) = 0 THEN
2526 vamt := vamt + bsln_amt;
2527 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
2528 END IF;
2529 IF p5(I) < I AND p5(I) NOT IN (-1,0) THEN
2530 vamt := vamt + NVL(tax_amt_tab(p5(I)),0);
2531 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P5(I)),0);
2532 ELSIF p5(I) = 0 THEN
2533 vamt := vamt + bsln_amt;
2534 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
2535 END IF;
2536 IF P6(I) < I AND P6(I) NOT IN (-1,0) THEN
2537 vamt := vamt + NVL(tax_amt_tab(P6(I)),0);
2538 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P6(I)),0);
2539 ELSIF P6(I) = 0 THEN
2540 vamt := vamt + bsln_amt;
2541 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
2542 END IF;
2543 IF p7(I) < I AND p7(I) NOT IN (-1,0) THEN
2544 vamt := vamt + NVL(tax_amt_tab(p7(I)),0);
2545 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P7(I)),0);
2546 ELSIF p7(I) = 0 THEN
2547 vamt := vamt + bsln_amt;
2548 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
2549 END IF;
2550 IF p8(I) < I AND p8(I) NOT IN (-1,0) THEN
2551 vamt := vamt + NVL(tax_amt_tab(p8(I)),0);
2552 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P8(I)),0);
2553 ELSIF p8(I) = 0 THEN
2554 vamt := vamt + bsln_amt;
2555 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
2556 END IF;
2557 IF p9(I) < I AND p9(I) NOT IN (-1,0) THEN
2558 vamt := vamt + NVL(tax_amt_tab(p9(I)),0);
2559 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P9(I)),0);
2560 ELSIF p9(I) = 0 THEN
2561 vamt := vamt + bsln_amt;
2562 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
2563 END IF;
2564 IF p10(I) < I AND p10(I) NOT IN (-1,0) THEN
2565 vamt := vamt + NVL(tax_amt_tab(p10(I)),0);
2566 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P10(I)),0);
2567 ELSIF p10(I) = 0 THEN
2568 vamt := vamt + bsln_amt;
2569 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
2570 END IF;
2571 v_tax_amt := v_tax_amt + (vamt * (tax_rate_tab(I)/100));
2572 ln_tax_amt_nr:=ln_tax_amt_nr+(ln_vamt_nr*(tax_rate_tab(I)/100));
2573 base_tax_amt_tab(I) := vamt;
2574 tax_amt_tab(I) := NVL(tax_amt_tab(I),0) + v_tax_amt;
2575 lt_tax_amt_non_rate_tab(I):=NVL(lt_tax_amt_non_rate_tab(I),0)+ln_tax_amt_nr;
2576 lt_tax_amt_rate_tax_tab(I):= tax_amt_tab(I);
2577 vamt := 0;
2578 v_tax_amt := 0;
2579 ln_tax_amt_nr:=0; --added by walton for inclusive tax
2580 ln_vamt_nr:=0; --added by walton for inclusive tax
2581 END IF;
2582
2583 ELSE
2584
2585 tax_amt_tab(I) := 0;
2586 base_tax_amt_tab(I) := 0;
2587
2588 END IF;
2589
2590 END LOOP;
2591 FOR I IN 1..row_count LOOP
2592 IF end_date_tab( I ) <> 0 THEN
2593 IF tax_rate_tab(I) <> 0 THEN
2594 IF P1(I) > I THEN
2595 vamt := vamt + NVL(tax_amt_tab(P1(I)),0);
2596 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P1(I)),0);
2597 END IF;
2598 IF p2(I) > I THEN
2599 vamt := vamt + NVL(tax_amt_tab(p2(I)),0);
2600 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P2(I)),0);
2601 END IF;
2602 IF p3(I) > I THEN
2603 vamt := vamt + NVL(tax_amt_tab(p3(I)),0);
2604 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P3(I)),0);
2605 END IF;
2606 IF p4(I) > I THEN
2607 vamt := vamt + NVL(tax_amt_tab(p4(I)),0);
2608 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P4(I)),0);
2609 END IF;
2610 IF p5(I) > I THEN
2611 vamt := vamt + NVL(tax_amt_tab(p5(I)),0);
2612 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P5(I)),0);
2613 END IF;
2614 IF P6(I) > I THEN
2615 vamt := vamt + NVL(tax_amt_tab(P6(I)),0);
2616 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P6(I)),0);
2617 END IF;
2618 IF p7(I) > I THEN
2619 vamt := vamt + NVL(tax_amt_tab(p7(I)),0);
2620 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P7(I)),0);
2621 END IF;
2622 IF p8(I) > I THEN
2623 vamt := vamt + NVL(tax_amt_tab(p8(I)),0);
2624 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P8(I)),0);
2625 END IF;
2626 IF p9(I) > I THEN
2627 vamt := vamt + NVL(tax_amt_tab(p9(I)),0);
2628 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P9(I)),0);
2629 END IF;
2630 IF p10(I) > I THEN
2631 vamt := vamt + NVL(tax_amt_tab(p10(I)),0);
2632 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P10(I)),0);
2633 END IF;
2634 base_tax_amt_tab(I) := vamt;
2635 v_tax_amt := v_tax_amt + (vamt * (tax_rate_tab(I)/100));
2636 ln_tax_amt_nr:=ln_tax_amt_nr+(ln_vamt_nr * (tax_rate_tab(I)/100));
2637 IF vamt <> 0 THEN
2638 base_tax_amt_tab(I) := base_tax_amt_tab(I) + vamt;
2639 END IF;
2640 tax_amt_tab(I) := NVL(tax_amt_tab(I),0) + v_tax_amt;
2641 lt_tax_amt_non_rate_tab(I):=NVL(lt_tax_amt_non_rate_tab(I),0)+ln_tax_amt_nr; --added by walton for inclusive tax
2642 lt_tax_amt_rate_tax_tab(I):= tax_amt_tab(I); --added by walton for inclusive tax
2643 tax_amt_tab(I) := round(tax_amt_tab(I) ,round_factor_tab(I)); --added by csahoo for bug#6077133
2644 vamt := 0;
2645 v_tax_amt := 0;
2646 ln_vamt_nr :=0; --added by walton for inclusive tax
2647 ln_tax_amt_nr :=0; --added by walton for inclusive tax
2648 END IF; --End of tax_rate_tab(I) <> 0
2649 ELSE
2650 base_tax_amt_tab(I) := vamt;
2651 tax_amt_tab(I) := 0;
2652 END IF; --End of end_date_tab( I ) <> 0
2653 END LOOP;
2654
2655 FOR counter IN 1 .. max_iter LOOP
2656 vamt := 0;
2657 v_tax_amt := 0;
2658 ln_vamt_nr:= 0; --added by walton for inclusive tax
2659 ln_tax_amt_nr:=0; --added by walton for inclusive tax
2660
2661 FOR i IN 1 .. row_count LOOP
2662
2663 /*
2664 || Modified by aiyer for the fwd porting bug 4691616.
2665 || The following if clause will restrict the taxes whose tax_rate is null
2666 || i.e when tax_rate is null, tax_rate_tab(i) is 0.
2667 */
2668 IF ( tax_rate_tab( i ) <> 0 OR
2669 tax_rate_zero_tab(I) = -9999
2670 ) AND
2671 end_date_tab( I ) <> 0
2672 THEN
2673
2674 IF tax_type_tab( I ) = 1 THEN
2675 IF ln_assessable_value =1
2676 THEN
2677 v_amt:=1;
2678 ln_bsln_amt_nr :=0;
2679 ELSE
2680 v_amt :=0;
2681 ln_bsln_amt_nr :=ln_assessable_value;
2682 END IF;
2683 ELSIF tax_type_tab(I) = 4 THEN
2684 IF ln_vat_assessable_value =1
2685 THEN
2686 v_amt:=1;
2687 ln_bsln_amt_nr :=0;
2688 ELSE
2689 v_amt :=0;
2690 ln_bsln_amt_nr :=ln_vat_assessable_value;
2691 END IF;
2692 ELSIF tax_type_tab(I) = 6 THEN
2693 v_amt:=0;
2694 ln_bsln_amt_nr :=0;
2695 ELSE
2696 IF ln_assessable_value IN ( 0, -1 ) OR tax_type_tab( I ) <> 1 THEN
2697 /* v_amt := p_tax_amount;*/
2698 v_amt:=1; --Added by walton for inclusive tax
2699 ln_bsln_amt_nr :=0; --Added by walton for inclusive tax
2700 ELSIF ln_vat_assessable_value IN ( 0, -1 ) OR tax_type_tab( I ) <> 4 THEN
2701 /* v_amt := p_tax_amount;*/
2702 v_amt:=1; --Added by walton for inclusive tax
2703 ln_bsln_amt_nr :=0; --Added by walton for inclusive tax
2704 END IF;
2705 END IF; --End of tax_type_tab( I ) = 1
2706
2707 IF P1( i ) <> -1 THEN
2708 IF P1( i ) <> 0 THEN
2709 vamt := vamt + tax_amt_tab( P1( I ) );
2710 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P1(I)),0);
2711 ELSIF P1(i) = 0 THEN
2712 vamt := vamt + v_amt;
2713 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
2714 END IF;
2715 END IF;
2716
2717 IF p2( i ) <> -1 THEN
2718 IF p2( i ) <> 0 THEN
2719 vamt := vamt + tax_amt_tab( p2( I ) );
2720 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P2(I)),0);
2721 ELSIF p2(i) = 0 THEN
2722 vamt := vamt + v_amt;
2723 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
2724 END IF;
2725 END IF;
2726 IF p3( i ) <> -1 THEN
2727 IF p3( i ) <> 0 THEN
2728 vamt := vamt + tax_amt_tab( p3( I ) );
2729 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P3(I)),0);
2730 ELSIF p3(i) = 0 THEN
2731 vamt := vamt + v_amt;
2732 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
2733 END IF;
2734 END IF;
2735
2736 IF p4( i ) <> -1 THEN
2737 IF p4( i ) <> 0 THEN
2738 vamt := vamt + tax_amt_tab( p4( i ) );
2739 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P4(I)),0);
2740 ELSIF p4(i) = 0 THEN
2741 vamt := vamt + v_amt;
2742 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
2743 END IF;
2744 END IF;
2745
2746 IF p5( i ) <> -1 THEN
2747 IF p5( i ) <> 0 THEN
2748 vamt := vamt + tax_amt_tab( p5( i ) );
2749 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P5(I)),0);
2750 ELSIF p5(i) = 0 THEN
2751 vamt := vamt + v_amt;
2752 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
2753 END IF;
2754 END IF;
2755
2756 IF P6( i ) <> -1 THEN
2757 IF P6( i ) <> 0 THEN
2758 vamt := vamt + tax_amt_tab( P6( I ) );
2759 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P6(I)),0);
2760 ELSIF P6(i) = 0 THEN
2761 vamt := vamt + v_amt;
2762 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
2763 END IF;
2764 END IF;
2765
2766 IF p7( i ) <> -1 THEN
2767 IF p7( i ) <> 0 THEN
2768 vamt := vamt + tax_amt_tab( p7( I ) );
2769 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P7(I)),0);
2770 ELSIF p7(i) = 0 THEN
2771 vamt := vamt + v_amt;
2772 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
2773 END IF;
2774 END IF;
2775 IF p8( i ) <> -1 THEN
2776 IF p8( i ) <> 0 THEN
2777 vamt := vamt + tax_amt_tab( p8( I ) );
2778 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P8(I)),0);
2779 ELSIF p8(i) = 0 THEN
2780 vamt := vamt + v_amt;
2781 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
2782 END IF;
2783 END IF;
2784
2785 IF p9( i ) <> -1 THEN
2786 IF p9( i ) <> 0 THEN
2787 vamt := vamt + tax_amt_tab( p9( i ) );
2788 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P9(I)),0);
2789 ELSIF p9(i) = 0 THEN
2790 vamt := vamt + v_amt;
2791 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
2792 END IF;
2793 END IF;
2794
2795 IF p10( i ) <> -1 THEN
2796 IF p10( i ) <> 0 THEN
2797 vamt := vamt + tax_amt_tab( p10( i ) );
2798 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P10(I)),0);
2799 ELSIF p10(i) = 0 THEN
2800 vamt := vamt + v_amt;
2801 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
2802 END IF;
2803 END IF;
2804
2805 base_tax_amt_tab(I) := vamt;
2806 tax_target_tab(I) := vamt;
2807 v_func_tax_amt := v_tax_amt + ( vamt * ( tax_rate_tab( i )/100));
2808 v_tax_amt := v_tax_amt + ( vamt * ( tax_rate_tab( i )/100));
2809
2810 ELSIF tax_rate_tab(I) = 0 THEN
2811 base_tax_amt_tab(I) := tax_amt_tab(i);
2812 v_tax_amt := tax_amt_tab( i );
2813 tax_target_tab(I) := v_tax_amt;
2814 ELSIF end_date_tab( I ) = 0 THEN
2815 tax_amt_tab(I) := 0;
2816 base_tax_amt_tab(I) := 0;
2817 tax_target_tab(I) := 0;
2818 END IF;
2819
2820 tax_amt_tab( I ) := NVL( v_tax_amt, 0 );
2821 lt_tax_amt_rate_tax_tab(I) := tax_amt_tab(I);
2822 func_tax_amt_tab(I) := NVL(v_func_tax_amt,0);
2823
2824 IF counter = max_iter THEN
2825 IF end_date_tab(I) = 0 THEN
2826 tax_amt_tab( i ) := 0;
2827 func_tax_amt_tab(i) := 0;
2828 END IF;
2829 END IF;
2830
2831 vamt := 0;
2832 v_amt := 0;
2833 v_tax_amt := 0;
2834 v_func_tax_amt := 0;
2835 ln_vamt_nr :=0; --added by walton for inclusive tax
2836 ln_tax_amt_nr:=0; --added by walton for inclusive tax
2837
2838 END LOOP;
2839
2840 END LOOP;
2841
2842 FOR I IN 1 .. ROW_COUNT --Compute Factor
2843 LOOP
2844 jai_cmn_utils_pkg.print_log('utils.log','lt_tax_amt_rate_tax_tab(I) = ' || lt_tax_amt_rate_tax_tab(I));
2845 jai_cmn_utils_pkg.print_log('utils.log','lt_tax_amt_non_rate_tab(I) = ' || lt_tax_amt_non_rate_tab(I));
2846 jai_cmn_utils_pkg.print_log('utils.log','inclu flag = ' || lt_inclusive_tax_tab(I));
2847 IF lt_inclusive_tax_tab(I) = 'Y'
2848 THEN
2849 ln_total_tax_per_rupee := ln_total_tax_per_rupee + nvl(lt_tax_amt_rate_tax_tab(I),0) ;
2850 ln_total_non_rate_tax := ln_total_non_rate_tax + nvl(lt_tax_amt_non_rate_tab(I),0);
2851 END IF;
2852 END LOOP; --End Compute Factor
2853
2854 ln_total_tax_per_rupee := ln_total_tax_per_rupee + 1;
2855
2856 IF ln_total_tax_per_rupee <> 0
2857 THEN
2858 ln_exclusive_price := (NVL(v_precedence_0,0) - ln_total_non_rate_tax ) / ln_total_tax_per_rupee;
2859 END If;
2860 jai_cmn_utils_pkg.print_log('utils.log','tot tax per rupee = ' || ln_total_tax_per_rupee
2861 || 'totl non tax = ' || ln_total_non_rate_tax );
2862 jai_cmn_utils_pkg.print_log('utils.log','incl sp = ' || v_precedence_0 || 'excl price = ' || ln_exclusive_price);
2863
2864 FOR i in 1 .. row_count --Compute Tax Amount
2865 Loop
2866 tax_amt_tab (i) := (lt_tax_amt_rate_tax_tab(I) * ln_exclusive_price ) + lt_tax_amt_non_rate_tab(I);
2867 tax_amt_tab(I) := round(tax_amt_tab(I) ,round_factor_tab(I));
2868 jai_cmn_utils_pkg.print_log('utils.log','in final loop , tax amt is ' ||tax_amt_tab(I));
2869 END LOOP; --End Compute Tax Amount
2870 --------------------------------------------------------------------------------------------------------
2871 FOR i in 1.. lt_tax_table.count LOOP
2872 po_lines_rec := lt_tax_table(i);
2873 v_cor_amount := nvl(tax_amt_tab(i), 0);
2874 /* Added by LGOPLASA. Bug 4210102.
2875 * Added CVD, Excise and customs education cess */
2876 IF upper(po_lines_rec.tax_type) IN ('EXCISE', 'ADDL. EXCISE', 'OTHER EXCISE', 'CUSTOMS', 'CVD',
2877 JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS,jai_constants.tax_type_exc_edu_cess,
2878 JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS,jai_constants.tax_type_cvd_edu_cess,
2879 JAI_CONSTANTS.TAX_TYPE_SH_CUSTOMS_EDU_CESS,jai_constants.tax_type_customs_edu_cess)/*Bug5904736 bduvarag*/ THEN
2880 -- v_claimable_amount := NVL(v_cor_amount * v_conv_factor, 0) * NVL(lines_rec.duty, 0) / 100;
2881 -- above line commented by Aparajita for bug#2929171
2882 v_claimable_amount := NVL(v_cor_amount, 0) * NVL(po_lines_rec.duty, 0) / 100;
2883 ELSE
2884 v_claimable_amount := 0;
2885 END IF;
2886
2887 --Added by GSRI on 21-OCT-01
2888 SELECT COUNT(*)
2889 INTO v_chk_receipt_tax_lines
2890 FROM JAI_RCV_LINE_TAXES
2891 WHERE shipment_line_id = p_shipment_line_id
2892 AND shipment_header_id = p_shipment_header_id
2893 AND tax_id = po_lines_rec.tax_id;
2894
2895 IF v_chk_receipt_tax_lines = 0 THEN
2896 /*DELETE FROM JAI_RCV_LINE_TAXES
2897 WHERE shipment_line_id = p_shipment_line_id AND
2898 shipment_header_id = p_shipment_header_id AND
2899 tax_id = tax_rec.tax_id;*/
2900 --End Addition by on GSRI 21-OCT-01
2901
2902 INSERT INTO JAI_RCV_LINE_TAXES
2903 (
2904 shipment_line_id,
2905 tax_line_no,
2906 shipment_header_id,
2907 tax_id,
2908 tax_name,
2909 currency,
2910 tax_rate,
2911 qty_rate,
2912 uom,
2913 tax_amount,
2914 tax_type,
2915 modvat_flag,
2916 creation_date,
2917 created_by,
2918 last_update_date,
2919 last_updated_by,
2920 last_update_login,
2921 vendor_id,
2922 claimable_amount,
2923 vendor_site_id, --Added by Nagaraj.s for Bug3037075
2924 third_party_flag,
2925 --3848133
2926 precedence_1,
2927 precedence_2,
2928 precedence_3,
2929 precedence_4,
2930 precedence_5,
2931 precedence_6, -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
2932 precedence_7,
2933 precedence_8,
2934 precedence_9,
2935 precedence_10,
2936 transaction_id
2937 --3848133
2938 )
2939 VALUES
2940 (
2941 p_shipment_line_id,
2942 po_lines_rec.tax_line_no,
2943 p_shipment_header_id,
2944 po_lines_rec.tax_id,
2945 po_lines_rec.tax_name,
2946 po_lines_rec.currency,
2947 po_lines_rec.tax_rate,
2948 po_lines_rec.qty_rate,
2949 po_lines_rec.uom,
2950 tax_amt_tab(i),
2951 po_lines_rec.tax_type,
2952 lt_tax_modvat_flag(i),
2953 p_creation_date,
2954 p_created_by,
2955 p_last_update_date,
2956 p_last_updated_by,
2957 p_last_update_login,
2958 po_lines_rec.vendor_id,
2959 v_claimable_amount,
2960 v_tax_vendor_site_id, --Added by Nagaraj.s for Bug3037075
2961 lt_third_party_flag(i),
2962 --3848133
2963 po_lines_rec.precedence_1,
2964 po_lines_rec.precedence_2,
2965 po_lines_rec.precedence_3,
2966 po_lines_rec.precedence_4,
2967 po_lines_rec.precedence_5,
2968 po_lines_rec.precedence_6, -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
2969 po_lines_rec.precedence_7,
2970 po_lines_rec.precedence_8,
2971 po_lines_rec.precedence_9,
2972 po_lines_rec.precedence_10,
2973 p_transaction_id
2974 --3848133
2975 );
2976 END IF;
2977
2978 IF po_lines_rec.tax_type NOT IN ('TDS', 'Modvat Recovery') THEN
2979 v_tax_total := v_tax_total + NVL(v_cor_amount * v_conv_factor, 0);
2980 END IF;
2981 IF v_debug_flag = 'Y' THEN
2982 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_tax_total is ' ||v_tax_total);
2983 END IF;
2984 END LOOP;
2985 END IF; ----Add by Kevin Cheng for bug 6853787 Mar 5, 2008
2986
2987 IF p_transaction_type = 'RECEIVE' -- AND was commented by GSRI on 21-OCT-01 and OR was added
2988 /* R12-PADDR OR v_chk_form IS NOT NULL */
2989 THEN
2990 update_receipt_line;
2991 END IF;
2992
2993
2994 IF p_transaction_type = 'MATCH' THEN
2995
2996 UPDATE JAI_RCV_LINES
2997 SET -- tax_modified_flag = 'N', /* Vijay Shankar for Bug#3940588 RECEIPTS DEPLUG*/
2998 line_location_id = p_line_location_id,
2999 tax_amount = NVL(tax_amount, 0) + v_tax_total,
3000 last_update_date = p_last_update_date,
3001 last_updated_by = p_last_updated_by,
3002 last_update_login = p_last_update_login
3003 WHERE shipment_line_id = p_shipment_line_id;
3004 END IF;
3005
3006
3007 END IF;
3008 -- ADDED FOR BAR-CODING BY GSri 21-OCT-01
3009
3010
3011 /* R12-PADDR p_chk_form := v_chk_form; -- Vijay Shankar for Bug#3940588. RECEIPTS DEPLUG
3012 */
3013
3014 -- END FOR BAR-CODING BY GSri and on 21-OCT-01
3015 IF v_receipt_source_code IS NOT NULL AND
3016 p_transaction_type = 'RECEIVE' AND
3017 v_receipt_source_code = 'INTERNAL ORDER' AND
3018 NVL(v_duplicate_ship, 'N') = 'N'
3019 THEN
3020 set_receipt_flag;
3021 -- v_receipt_modify_flag := 'N'; --commented by csahoo for bug#6154234
3022 insert_receipt_line;
3023
3024 -- ssumaith - bug# 3657662
3025 open c_order_cur(v_shipment_num);
3026 fetch c_order_cur into v_order_header_id;
3027 close c_order_cur;
3028
3029 if v_debug_flag = 'Y' THEN
3030 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_order_header_id is ' || v_order_header_id);
3031 end if;
3032 -- ssumaith - bug# 3657662
3033
3034 --Changed by Nagaraj.s on 30/07/2003 for Bug#2993865
3035 FOR line_rec IN (
3036 SELECT
3037 line_id,
3038 sha.transactional_curr_code,
3039 spl.delivery_detail_id,
3040 spl.quantity,
3041 spl.unit_code
3042
3043 FROM
3044
3045 oe_order_lines_all sla,
3046 oe_order_headers_all sha,
3047 po_requisition_headers_all prha,
3048 po_requisition_lines_all prla,
3049 JAI_OM_WSH_LINES_ALL spl,
3050 rcv_shipment_headers rsh
3051
3052 WHERE
3053
3054 prha.requisition_header_id = prla.requisition_header_id
3055 and sha.source_document_id = prha.requisition_header_id
3056 AND sla.header_id = sha.header_id
3057 AND sha.header_id in /* following subquery added by ssumaith - 3772135*/
3058 (
3059 select order_header_id
3060 from JAI_OM_WSH_LINES_ALL
3061 where delivery_id = v_shipment_num
3062 )
3063 /* = v_order_header_id -- ssumaith - bug# 3657662*/
3064 and sla.source_document_line_id = prla.requisition_line_id
3065 /*and to_char(spl.delivery_id) = rsh.shipment_num*/
3066 --and spl.delivery_id = rsh.shipment_num
3067 --commented the above and added the below by Ramananda for Bug#4533114
3068 and spl.delivery_id = decode(ltrim(translate(shipment_num,'0123456789','~'),'~'),NULL,rsh.shipment_num,
3069 (select delivery_id from wsh_new_deliveries where name=rsh.shipment_num))
3070 and spl.order_line_id = sla.line_id
3071 and prla.requisition_line_id = p_requisition_line_id
3072 AND rsh.shipment_header_id = p_shipment_header_id
3073 and rownum <= (select line_num from rcv_shipment_lines where shipment_line_id = p_shipment_line_id) -- bug#3878439
3074 )
3075
3076
3077 LOOP
3078 v_line_id := line_rec.line_id;
3079 v_so_currency := line_rec.transactional_curr_code;
3080 v_loc_quantity := line_rec.quantity;
3081 v_picking_line_id := line_rec.delivery_detail_id;
3082
3083 -- start adding by Aparajita for bug # 2813244 on 05/03/2003
3084 IF v_rcv_uom_code <> line_rec.unit_code THEN
3085
3086 Inv_Convert.inv_um_conversion
3087 (
3088 v_rcv_uom_code,
3089 line_rec.unit_code,
3090 v_item_id,
3091 v_uom_rate
3092 );
3093
3094 IF v_uom_rate = -99999 THEN
3095 v_uom_rate := 0;
3096 END IF;
3097
3098 ELSE
3099
3100 v_uom_rate := 1;
3101
3102 END IF; --End if for v_rcv_uom_code
3103
3104
3105 v_uom_rate := NVL(v_uom_rate, 1);
3106
3107 IF v_debug_flag = 'Y' THEN
3108 UTL_FILE.PUT_LINE(v_myfilehandle, ' v_uom_rate:' || v_uom_rate
3109 ||', v_line_id:' || v_line_id|| ', v_so_currency:' || v_so_currency
3110 ||', v_loc_quantity:' || v_loc_quantity||', delivery_detail_id:' || v_picking_line_id
3111 );
3112 END IF;
3113 -- end adding by Aparajita for bug # 2813244 on 05/03/2003
3114
3115 END LOOP; --Added by Jagdish 13-sep-01
3116
3117 IF v_currency_code <> v_func_currency THEN
3118 v_conv_factor := NVL(v_currency_conversion_rate, 1);
3119 ELSE
3120 v_conv_factor := 1;
3121 END IF;
3122
3123 FOR tax_rec IN
3124 (
3125 SELECT tax_line_no,
3126 stl.tax_id,
3127 stl.tax_rate,
3128 stl.qty_rate,
3129 uom,
3130 stl.tax_amount,
3131 base_tax_amount,
3132 func_tax_amount,
3133 jtc.tax_name,
3134 jtc.tax_type,
3135 jtc.vendor_id,
3136 jtc.mod_cr_percentage,
3137 jtc.rounding_factor,
3138 jtc.duty_drawback_percentage duty,
3139 --3848133
3140 stl.precedence_1,
3141 stl.precedence_2,
3142 stl.precedence_3,
3143 stl.precedence_4,
3144 stl.precedence_5,
3145 stl.precedence_6, -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
3146 stl.precedence_7,
3147 stl.precedence_8,
3148 stl.precedence_9,
3149 stl.precedence_10,
3150 tax_types.regime_code regime_code
3151 --3848133
3152 FROM JAI_OM_WSH_LINE_TAXES stl,
3153 JAI_CMN_TAXES_ALL jtc,
3154 jai_regime_tax_types_v tax_types
3155 WHERE delivery_detail_id = v_picking_line_id --added by GSri and Jagdish on 5-may-01
3156 AND jtc.tax_id = stl.tax_id
3157 AND tax_types.tax_type(+) = jtc.tax_type
3158 )
3159 LOOP
3160
3161 IF NVL(v_loc_quantity, 0) <> 0 THEN
3162 v_cor_amount := ROUND(( P_qty_received * tax_rec.tax_amount * v_uom_rate / v_loc_quantity),
3163 NVL(tax_rec.rounding_factor, 0));
3164 END IF;
3165
3166 -- Start of addition by Srihari on 30-NOV-99
3167
3168 IF v_item_modvat_flag = 'N' AND upper(tax_rec.tax_type) IN ('EXCISE', 'ADDL. EXCISE', 'OTHER EXCISE',
3169 'CVD', JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS,jai_constants.tax_type_exc_edu_cess, JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS,jai_constants.tax_type_cvd_edu_cess)/*Bug5989740 bduvarag*/
3170 THEN
3171 v_tax_modvat_flag := 'N';
3172 -- End of addition by Srihari on 30-NOV-99
3173
3174 /* following elsif added by Vijay Shankar for Bug#4250236(4245089). VAT Impl. */
3175 ELSIF lv_vat_recoverable_for_item <> jai_constants.yes
3176 AND tax_rec.regime_code = jai_constants.vat_regime
3177 THEN
3178 v_tax_modvat_flag := jai_constants.no;
3179
3180 ELSIF tax_rec.mod_cr_percentage > 0 THEN
3181 v_tax_modvat_flag := 'Y';
3182 ELSE
3183 v_tax_modvat_flag := 'N';
3184 END IF;
3185
3186 IF tax_rec.vendor_id IS NULL THEN
3187 v_internal_vendor := - v_rsh_organization_id;
3188 ELSE
3189 v_internal_vendor := tax_rec.vendor_id;
3190 END IF;
3191
3192 /* Added by LGOPALSa. Bug 4210102.
3193 * ADded Excise, Customs and CVD education cess */
3194
3195 IF upper(tax_rec.tax_type) IN ('EXCISE', 'ADDL. EXCISE', 'OTHER EXCISE', 'CUSTOMS', 'CVD',
3196 JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS,jai_constants.tax_type_exc_edu_cess,
3197 JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS,jai_constants.tax_type_cvd_edu_cess,
3198 JAI_CONSTANTS.TAX_TYPE_SH_CUSTOMS_EDU_CESS,jai_constants.tax_type_customs_edu_cess/*Bug5989740 bduvarag*/
3199 )
3200 THEN
3201 v_claimable_amount := NVL(v_cor_amount * v_conv_factor, 0) * NVL(tax_rec.duty, 0) / 100;
3202 ELSE
3203 v_claimable_amount := 0;
3204 END IF;
3205
3206 -- Added by GSRI on 21-OCT-01
3207 SELECT COUNT(*)
3208 INTO v_chk_receipt_tax_lines
3209 FROM JAI_RCV_LINE_TAXES
3210 WHERE shipment_line_id = p_shipment_line_id
3211 AND shipment_header_id = p_shipment_header_id
3212 AND tax_id = tax_rec.tax_id;
3213
3214 -- END of Addition by GSRI on 21-OCT-01
3215 IF v_chk_receipt_tax_lines = 0 THEN
3216
3217 INSERT INTO JAI_RCV_LINE_TAXES
3218 (
3219 shipment_line_id,
3220 tax_line_no,
3221 shipment_header_id,
3222 tax_id,
3223 tax_name,
3224 currency,
3225 tax_rate,
3226 qty_rate,
3227 uom,
3228 tax_amount,
3229 tax_type,
3230 modvat_flag,
3231 creation_date,
3232 created_by,
3233 last_update_date,
3234 last_updated_by,
3235 last_update_login,
3236 vendor_id,
3237 --3848133
3238 precedence_1,
3239 precedence_2,
3240 precedence_3,
3241 precedence_4,
3242 precedence_5,
3243 precedence_6, -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
3244 precedence_7,
3245 precedence_8,
3246 precedence_9,
3247 precedence_10,
3248 transaction_id
3249 --3848133
3250 )
3251 VALUES
3252 (
3253 p_shipment_line_id,
3254 tax_rec.tax_line_no,
3255 p_shipment_header_id,
3256 tax_rec.tax_id,
3257 tax_rec.tax_name,
3258 p_currency_code,
3259 tax_rec.tax_rate,
3260 tax_rec.qty_rate,
3261 tax_rec.uom,
3262 v_cor_amount,
3263 tax_rec.tax_type,
3264 v_tax_modvat_flag,
3265 p_creation_date,
3266 p_created_by,
3267 p_last_update_date,
3268 p_last_updated_by,
3269 p_last_update_login,
3270 v_internal_vendor,
3271 --3848133
3272 tax_rec.precedence_1,
3273 tax_rec.precedence_2,
3274 tax_rec.precedence_3,
3275 tax_rec.precedence_4,
3276 tax_rec.precedence_5,
3277 tax_rec.precedence_6, -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
3278 tax_rec.precedence_7,
3279 tax_rec.precedence_8,
3280 tax_rec.precedence_9,
3281 tax_rec.precedence_10,
3282 p_transaction_id
3283 --3848133
3284 );
3285 END IF;
3286
3287 IF tax_rec.tax_type NOT IN ('TDS', 'Modvat Recovery') THEN
3288 v_tax_total := v_tax_total + NVL(v_cor_amount * v_conv_factor, 0);
3289 END IF;
3290
3291 END LOOP;
3292
3293 update_receipt_line;
3294
3295 END IF;
3296
3297 IF v_debug_flag = 'Y' THEN
3298 UTL_FILE.PUT_LINE(v_myfilehandle,'Before the condition if p_transaction_type is UNORDERED ');
3299 END IF;
3300
3301 IF p_transaction_type = 'UNORDERED'
3302 /* R12-PADDR AND v_chk_form IS NOT NULL */
3303 THEN
3304
3305 IF v_debug_flag = 'Y' THEN
3306 UTL_FILE.PUT_LINE(v_myfilehandle,'Before set receipt flag');
3307 END IF;
3308
3309 set_receipt_flag;
3310
3311 IF v_debug_flag = 'Y' THEN
3312 UTL_FILE.PUT_LINE(v_myfilehandle,'After set receipt flag');
3313 END IF;
3314
3315 v_receipt_modify_flag := 'N';
3316
3317 IF v_debug_flag = 'Y' THEN
3318 UTL_FILE.PUT_LINE(v_myfilehandle,'Before Insert Receipt Line Procedure');
3319 END IF;
3320
3321 insert_receipt_line;
3322
3323 IF v_debug_flag = 'Y' THEN
3324 UTL_FILE.PUT_LINE(v_myfilehandle,'After Insert Receipt Line Procedure');
3325 END IF;
3326
3327 END IF;
3328
3329 END IF;
3330
3331
3332 -- Vijay Shankar for Bug#3940588. no more processing is required. so return back
3333 -- GOTO end_of_procedure;
3334 -- NULL;
3335
3336 /* A lot of Code is COMMENTED by Vijay Shankar for Bug#3940588. RECEIPTS DEPLUG
3337 Please refer to Old Version of the Object incase commented code needs to be looked at
3338 */
3339
3340 -- <<end_of_procedure>>
3341
3342 /* Validation for RETURN TO VENDOR transactions. following check is not required for Correct transactions because
3343 RTV creation should have taken care of this check
3344 Vijay Shankar for Bug#3940588. RECEIPTS DEPLUG */
3345 FOR exc_rec IN (SELECT jrtl.tax_amount, jrtl.tax_type tax_type, nvl(mod_cr_percentage, 0) mod_cr_percentage
3346 , nvl(tax_types.regime_code, 'XXXX') regime_code
3347 FROM JAI_RCV_LINE_TAXES jrtl, JAI_CMN_TAXES_ALL jtc, jai_regime_tax_types_v tax_types
3348 WHERE shipment_line_id = p_shipment_line_id
3349 AND NVL(modvat_flag, 'N') = 'Y'
3350 AND jtc.tax_type = tax_types.tax_type(+)
3351 AND jrtl.tax_id = jtc.tax_id)
3352 LOOP
3353
3354 if upper(exc_rec.tax_type) IN ('EXCISE', 'CVD', 'ADDL. EXCISE', 'OTHER EXCISE',
3355 JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS,jai_constants.tax_type_exc_edu_cess, JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS,jai_constants.tax_type_cvd_edu_cess)/*Bug5989740 bduvarag*/
3356 then
3357 v_chk_excise := NVL(v_chk_excise, 0) + (exc_rec.tax_amount * exc_rec.mod_cr_percentage / 100);
3358
3359 /* following added by Vijay Shankar for Bug#4250236(4245089). VAT Impl. */
3360 elsif exc_rec.regime_code = jai_constants.vat_regime then
3361 ln_chk_vat := nvl(ln_chk_vat, 0) + (exc_rec.tax_amount * exc_rec.mod_cr_percentage / 100);
3362 end if;
3363
3364 END LOOP;
3365
3366 /* following if condition for receive and match added by Vijay shankar for Bug#4250236(4245089) VAT Impl. */
3367 IF p_transaction_type in ('RECEIVE', 'MATCH') THEN
3368
3369 IF v_rg_location_id = 0 THEN
3370 v_rg_location_id := null;
3371 END IF;
3372
3373 open c_rgm_setup_for_orgn_loc(jai_constants.vat_regime, jai_constants.orgn_type_io,
3374 p_organization_id, v_rg_location_id);
3375 fetch c_rgm_setup_for_orgn_loc into ln_vat_setup_chk;
3376 close c_rgm_setup_for_orgn_loc;
3377
3378 fnd_file.put_line( fnd_file.log, 'VAT SetupChkforOrgnLoc:'||ln_vat_setup_chk
3379 ||', ChkVat:'||nvl(ln_chk_vat, -999999)
3380 ||', Orgn:'||p_organization_id||', Loc:'||v_rg_location_id
3381 );
3382
3383 IF nvl(ln_vat_setup_chk,0) = 0 and nvl(ln_chk_vat,0) <> 0 THEN
3384 errormsg:='Organization and Location is not attached to VAT Regime';
3385 RAISE_APPLICATION_ERROR (-20502, 'Organization and Location is not attached to VAT Regime');
3386 END IF;
3387
3388 ELSIF p_transaction_type = 'RETURN TO VENDOR' THEN
3389
3390 -- NULL; --commented by bgowrava for Bug#6459894
3391 /* Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.*/
3392 /* Uncommented below statements by bgowrava for Bug#6459894*/
3393 open c_fetch_unclaim_cenvat;
3394 fetch c_fetch_unclaim_cenvat into v_unclaim_cenvat_flag,
3395 v_cenvat_claimed_ptg,
3396 v_non_bonded_delivery_flag,
3397 v_cenvat_amount;
3398 close c_fetch_unclaim_cenvat;
3399
3400 /* IF v_debug_flag = 'Y' THEN
3401 UTL_FILE.PUT_LINE(v_myfilehandle,' ABC1. v_register_type:' || v_register_type
3402 || ', v_chk_excise:' || v_chk_excise||', v_item_modvat_flag:' || v_item_modvat_flag
3403 ||', v_item_class:' || v_item_class||', chkVat:'||ln_chk_vat );
3404 UTL_FILE.PUT_LINE(v_myfilehandle,' ABC2. v_cenvat_claimed_ptg:' || v_cenvat_claimed_ptg
3405 ||', v_manufacturing:' || v_manufacturing ||', v_non_bonded_flag:' || v_non_bonded_delivery_flag
3406 ||', unclaim_flag:'||v_unclaim_cenvat_flag);
3407 END IF;*/
3408
3409 FOR i IN (select manufacturing from JAI_CMN_INVENTORY_ORGS where organization_id=p_organization_id) loop
3410 v_manufacturing := i.manufacturing;
3411 EXIT WHEN v_manufacturing IS NOT NULL;
3412 END LOOP;
3413
3414 open c_rcv_rgm_dtl(jai_constants.vat_regime, p_shipment_line_id);
3415 fetch c_rcv_rgm_dtl into r_rcv_rgm_dtl;
3416 close c_rcv_rgm_dtl;
3417
3418 /* Bug 4516678. Added by Lakshmi Gopalsami */
3419 pick_register_type(p_organization_id,
3420 v_item_id,
3421 v_register_type );
3422
3423 IF NVL(v_item_modvat_flag, 'N') = 'Y'
3424 AND NVL(v_chk_excise, 0) <> 0
3425 AND v_item_class in ('RMIN','RMEX','CGIN', 'CGEX','CCIN','CCEX')
3426 AND nvl(v_cenvat_claimed_ptg,0) = 0
3427 AND nvl(v_manufacturing, 'N') = 'Y'
3428 AND nvl(v_non_bonded_delivery_flag,'N') = 'N'
3429 AND nvl(v_unclaim_cenvat_flag,'N') = 'N'
3430 THEN
3431 errormsg:='RTV not allowed as the CENVAT has not been claimed';
3432 RAISE_APPLICATION_ERROR (-20501, 'RTV not allowed as the CENVAT has not been claimed');
3433 --END IF; commented for bug4516678
3434
3435 /* Bug 4516678. Added by Lakshmi Gopalsami
3436 Added the following code
3437 */
3438 ELSIF v_register_type ='C' -- implies item is CGIN /CGEX
3439 and v_cenvat_amount > 0
3440 and v_cenvat_claimed_ptg < 100 then
3441
3442 -- fetch the receipt quantity
3443 open c_fetch_receive_quantity(p_shipment_header_id,
3444 p_shipment_line_id);
3445 fetch c_fetch_receive_quantity into v_receipt_quantity;
3446 close c_fetch_receive_quantity;
3447
3448 -- fetch the total quantity returned
3449
3450 open c_fetch_transaction_quantity(p_shipment_header_id,
3451 p_shipment_line_id,'RETURN TO VENDOR');
3452 fetch c_fetch_transaction_quantity into v_sum_rtv_quantity;
3453 close c_fetch_transaction_quantity;
3454
3455 if v_receipt_quantity = v_sum_rtv_quantity and nvl(v_non_bonded_delivery_flag,'N') ='N' then --3456636,3273075
3456 FND_FILE.PUT_LINE(FND_FILE.LOG, ' 6_30 The RTV Quantity is Equal to Receipt Quantity and the Remaining 50% Cenvat is not availed ');
3457 FND_FILE.PUT_LINE(FND_FILE.LOG, ' 6_31 Please avail the remaining Cenvat credit and then proceed with RTV ' );
3458 errormsg:='RETURN TO VENDOR not allowed.Please claim remaining 50% cenvat' ;
3459 raise_application_error(-20110,'RETURN TO VENDOR not allowed.Please claim remaining 50% cenvat' );
3460 end if;
3461 End if; -- End for bug 4516678
3462
3463
3464 fnd_file.put_line( fnd_file.log, 'ItmModFlg:'||v_item_modvat_flag
3465 ||', ChkVat:'||nvl(ln_chk_vat, -999999)
3466 ||', procStaFlg:'||r_rcv_rgm_dtl.process_status_flag
3467 ||', invNo:'||nvl(r_rcv_rgm_dtl.invoice_no,'ABCDEF')
3468 );
3469
3470 -- following added by Vijay Shankar for Bug#4250236(4245089). VAT Impl.
3471 -- Before VAT is fully Claimed if RTV is being done, then we should error out the transaction
3472 /* IF lv_vat_recoverable_for_item = jai_constants.yes
3473 AND NVL(ln_chk_vat, 0) <> 0
3474 AND r_rcv_rgm_dtl.process_status_flag <> 'U' -- Not Unclaimed
3475 AND r_rcv_rgm_dtl.invoice_no IS NULL -- if this is given it means then VAT is eligible for claim
3476 THEN
3477 RAISE_APPLICATION_ERROR (-20502, 'RETURN TO VENDOR not allowed as VAT Amount is not Claimed');
3478 END IF;
3479 */ --commented by Ramananda for Bug #4530112
3480 END IF;
3481
3482 IF v_debug_flag = 'Y' THEN
3483 UTL_FILE.PUT_LINE(v_myfilehandle,'*********End of the Receipt taxes procedure********');
3484 UTL_FILE.FCLOSE(v_myfileHandle);
3485 END IF;
3486
3487
3488 /* Added by Ramananda for bug# exc_objects */
3489 EXCEPTION
3490 WHEN OTHERS THEN
3491 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
3492 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', '. Err:'||sqlerrm ); --bgowrava for bug#6459894
3493 app_exception.raise_exception (
3494 EXCEPTION_TYPE => 'APP',
3495 EXCEPTION_CODE => -20110 ,
3496 EXCEPTION_TEXT => errormsg
3497 );
3498
3499
3500 END default_taxes_onto_line;
3501
3502 END jai_rcv_tax_pkg;