1 PACKAGE BODY JAI_RCV_TAX_PKG AS
2 /* $Header: jai_rcv_tax.plb 120.49.12020000.9 2013/01/30 06:05:52 mbremkum ship $ */
3
4 PROCEDURE default_taxes_onto_line
5 (
6 p_transaction_id NUMBER,
7 p_parent_transaction_id NUMBER,
8 p_shipment_header_id NUMBER,
9 p_shipment_line_id NUMBER,
10 p_organization_id NUMBER,
11 p_requisition_line_id NUMBER,
12 p_qty_received NUMBER,
13 p_primary_quantity NUMBER,
14 p_line_location_id NUMBER,
15 p_transaction_type VARCHAR2,
16 p_source_document_code VARCHAR2,
17 p_destination_type_code VARCHAR2,
18 p_subinventory VARCHAR2,
19 p_vendor_id NUMBER,
20 p_vendor_site_id NUMBER,
21 p_po_header_id NUMBER,
22 p_po_line_id NUMBER,
23 p_location_id NUMBER,
24 p_transaction_date DATE,
25 p_uom_code VARCHAR2,
26 --Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. p_attribute1 VARCHAR2,
27 --p_attribute2 DATE,
28 --p_attribute3 VARCHAR2,
29 --p_attribute4 VARCHAR2,
30 p_attribute15 VARCHAR2,
31 p_currency_code VARCHAR2,
32 p_currency_conversion_type VARCHAR2,
33 p_currency_conversion_date DATE,
34 p_currency_conversion_rate NUMBER,
35 p_creation_date DATE,
36 p_created_by NUMBER,
37 p_last_update_date DATE,
38 p_last_updated_by NUMBER,
39 p_last_update_login NUMBER,
40 p_unit_of_measure VARCHAR2,
41 p_po_distribution_id NUMBER,
42 p_oe_order_header_id NUMBER,
43 p_oe_order_line_id NUMBER,
44 p_routing_header_id NUMBER,
45 -- Vijay Shankar for Bug#3940588 RECEIPTS DEPLUG
46 /* R12-PADDR p_chk_form OUT NOCOPY VARCHAR2, */
47 -- Vijay Shankar for Bug#4159557
48 p_interface_source_code VARCHAR2,
49 p_interface_transaction_id VARCHAR2,
50 p_allow_tax_change_hook VARCHAR2
51 --Reverted the chnage in R12 p_group_id IN NUMBER DEFAULT NULL /*added by nprashar for bug 8566481*/
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 v_assessable_val_defined NUMBER; -- by nkodakan for bug 14590356.
136
137 /* Vijay Shankar for Bug#4250236(4245089). VAT Impl. */
138 ln_vat_assess_value NUMBER; --File.Sql.35 Cbabu :=0;
139 ln_vat_assess_val_defined NUMBER; -- by nkodakan for the bug 14590356
140 ln_chk_vat number ;
141 lv_vat_recoverable_for_item JAI_RGM_ITM_TMPL_ATTRS.ATTRIBUTE_VALUE%TYPE;
142 lv_process_flag VARCHAR2(2);
143 lv_process_msg VARCHAR2(1000);
144 ln_vat_setup_chk NUMBER;
145 ln_test_delivery_id JAI_OM_WSH_LINES_ALL.delivery_id%TYPE; --Added by Ramananda for Bug#4533114
146 --Added by walton for inclusive tax
147 ---------------------------------------------
148 TYPE num_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
149 TYPE tax_amt_num_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
150 tax_amt_tab TAX_AMT_NUM_TAB;
151 round_factor_tab TAX_AMT_NUM_TAB;
152 base_tax_amt_tab TAX_AMT_NUM_TAB;
153 func_tax_amt_tab TAX_AMT_NUM_TAB;
154 v_tax_vendor_site_id TAX_AMT_NUM_TAB; /*Added by nprshar for bug # 10193326 */
155
156 TYPE char_tab IS TABLE OF VARCHAR2(10)
157 INDEX BY BINARY_INTEGER;
158
159 Type v_curr_check is table of VARCHAR2(10)
160 Index by binary_integer;
161 --commented out by shyan for bug 10100899 on 12/11/2010, Begin
162 --------------------------------------------------------------------
163 -- p_curr_check v_curr_check; /*Added by nprashar for bug # 10100899*/
164 --------------------------------------------------------------------
165 --commented out by shyan for bug 10100899 on 12/11/2010, End
166
167 lt_adhoc_tax_tab CHAR_TAB;
168 lt_inclusive_tax_tab CHAR_TAB;
169 lt_tax_modvat_flag CHAR_TAB;
170 lt_third_party_flag CHAR_TAB;
171 ln_exclusive_price NUMBER;
172 lt_tax_rate_per_rupee NUM_TAB;
173 lt_cumul_tax_rate_per_rupee NUM_TAB;
174 ln_total_non_rate_tax NUMBER :=0;
175 ln_total_inclusive_factor NUMBER;
176 lt_tax_amt_rate_tax_tab TAX_AMT_NUM_TAB;
177 lt_tax_amt_non_rate_tab TAX_AMT_NUM_TAB;
178 ln_bsln_amt_nr NUMBER :=0;
179 ln_tax_amt_nr NUMBER(38,10) :=0;
180 ln_vamt_nr NUMBER(38,10) :=0;
181 ln_total_tax_per_rupee NUMBER;
182 ln_assessable_value NUMBER;
183 ln_vat_assessable_value NUMBER;
184 ln_curflag NUMBER; --Add by Kevin Cheng for bug 6853787 Mar 5, 2008
185 lv_valid_date DATE;
186 --ln_gst_assessable_value NUMBER; --Add by Xiao for GST refer to bug#10043656 on 14-Sep-2010
187 --ln_gst_assess_value NUMBER; --Add by Xiao for GST refer to bug#10043656 on 14-Sep-2010
188 ln_amount number; --Added by Shujuan for 10358786 on 28-Jan-2011
189 --commented out by shyan for bug 10100899 on 12/11/2010, Begin
190 ---------------------------------------------------------------------------
191 -- ln_check_curr_conv NUMBER := 0; --Added by nprashar for bug # 10100899
192 -- p_index NUMBER; --Added by nprashar for bug # 10100899
193 ----------------------------------------------------------------------------
194 --commented out by shyan for bug 10100899 on 12/11/2010, End
195 p1 NUM_TAB;
196 p2 NUM_TAB;
197 p3 NUM_TAB;
198 p4 NUM_TAB;
199 p5 NUM_TAB;
200
201 p6 NUM_TAB;
202 p7 NUM_TAB;
203 p8 NUM_TAB;
204 p9 NUM_TAB;
205 p10 NUM_TAB;
206
207 end_date_tab NUM_TAB;
208 tax_rate_tab NUM_TAB;
209 tax_type_tab NUM_TAB;
210 tax_rate_zero_tab NUM_TAB;
211 tax_target_tab NUM_TAB;
212
213 bsln_amt NUMBER := 0;
214 vamt NUMBER := 0;
215 row_count NUMBER := 1;
216 v_tax_amt NUMBER := 0;
217 max_iter NUMBER := 10;
218 v_func_tax_amt NUMBER := 0;
219 v_amt NUMBER := 0;
220
221 errormsg VARCHAR2(500);
222
223 cursor c_get_inclusive_flag
224 ( pn_tax_id number
225 )
226 is
227 select NVL(inclusive_tax_flag,'N'),
228 end_date,
229 NVL(adhoc_flag,'N')--Added by qiong.liu for bug12717416 2011.07.06
230 from jai_cmn_taxes_all
231 where tax_id=pn_tax_id;
232 -----------------------------------------------
233
234 CURSOR c_rgm_setup_for_orgn_loc(cp_regime_code varchar2, cp_organization_type varchar2,
235 cp_organization_id number, cp_location_id number) IS
236 SELECT 1 FROM jai_rgm_parties a, JAI_RGM_DEFINITIONS b
237 WHERE a.regime_id = b.regime_id
238 AND b.regime_code=cp_regime_code
239 AND a.organization_type = cp_organization_type
240 AND a.organization_id = cp_organization_id
241 AND (cp_location_id is null or a.location_id=cp_location_id);
242
243 CURSOR c_rcv_rgm_dtl(cp_regime_code VARCHAR2, cp_shipment_line_id NUMBER) IS
244 SELECT nvl(process_status_flag, jai_constants.no) process_status_flag,
245 regime_item_class,
246 invoice_no
247 FROM jai_rcv_rgm_lines
248 WHERE shipment_line_id = cp_shipment_line_id
249 AND regime_code = cp_regime_code;
250
251 r_rcv_rgm_dtl c_rcv_rgm_dtl%ROWTYPE;
252 /* End of VAT Impl. */
253
254 --v_tax_vendor_site_id JAI_CMN_VENDOR_SITES.vendor_site_id%type; Commented by by nprshar for bug # 10193326 --Added by Nagaraj.s for Bug3037075
255 v_third_party_flag JAI_RCV_LINE_TAXES.third_party_flag%type; --Added by Nagaraj.s for Bug3037075
256 --Ends over here.
257 v_item_class JAI_INV_ITM_SETUPS.item_class%type; --3202319
258
259 CURSOR uom_cur(c_uom VARCHAR2) IS
260 SELECT uom_code
261 FROM mtl_units_of_measure
262 WHERE unit_of_measure = c_uom;
263 -- End of addition by Srihari on 30-NOV-99.
264
265 -- Added by GSRI on 21-OCT-01
266 CURSOR get_paddr IS SELECT paddr
267 FROM v$session vs, v$mystat vm
268 WHERE vs.sid = vm.sid
269 AND ROWNUM = 1;
270 -- End of Addition on 21-OCT-01
271
272 -- cbabu for Bug# 3028040
273 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. CURSOR c_hdr_attribute5(p_shipment_header_id IN NUMBER) IS
274 CURSOR c_hdr_dtl(p_shipment_header_id IN NUMBER) IS
275 SELECT -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. attribute5,
276 shipment_num
277 FROM rcv_shipment_headers
278 WHERE shipment_header_id = p_shipment_header_id;
279
280 --Added by Sanjikum for Bug#4533114
281 CURSOR c_hdr_attribute5_1(p_delivery_name VARCHAR2)
282 IS
283 SELECT delivery_id
284 FROM wsh_new_deliveries
285 WHERE name = p_delivery_name ;
286
287 CURSOR c_fetch_unclaim_cenvat is
288 SELECT nvl(unclaim_cenvat_flag,'N'),
289 nvl(cenvat_claimed_ptg,0),
290 nvl(non_bonded_delivery_flag,'N'), --3655330
291 nvl(cenvat_amount,0) -- Bug 4516678. Added by Lakshmi Gopalsami
292 FROM JAI_RCV_CENVAT_CLAIMS
293 WHERE shipment_line_id = p_shipment_line_id;
294
295 v_unclaim_cenvat_flag JAI_RCV_CENVAT_CLAIMS.unclaim_cenvat_flag%type;
296 v_cenvat_claimed_ptg JAI_RCV_CENVAT_CLAIMS.cenvat_claimed_ptg%type;
297 v_non_bonded_delivery_flag JAI_RCV_CENVAT_CLAIMS.non_bonded_delivery_flag%type;
298 -- bug 4516678. Added by Lakshmi Gopalsami
299 v_cenvat_amount JAI_RCV_CENVAT_CLAIMS.cenvat_amount%type;
300
301 v_express VARCHAR2(100);
302 v_shipment_num rcv_shipment_headers.shipment_num%type; -- ssumaith - bug# 3657662
303 v_order_header_id oe_order_headers_all.header_id%type; -- ssumaith - bug# 3657662
304
305 cursor c_order_cur (p_shipment_num rcv_shipment_headers.shipment_num%type) is
306 select order_header_id
307 from JAI_OM_WSH_LINES_ALL
308 where delivery_id = p_shipment_num;
309
310 /* bug 4516678. Added by Lakshmi Gopalsami */
311
312 cursor c_fetch_receive_quantity(p_shipment_header_id number,
313 p_shipment_line_id number) is
314 select qty_received
315 from JAI_RCV_LINES
316 where shipment_header_id = p_shipment_header_id
317 and shipment_line_id = p_shipment_line_id;
318
319 cursor c_fetch_transaction_quantity(p_shipment_header_id number,
320 p_shipment_line_id number ,
321 p_transaction_type varchar2) is
322 select sum(quantity)
323 from JAI_RCV_TRANSACTIONS
324 where shipment_header_id = p_shipment_header_id
325 and shipment_line_id = p_shipment_line_id
326 and transaction_type = p_transaction_type;
327
328 v_receipt_quantity number;
329 v_sum_rtv_quantity number;
330
331 -- End for bug4516678.
332
333 /* Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. */
334 cursor c_ja_rcv_interface(cp_interface_trx_id in number) is
335 select interface_transaction_id, excise_invoice_no, excise_invoice_date, online_claim_flag
336 from jai_rcv_interface_trxs
337 where interface_transaction_id = cp_interface_trx_id;
338
339 r_ja_rcv_interface c_ja_rcv_interface%ROWTYPE;
340 lv_excise_invoice_no JAI_RCV_LINES.excise_invoice_no%TYPE;
341 lv_excise_invoice_date JAI_RCV_LINES.excise_invoice_date%TYPE;
342 lv_online_claim_flag JAI_RCV_LINES.online_claim_flag%TYPE;
343
344 /* Bug 5243532. Added by Lakshmi Gopalsami
345 * Defined variable for implementing caching logic.
346 */
347 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
348 -- End for bug 5243532
349 /*Bug 4644524 start bduvarag*/
350 CURSOR c_rcv_shipment_lines(cp_shipment_line_id rcv_shipment_lines.shipment_line_id%TYPE)
351 IS
352 SELECT item_id
353 FROM rcv_shipment_lines
354 WHERE shipment_line_id = cp_shipment_line_id;
355
356 r_rcv_shipment_lines c_rcv_shipment_lines%ROWTYPE;
357 /*Bug 4644524 End bduvarag*/
358
359 -- For iSupp Porting
360 /*
361 || Start additions by ssumaith - Iprocurement.
362 */
363
364 CURSOR check_rcpt_source IS
365 SELECT apps_source_code
366 FROM po_requisition_headers_all WHERE requisition_header_id IN
367 (SELECT requisition_header_id
368 FROM po_requisition_lines_all
369 WHERE line_location_id = p_line_location_id
370 );
371
372 lv_apps_source_code VARCHAR2(30);
373
374 /*
375 || End additions by ssumaith - iprocurement
376 */
377
378
379
380 /*
381 start for ASBN -- ssumaith
382 */
383
384
385 cursor c_check_asbn is
386 SELECT '1' , shipment_num
387 from rcv_shipment_headers
388 where shipment_header_id = p_shipment_header_id
389 And asn_type = 'ASBN';
390
391
392 lv_asbn_type varchar2(10);
393 lv_shipment_num VARCHAR2(100);
394 ln_po_unit_price NUMBER;
395
396
397
398 TYPE PO_TAX_CUR IS RECORD(
399 tax_line_no JAI_PO_TAXES.TAX_LINE_NO%TYPE,
400 Tax_Id JAI_PO_TAXES.TAX_ID%TYPE,
401 Tax_rate JAI_PO_TAXES.TAX_RATE%TYPE,
402 Qty_Rate JAI_PO_TAXES.QTY_RATE%TYPE,
403 Uom JAI_PO_TAXES.UOM%TYPE,
404 Tax_Amount JAI_PO_TAXES.TAX_AMOUNT%TYPE,
405 tax_type JAI_PO_TAXES.TAX_TYPE%TYPE,
406 tax_name JAI_CMN_TAXES_ALL.TAX_NAME%TYPE,
407 modvat_flag JAI_PO_TAXES.modvat_flag%TYPE,
408 vendor_id JAI_CMN_TAXES_ALL.vendor_id%type,
409 tax_vendor_id JAI_CMN_TAXES_ALL.vendor_id%type,
410 vendor_site_id JAI_CMN_TAXES_ALL.vendor_site_id%type,
411 currency JAI_PO_TAXES.currency%type,
412 rounding_factor JAI_CMN_TAXES_ALL.rounding_factor%type,
413 duty JAI_CMN_TAXES_ALL.duty_drawback_percentage%type,
414 Precedence_1 JAI_PO_TAXES.PRECEDENCE_1%TYPE,
415 Precedence_2 JAI_PO_TAXES.PRECEDENCE_2%TYPE,
416 Precedence_3 JAI_PO_TAXES.PRECEDENCE_3%TYPE,
417 Precedence_4 JAI_PO_TAXES.PRECEDENCE_4%TYPE,
418 Precedence_5 JAI_PO_TAXES.PRECEDENCE_5%TYPE,
419 regime_code varchar2(30),
420 Precedence_6 JAI_PO_TAXES.PRECEDENCE_6%TYPE,
421 Precedence_7 JAI_PO_TAXES.PRECEDENCE_7%TYPE,
422 Precedence_8 JAI_PO_TAXES.PRECEDENCE_8%TYPE,
423 Precedence_9 JAI_PO_TAXES.PRECEDENCE_9%TYPE,
424 Precedence_10 JAI_PO_TAXES.PRECEDENCE_10%TYPE
425 );
426 TYPE tax_cur_type IS REF CURSOR RETURN PO_TAX_CUR;
427 c_po_tax_cur TAX_CUR_TYPE;
428 po_lines_rec c_po_tax_cur%rowtype;
429 --Added by walton for inclusive tax
430 ---------------------------------------
431 type tax_table_typ is
432 table of PO_TAX_CUR index by binary_integer;
433 lt_tax_table tax_table_typ;
434 -----------------------------------------
435 -- end ssumaith - asbn
436
437 -- rchandan start - 6030615(INTERORG_XFER FP )
438
439 Cursor c_rec_ship_txn(cp_ship_line_id IN NUMBER) is
440 select mmt_transaction_id
441 from rcv_shipment_lines
442 where shipment_line_id = cp_ship_line_id;
443
444 r_rec_ship_txn c_rec_ship_txn%rowtype;
445
446 cursor c_get_inv_trx_info(Cp_transaction_id IN NUMBER) IS
447 select abs(transaction_quantity) , transaction_uom , original_transaction_temp_id , prior_cost
448 from mtl_material_transactions
449 where transaction_id = cp_transaction_id ;
450
451 cursor c_jai_mtl_Trxs(cp_trx_temp_id NUMBER) IS
452 select *
453 from jai_mtl_trxs
454 where transaction_temp_id = cp_trx_temp_id;
455
456 r_jai_mtl_Trxs c_jai_mtl_Trxs%rowtype;
457
458 cursor c_jai_cmn_lines(cp_shipment_num VARCHAR2) IS
459 SELECT *
460 FROM jai_cmn_lines
461 WHERE po_line_location_id = p_line_location_id
462 AND shipment_number = cp_shipment_num;
463
464 r_jai_cmn_lines c_jai_cmn_lines%rowtype;
465
466 ln_trx_qty number;
467 lv_trx_uom varchar2(20);
468 ln_orig_id number;
469 ln_item_cost number;
470
471 /*Bug 14010660 - Start*/
472 CURSOR c_excise_inv_number(p_shipment_header_id NUMBER)
473 IS
474 SELECT
475 (
476 SELECT
477 EXCISE_INV_NUMBER
478 FROM
479 JAI_CMN_LINES
480 WHERE
481 HEADER_INTERFACE_ID = RHI.HEADER_INTERFACE_ID
482 AND rownum = 1
483 )
484 EXCISE_INV_NUM,
485 (
486 SELECT
487 EXCISE_INV_DATE
488 FROM
489 JAI_CMN_LINES
490 WHERE
491 HEADER_INTERFACE_ID = RHI.HEADER_INTERFACE_ID
492 AND rownum = 1
493 )
494 EXCISE_INV_DATE
495 FROM
496 RCV_HEADERS_INTERFACE RHI
497 WHERE
498 RHI.receipt_header_id = p_shipment_header_id;
499 CURSOR c_is_shipment_asn(p_shipment_header_id NUMBER)
500 IS
501 SELECT
502 '1'
503 FROM
504 rcv_shipment_headers RSH
505 WHERE
506 RSH.shipment_header_id = p_shipment_header_id
507 AND asn_type = 'ASN';
508 lv_asn_type VARCHAR2(1);
509 /*Bug 14010660 - End*/
510
511 -- rchandan end - 6030615(INTERORG_XFER FP )
512
513 --Add by Xiao for Adhoc taxes, reg bug#12589218 on 16-Jun-2011, begin
514 -----------------------------------------------------------------------
515 --commented by qiong.liu for bug12717416 2011.07.06 begin
516 ---------------------------------------------------------
517 /* CURSOR get_adhoc_flag(pn_tax_id NUMBER) IS
518 SELECT adhoc_flag
519 FROM jai_cmn_taxes_all
520 WHERE tax_id = pn_tax_id;
521
522 lv_adhoc_flag VARCHAR2(1);*/
523 --------------------------------------------------------
524 --commented by qiog.liu for bug12717416 2011.07.06 end
525 -----------------------------------------------------------------------
526 --Add by Xiao for Adhoc taxes, reg bug#12589218 on 16-Jun-2011, end
527
528 --------------------------- Procedure For inserting a record in JAI_RCV_LINES ---------
529 PROCEDURE insert_receipt_line IS
530
531 lv_mfg_trading JAI_RCV_LINES.mfg_trading%type ;
532 BEGIN
533
534 --Added by GSRI on 21-OCT-01
535 SELECT COUNT(*) INTO v_chk_receipt_lines
536 FROM JAI_RCV_LINES
537 WHERE shipment_line_id = p_shipment_line_id AND
538 shipment_header_id = p_shipment_header_id;
539 IF v_chk_receipt_lines = 0 THEN
540 /*DELETE FROM JAI_RCV_LINES
541 WHERE shipment_line_id = p_shipment_line_id AND
542 shipment_header_id = p_shipment_header_id;*/
543 --End Addition by GSRI on 21-OCT-01
544
545 lv_mfg_trading := NVL(v_manufacturing, 'N')|| NVL(v_trading, 'N') ; -- Removed minus sign (-) for bug#4519697
546
547 INSERT INTO JAI_RCV_LINES
548 (shipment_line_id,
549 shipment_header_id,
550 receipt_num,
551 qty_received,
552 boe_number,
553 excise_invoice_no,
554 excise_invoice_date,
555 online_claim_flag, -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
556 tax_modified_flag,
557 line_location_id,
558 tax_amount,
559 MFG_TRADING,
560 transaction_id, --Added by Nagaraj.s for Bug#2692052.
561 organization_id,--Added by Nagaraj.s for Bug#2692052.
562 inventory_item_id, -- added by Aparajita for Bug#2813244
563 creation_date,
564 created_by,
565 last_update_date,
566 last_updated_by,
567 last_update_login)
568 VALUES (p_shipment_line_id,
569 p_shipment_header_id,
570 v_receipt_num,
571 p_qty_received,
572 NULL,
573 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. v_attribute1,
574 -- v_attribute2,
575 lv_excise_invoice_no,
576 lv_excise_invoice_date,
577 lv_online_claim_flag,
578 'Y',-- v_receipt_modify_flag,added 'Y' for bug#9045278
579 p_line_location_id,
580 NULL,
581 lv_mfg_trading, --NVL(v_manufacturing, 'N')||NVL(v_trading, 'N'), /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
582 p_transaction_id, --Added by Nagaraj.s for Bug#2692052.
583 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
584 v_item_id,-- added by Aparajita for Bug#2813244
585 p_creation_date,
586 p_created_by,
587 p_last_update_date,
588 p_last_updated_by,
589 p_last_update_login);
590 END IF;
591 END insert_receipt_line;
592
593 --------------------------- Procedure For updating tax amount in JAI_RCV_LINES --------
594 PROCEDURE update_receipt_line IS
595 BEGIN
596 IF v_tax_total <> 0
597 THEN
598 UPDATE JAI_RCV_LINES
599 SET tax_amount = v_tax_total
600 WHERE shipment_line_id = p_shipment_line_id;
601 END IF;
602 END update_receipt_line;
603
604 ------------------------------ For picking po_header_date from po_headers -------------------
605 /* Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
606 PROCEDURE pick_po_header_date IS
607 BEGIN
608 FOR date_rec IN (SELECT creation_date,
609 vendor_site_id
610 FROM po_headers_all
611 WHERE po_header_id = p_po_header_id)
612 LOOP
613 v_po_header_date := date_rec.creation_date;
614 v_vendor_site_id := date_rec.vendor_site_id;
615 END LOOP;
616 jai_rcv_utils_pkg.get_div_range(p_vendor_id,
617 v_vendor_site_id,
618 v_range_no,
619 v_division_no);
620 END pick_po_header_date;
621 */
622
623 ------------------ Procedure For updating tax amount in case of internal order ---------------
624 PROCEDURE duplicate_shipment_update IS
625 BEGIN
626 IF p_transaction_type = 'RECEIVE'
627 THEN
628 FOR conf_rec IN (SELECT shipment_line_id,
629 qty_received
630 FROM JAI_RCV_LINES
631 WHERE shipment_line_id = p_shipment_line_id)
632 LOOP
633 v_conf := conf_rec.shipment_line_id;
634 IF NVL(conf_rec.qty_received, 0) <> 0
635 THEN
636 v_cor_quantity := 1 + (p_qty_received / conf_rec.qty_received);
637 END IF;
638 END LOOP;
639 IF v_conf IS NOT NULL
640 THEN
641 v_duplicate_ship := 'Y';
642 FOR lines_rec IN (SELECT rtl.tax_amount,
643 rtl.tax_type,
644 rtl.tax_line_no,
645 jtc.rounding_factor
646 FROM JAI_RCV_LINE_TAXES rtl,
647 JAI_CMN_TAXES_ALL jtc
648 WHERE rtl.shipment_line_id = p_shipment_line_id
649 AND jtc.tax_id = rtl.tax_id)
650 LOOP
651 v_current_tax := ROUND((lines_rec.tax_amount * v_cor_quantity),
652 NVL(lines_rec.rounding_factor, 0));
653 UPDATE JAI_RCV_LINE_TAXES
654 SET tax_amount = v_current_tax
655 WHERE shipment_line_id = p_shipment_line_id
656 AND tax_line_no = lines_rec.tax_line_no;
657 IF lines_rec.tax_type NOT IN ('TDS', 'Modvat Recovery')
658 THEN
659 v_tax_total := NVL(v_tax_total, 0) + NVL(v_current_tax, 0);
660 END IF;
661 END LOOP;
662 UPDATE JAI_RCV_LINES
663 SET qty_received = NVL(qty_received, 0) + p_qty_received,
664 tax_amount = v_tax_total
665 WHERE shipment_line_id = p_shipment_line_id;
666 END IF;
667 END IF;
668 END duplicate_shipment_update;
669
670 --------------------------------- rg i entry -----------------------------------------------
671 /* Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
672 PROCEDURE rg23_i_entry IS
673 BEGIN
674 Ja_In_Receipt_Rg_Pkg.rg23_i_entry (p_shipment_line_id,
675 p_location_id,
676 p_subinventory,
677 p_vendor_id,
678 p_vendor_site_id,
679 p_po_header_id,
680 p_created_by,
681 p_creation_date,
682 p_last_update_date,
683 p_last_updated_by,
684 p_last_update_login,
685 p_qty_received,
686 p_transaction_id,
687 p_transaction_type,
688 p_transaction_date,
689 p_po_line_id,
690 p_line_location_id,
691 v_attribute1,
692 v_attribute2,
693 p_uom_code,
694 p_primary_quantity,
695 v_rg_type);
696 END rg23_i_entry;
697 */
698 --------------------------------- Deciding tax modified flag --------------------------------
699 PROCEDURE set_receipt_flag IS
700
701 /* Start Vijay Shankar for Bug#4199929 */
702 lv_tax_change_on_roi_recpts JAI_CMN_INVENTORY_ORGS.tax_change_on_open_int_recpts%TYPE;
703 lv_tax_change_on_wms_recpts JAI_CMN_INVENTORY_ORGS.tax_change_on_wms_recpts%TYPE;
704 /*lv_mobile_txn_flag rcv_transactions_interface.mobile_txn%TYPE;
705 above declaration fails incase client has a lower BASE VERSION like 11.5.5
706 So modified as below so that the procedure gets compiled. 4252036(4245089)
707 */
708 lv_mobile_txn_flag VARCHAR2(2);
709 lv_open_interace_receipt_flag rcv_transactions_interface.validation_flag%TYPE;
710 lv_dynamic_sql VARCHAR2(1000);
711 lv_profile_val VARCHAR2(10);
712
713 CURSOR c_interface_trx_dtl(cp_interface_transaction_id IN NUMBER) IS
714 /*cursor query changed for bug 8486273 - on successful import, rows will be deleted
715 * from rcv_transactions_interface. We should check rcv_headers_interface table instead,
716 * where data would be retained until being purged. It should be noted that the group_id
717 * link between rcv_transactions and rcv_headers_interface is not one-one, but it is enough
718 * to establish that the receipt is imported through open interface.
719 bug 8594501 - earlier query caused mutating table error, as it hit the rcv_transactions
720 * table for the same row which triggered this procedure (not committed yet). For this,
721 * group_id is being passed as a procedure parameter from the trigger.*/
722 /* Code added from the above bugs needs to be Reverted as they are not supported in R12*/
723 --SELECT nvl(validation_flag, 'N') validation_flag commented by Vijay Shankar for bug#4240265
724 SELECT decode(header_interface_id, null, 'N', 'Y') imported_receipt_flag
725 FROM rcv_transactions_interface
726 WHERE interface_transaction_id = cp_interface_transaction_id;
727 /*End Vijay Shankar for Bug#4199929 */
728 /* Reverted the change in R12 Query added by nprashar for bug # 8566481
729 SELECT 'Y'
730 FROM dual
731 WHERE EXISTS (SELECT 1
732 FROM RCV_HEADERS_INTERFACE RHI
733 WHERE RHI.group_id = p_group_id);*/
734
735 CURSOR c_iproc_profile IS
736 SELECT fnd_profile.value('JA_ACCESS_IPROC_TAX')
737 FROM DUAL;
738
739 ln_user_id NUMBER := fnd_global.user_id;
740
741
742 BEGIN
743 jai_rcv_utils_pkg.get_rg1_location (p_location_id,
744 p_organization_id,
745 p_subinventory,
746 v_rg_location_id);
747 FOR rec_upd_rec IN (SELECT nvl(receipt_modify_flag, 'N') receipt_modify_flag,
748 nvl(trading, 'N') trading,
749 nvl(manufacturing, 'N') manufacturing
750 /* following added by Vijay Shankar for Bug#4199929 */
751 , nvl(tax_change_on_open_int_recpts, 'N') tax_change_on_open_int_recpts
752 , nvl(tax_change_on_wms_recpts, 'N') tax_change_on_wms_recpts
753 FROM JAI_CMN_INVENTORY_ORGS
754 WHERE organization_id = p_organization_id
755 AND location_id = v_rg_location_id)
756 LOOP
757 v_receipt_modify_flag := rec_upd_rec.receipt_modify_flag;
758 v_trading := rec_upd_rec.trading;
759 v_manufacturing := rec_upd_rec.manufacturing;
760 /* following added by Vijay Shankar for Bug#4199929 */
761 lv_tax_change_on_roi_recpts := rec_upd_rec.tax_change_on_open_int_recpts;
762 lv_tax_change_on_wms_recpts := rec_upd_rec.tax_change_on_wms_recpts;
763 END LOOP;
764
765 /* Start, Vijay Shankar for Bug#4199929 */
766 /* following is written to give control to clients so that for open interface receipts the value returned is 'Y' */
767 lv_dynamic_sql := 'select nvl(mobile_txn, ''N'') mobile_txn FROM rcv_transactions_interface WHERE interface_transaction_id = :1';
768 BEGIN
769 execute immediate lv_dynamic_sql into lv_mobile_txn_flag using p_interface_transaction_id;
770 EXCEPTION
771 WHEN OTHERS THEN
772 lv_mobile_txn_flag := 'N';
773 END;
774
775 OPEN c_interface_trx_dtl(p_interface_transaction_id);
776 FETCH c_interface_trx_dtl INTO lv_open_interace_receipt_flag;
777 CLOSE c_interface_trx_dtl;
778
779 OPEN c_iproc_profile;
780 FETCH c_iproc_profile INTO lv_profile_val;
781 CLOSE c_iproc_profile;
782
783 IF v_debug_flag = 'Y' THEN
784 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of lv_profile_val (profile value) is ' || lv_profile_val);
785 END IF;
786
787 lv_profile_val := fnd_profile.value_specific(NAME =>'JA_ACCESS_IPROC_TAX',user_id=>ln_user_id);
788
789 IF v_debug_flag = 'Y' THEN
790 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of lv_profile_val (for userid) is ' || lv_profile_val);
791 END IF;
792
793 IF NVL(lv_profile_val,'2') = '2' Then
794 lv_profile_val := 'N';
795 ELSE
796 lv_profile_val :='Y';
797 END IF;
798
799 IF v_debug_flag = 'Y' THEN
800 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of lv_profile_val (as Y/N) is ' || lv_profile_val);
801 END IF;
802
803 FND_FILE.put_line(fnd_file.log, 'ROI:'|| lv_tax_change_on_roi_recpts
804 ||', WMS:'||lv_tax_change_on_wms_recpts
805 ||', Receipts:'||v_receipt_modify_flag
806 ||', TxnROI Flg:'||lv_open_interace_receipt_flag
807 ||', TxnMobile Flg:'||lv_mobile_txn_flag
808 );
809
810 /* following if condition is for WMS Receipts */
811 IF lv_mobile_txn_flag = 'Y' THEN
812 IF lv_tax_change_on_wms_recpts = 'Y' THEN
813 v_receipt_modify_flag := 'Y';
814 ELSE
815 --v_receipt_modify_flag := 'X';
816 --commented the above and added the below by Ramananda for Bug#4519697
817 v_receipt_modify_flag := 'N';
818 END IF;
819
820 IF v_debug_flag = 'Y' THEN
821 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_receipt_modify_flag after WMS Receipts loop is ' || v_receipt_modify_flag);
822 END IF;
823
824 /* following if condition is for Open Interface Receipts */
825 ELSIF lv_open_interace_receipt_flag = 'Y' THEN
826 IF lv_tax_change_on_roi_recpts = 'Y' THEN
827 v_receipt_modify_flag := 'Y';
828 ELSE
829 --v_receipt_modify_flag := 'X';
830 --commented the above and added the below by Ramananda for Bug#4519697
831 v_receipt_modify_flag := 'N';
832 END IF;
833
834 IF v_debug_flag = 'Y' THEN
835 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_receipt_modify_flag after Open Interface Receipts loop is ' || v_receipt_modify_flag);
836 END IF;
837
838 ELSE
839
840 IF v_receipt_modify_flag = 'N' THEN
841 -- added, CSahoo for Bug 5344225
842 -- iSupplier Porting
843 /*
844 || Start additions by ssumaith for Iprocurement.
845 */
846 IF lv_apps_source_code = 'POR' AND p_transaction_type = 'RECEIVE'
847 AND lv_profile_val = 'N' then
848 /* Rcpt Created thru Iproc and user does not have change the taxes on rcpt
849 || In such a case setting the receipt_modify_flag to 'N'
850 */
851 v_receipt_modify_flag := 'N';
852
853 END IF;
854 /*
855 || End additions by ssumaith - Iprocurement.
856 */
857 -- iSupplier Porting
858 -- v_receipt_modify_flag := 'N';
859 /* added by csahoo for bug#6209911 */
860 IF v_trading = 'Y' THEN
861 v_receipt_modify_flag := 'Y';
862 END IF;
863 ELSE
864 /*
865 || Start additions by ssumaith for Iprocurement.
866 || user did not navigate from the receipts localised form because
867 || the rcpt was created from iproc and user has right to change the taxes
868 */
869
870 IF lv_apps_source_code = 'POR' AND p_transaction_type = 'RECEIVE'
871 THEN
872 IF lv_profile_val = 'Y' then
873 /* iSupplier Porting
874 || End additions by ssumaith - Iprocurement.
875 */
876 v_receipt_modify_flag := 'Y';
877 ELSE
878 v_receipt_modify_flag := 'N';
879 END IF;
880 END IF;
881 /* iSupplier Porting
882 || End additions by ssumaith - Iprocurement.
883 */
884
885 /* R12-PADDR
886 IF v_chk_form IS NOT NULL THEN
887 v_receipt_modify_flag := 'Y';
888 ELSE
889 v_receipt_modify_flag := 'N';
890 END IF;
891 R12-PADDR */
892 -- v_receipt_modify_flag := 'Y';
893 END IF;
894
895 END IF;
896 /* END, Vijay Shankar for Bug#4199929 */
897
898 FND_FILE.put_line(fnd_file.log, 'Final ReceiptModifyFlag:'||v_receipt_modify_flag);
899
900 /* following is commented by Vijay Shankar for Bug#4159557
901 Added by Nagaraj.s for Bug2915829.
902 IF v_receipt_modify_flag ='N' THEN
903 v_receipt_modify_flag :='X'; --To Indicate that the Flag Value is to be Processed.
904 END IF;
905 */
906
907 END set_receipt_flag;
908
909 ---------------------------------------------------------------------------
910 PROCEDURE pick_register_type (p_organization_id number,
911 p_item_id number,
912 p_register_type out nocopy varchar2) IS
913 v_register_type JAI_CMN_RG_23AC_I_TRXS.register_type % TYPE;
914 v_item_class JAI_INV_ITM_SETUPS.item_class % type;
915 BEGIN
916 For reg_rec IN (SELECT item_class
917 FROM JAI_INV_ITM_SETUPS
918 WHERE organization_id = p_organization_id
919 AND inventory_item_id = p_item_id)
920 LOOP
921 v_item_class := reg_rec.item_class;
922 END LOOP;
923 IF v_item_class IN ('RMIN', 'RMEX', 'CCIN', 'CCEX','FGIN','FGEX')
924 THEN
925 v_register_type := 'A';
926 ELSIF v_item_class IN ('CGIN', 'CGEX')
927 THEN
928 v_register_type := 'C';
929
930 END IF;
931 p_register_type := v_register_type;
932 END pick_register_type;
933 ---------------------------------------------------------------------------
934
935 /* ***************************** MAIN BEGIN ***************************** */
936
937 BEGIN
938 /*----------------------------------------------------------------------------------------------------------------------
939 FILENAME: ja_in_receipts_p.sql
940 CHANGE HISTORY:
941
942 S.No Date Author and Details
943 -----------------------------------------------------------------------------------------------------------------------
944 1 17/06/02 Changed by Nagaraj.s on 17/06/2002 for Bug#2417290...............
945 Previously the Coding was- AND sla.orig_sys_line_ref = prla.line_num)
946 Changed Coding is AND sla.orig_sys_line_ref = to_char(prla.line_num))
947 Since the LHS of the expression was a varchar2 and Right Hand side of the Expression was a Number.
948 Hence the Invalid Number exception would occur when sla.orig_sys_line_ref is not a number........
949 Hence the RHS of the expression is also converted into To_Char before the comparison is made.
950
951
952 2. 18/06/2002 Aparajita for bug 2415767
953 When currency conversion rate is changes at receipt level from the rate defined at PO,
954 all the taxes that are with precedences need to be recalculated as the tax amount changes.
955 Added the code for recalculation of receipt tax in such situation.
956
957 3. 22/07/2002 Additional documentation for bug # 2415767 by Aparajita on 22/07/2002.
958 This procedure is dependent on the modified structure of table JA_IN_TEMP_RECEIPT. Ensure that
959 SHIPMENT_HEADER_ID NUMBER (15) and
960 PROCESS_FLAG VARCHAR2 (1)
961 columns are present in the table JA_IN_TEMP_RECEIPT in the client instance.
962
963 4. 18/08/2002 In Case of an Direct Delivery, the receipt_routing column in Purchase Order
964 is stored with an Value of 3. The For Passing Accounting Entries will fire
965 and takes care of receiving and delivery entries and hence it is
966 necessary that, the to handle Deliver RTR RTV should not fire
967 when it is a direct delivery and this is handled with the condition(V_RECEIPT_ROUTING<>3)
968 and To handle Deliver RTR RTV should fire only in case
969 of standard and inspection routing(Receipt_routing is 1 or 2).
970
971 5. 21/08/2002 Changed by Sriram for Bug # 2514719 . In the procedure , changed the else condition that was the else for RMA
972 Because of the Else , if an internal sales order cycle is done , the control flows into the
973 else ,which should happen only in case of a PO cycle. Hence the else was replaced by
974 elsif with PO
975
976 6. 23/08/2002 Changed by Nagaraj.s for Bug#2525910
977 Incorporated an parameter P_ROUTING_HEADER_ID which is referred to before the
978 concurrent call for To Handle Deliver RTR RTV. Also removed the cursor get_receipt_routing
979 as this is referred in parameters and there is no need to fetch the same.
980
981
982 7 27/08/2002 Changed by Nagaraj.s for Bug2531667
983 The condition for fetching UOM has been incorporated with one more "and" condition
984 AND pla.po_line_id = p_po_line_id
985 which gets the UOM for that PO Line as previously the join was not proper and hence
986 the UOM conversion was having a value of zero which resulted in tax amounts being
987 calculated as zero.
988
989 8 25/09/2002 Changed by Nagaraj.s for Bug2588096
990 In case of an Unordered Receipt, there was a call to rg23_part_i_entry procedure
991 which in turn makes a call to ja_in_receipt_rg_pkg which had a select statement
992 from RCV_Transactions, which resulted in Mutation Error. Also, according to the URM
993 no RG Entries should happen at the point of Unordered Receipts and hence the coding
994 which was previously calling rg23_part_i_entry is commented and this has resulted in
995 Mutation Trigger being avoided. And also the same coding has been written in
996 the case where Transaction Type is Match. And also previously Tax Lines were inserted
997 for source_document_code='PO' but this can also go into an error as in case of UNORDERED
998 receipt, source_document_code will be PO, but Transaction Type will be Unordered and hence
999 this condition is also incorporated.
1000
1001 9. 21/11/2002 Changed by Nagaraj.s for Bug # 2659628 Version - 615.8
1002 Receipt Taxes are recalculated irrespective of whether
1003 there is a currency change or not so that rounding does
1004 not happen on the higher side in both PO and Receipt.
1005
1006
1007 10. 04/03/2003 By Nagaraj.s Version - 615.9
1008
1009 1. For Bug # 2692052
1010 Commented the calls to ja_in_temp_receipt
1011 Changed the calls to "TO HANDLE DELIVER RTR RTV"
1012 Changed the Insert of JAI_RCV_LINES with 2 columns ( organization_id,transaction_id).
1013
1014 2. For Bug # 2808110
1015 Added an Order by Clause in the Fetching
1016 of Tax related Information from JAI_PO_TAXES
1017 as this is very critical in the Precedence Logic Incorporated.
1018
1019 3. For Bug # 2798999 - Generic Fix for the One Off Patch.
1020 Added the call to jai_rcv_utils_pkg.get_organization_id
1021 to ensure that Organization Id and Item Id is picked up as this
1022 is critical in INV_CONVERT.INV_UM_CONVERSION to calculate the UOM
1023 Rate.
1024
1025 11. 10/03/2003 Aparajita for bug # 2813244. Version 615.11
1026
1027 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.
1028
1029 The reason was that the uom conversion was as follows,
1030 - uom from requisition and quantity from delivery
1031 - uom from receipt and quanitity from receipt.
1032
1033 The quantity in delivery is always in primary uom where as the requisition is not in primary uom. The apportionment was hence going wrong.
1034
1035 Version 615.10 is obsoleted as wrong file was checked in.
1036
1037 12. 22/03/2003 Nagaraj.s for Bug # 2915829. Version : 615.12
1038
1039 The Tax Modified Flag is set through set_receipt_flag Procedure.
1040 This is changed to populate the value as 'X' in case it is 'N'.
1041
1042 Hence Now the Tax Modified Flag will have 3 possible Values.
1043
1044 1. 'Y' - Which Indicates that Taxes can be changed at Receipt Level.
1045 This Value will Invoke For Passing Accounting Entries Program
1046
1047 2. 'N' - Which Indicates that Taxes can no longer be changed at Receipt Level.
1048 This Value will ensure that For Passing Accounting Entries will not be Invoked.
1049
1050 3. 'X' - Which Indicates that Taxes cannot be changed at Receipt Level,
1051 But still this Ensures that For Passing Accounting Entries Program is Invoked
1052 on Closing the Receipts Localized screen.
1053
1054 Hence, For Passing Accounting Entries Concurrent gets Invoked as Long as this Flag
1055 has an value of 'X' or 'Y'. And For Passing Procedure Updates this to 'N' after
1056 Processing. This Prevents Invoking of For Passing Accounting Entries Concurrent
1057 Multiple Times.
1058
1059 13. 02/05/2003 Aparajita for bug # 2929171. Version#615.13
1060 Taxes were not getting calculated properly when,
1061 - uom is changed at receipt
1062 - taxes have non zero precedences, that is tax on tax.
1063
1064 The problem was because of uom conversion being applied to non zero precedence taxes.
1065 The tax lines are always calculated in the order of tax line number. When uom is changed,
1066 only the tax precedence 0 undergoes the change. This conversion should not be applied to
1067 precedence 1 as, when tax line 1 was calculated, that is already taken care of.
1068
1069 14. 08/05/2003 Nagaraj.s for Bug#2915783. Version#615.14
1070 The Initialization of v_item_modvat_flag and v_item_trading_flag was not done at the
1071 proper place as a result of which Modvat_flag of JAI_RCV_LINE_TAXES
1072 was not populated properly. This has been moved to a location which is proper,
1073 so that Proper comparison happens.
1074
1075 15. 13/05/2003 Vijay Shankar for bug # 2943558. Version#615.15
1076 When a Receipt is made against a SCHEDULED Release (partial quantity) of
1077 PLANNED PO, then taxes are calculated for whole of the PLANNED PO line quantity
1078 instead of SCHEDULED Release quantity.
1079 The issue is occuring because, precedence_0 is calculated as unit_price * quantity
1080 of PO_LINES_ALL instead of PO_LINE_LOCATIONS_ALL. Code is modified to calculate
1081 precedence_0 as PRICE_OVERRIDE * QUANTITY of PO_LINE_LOCATIONS_ALL instead of using
1082 PO_LINES_ALL
1083
1084 16. 06/06/2003 Nagaraj.s for Bug #2991872. Version : 616.1
1085 Code is added to ensure that if Assessable price is defined for Excise Taxes, then
1086 the Assessable Price is picked up instead of the unit price from po_line_locations_all
1087 table.
1088
1089 17. 21/07/2003 Vijay Shankar for Bug# 3028040, Version : 616.2
1090 Code is added to check for EXPRESS transaction and then stop 'For Passing' to fire for every RECEIVE transaction
1091 of EXPRESS Receipt. This change is made as the PADDR concept is removed in RCV_SHIPMENT_HEADERS DFF.
1092
1093 18. 29/07/2003 Nagaraj.s for Bug #2993865 . Version#:616.3
1094 The two queries which were written earlier to fetch line_id,transaction_curr_code,
1095 delivery_detail_id have been merged to form one query.
1096 The join is now changed to
1097 order. source document id = requisition.requisition header id
1098 order line.source document line id = requisition line. requisition line id.
1099
1100 19. 30/07/2003 Nagaraj.s for Bug#3037075. Version#:616.4
1101 The Vendor site id is now fetched in the lines_rec cursor and
1102 the same is populated into JAI_RCV_LINE_TAXES table.
1103 This change is applicable for only PO type of Transaction. Huge Dependency
1104
1105 20. 22/08/2003 Nagaraj.s for Bug#3057752. Version#:616.5
1106 The changes are as below:
1107 An Raise Application Error is written to ensure that RTR Transactions should not happen
1108 if neither claim nor unclaim is done.
1109
1110 21. 31/10/2003 Nagaraj.s for Bug # 3123778, File Version : 616.6
1111 The check for CGIN,CGEX items for 100% claim is removed as per
1112 the Functional Requirement.
1113
1114 22. 03/11/2003 Nagaraj.s for Bug # 3202319 File Version : 616.7
1115 In case of FGIN,FGEX Item Classes, the Claim percentage should not hold good.
1116 Hence the cursor mod_rec is added with Item class FGIN,FGEX and the same
1117 is incorporated in the IF Condition, which validates for 100% cenvat claim.
1118
1119 23. 05/11/2003 Nagaraj.s for Bug3237536 File Version : 616.8 (IN60105D2)
1120 Added the condition v_tax_vendor_site_id := null as this was leading to Vendor
1121 site id being populated in all cases irrespective of the type of Tax.
1122
1123 24. 02/06/2004 ssumaith - bug# 3657662 - File Version 115.1
1124
1125 There was a performance problem with a query . The issue was it was taking time because
1126 of a full table scan on the oe_order_headers_all table.
1127
1128 Issue was resolved by retreiving the order_header_id from the JAI_OM_WSH_LINES_ALL
1129 based on delivery id and adding a where clause to qualify the order_header_id.
1130
1131
1132 25. 04/06/2004 Nagaraj.s - Bug # 3655330 - File Version : 115.1
1133 In case of RTR and RTV scenarios, check has been made for either
1134 claim or unclaim but in this case neither claim or unclaim happens and hence,
1135 added one more condition :v_non_bonded_delivery_flag = 'N'
1136 so that RTR and RTV will be done without any problems.
1137
1138 26. 18/06/2004 ssumaith - bug# 3683666 - File Version 115.2
1139
1140 When a RMA receipt is done without navigating from Receipts-localised form and not entering
1141 the values in the DFF of RCV_SHIPMENT_HEADERS or RCV_TRANSACTIONS, it was observed that
1142 taxes are not defaulted into localization tables (JAI_RCV_LINE_TAXES).
1143 The reason this was happening is because the if condition which was checking this to be a
1144 RMA receipt was also checking the chk_form to be not null with an "AND" to other required
1145 conditions. Hence the problem
1146
1147 This issue has been fixed by doing making the v_chk_form check with 'OR' rather than 'AND'
1148
1149 Dependecny due to this bug - None
1150
1151 27. 22/07/2004 ssumaith - bug# 3772135 file version 115.3
1152
1153 When two internal internal orders created out of two internal requisitions are
1154 merged into a single delivery ,and a receipt created for the delivery, it was
1155 causing the taxes only for the first line to be populated.
1156
1157 28. 28/08/2004 Nagaraj.s for Bug#3858917 File Version : 115.4
1158 In valid number error was occuring as the shipment number was entered as
1159 alpha numeric and the condition was entering into the ISO route the comparison
1160 for the shipment num with the delivery id in the cursor c_order_cur
1161 was going into this error.
1162
1163 Ideally the condition :
1164 ========================================================
1165 v_receipt_source_code = 'INTERNAL ORDER' OR -- AND
1166 v_chk_form IS NOT NULL AND
1167 ========================================================
1168 is wrong as in case of PO and RMA also this enters the code
1169 and as the shipment num can be entered as any value the comparison is wrong.
1170 In any case, this code should get executed for Internal Order
1171 and hence the condition of v_chk_form is not needed.
1172 Hence this is commented. This will be fixed as part of the generic
1173 fix for the bug3848133 as this object is being changed.
1174
1175 29. 24/08/2004 Nagaraj.s - Bug# 3848133 (BaseBug# 3496408). File version : 115.4
1176
1177 The code now fetches the Precedences from the following points for the following sources:
1178 ----------------------------------------------------------------------------------------
1179 source type Source Table
1180 RMA JAI_OM_OE_RMA_TAXES
1181 PO JAI_PO_TAXES
1182 ISO JAI_OM_WSH_LINE_TAXES
1183 ----------------------------------------------------------------------------------------
1184 In these 3 cases a insert into JAI_RCV_LINE_TAXES is present and this is,
1185 changed to incorporate precedences as well as transaction id.
1186
1187 30. 05/11/2004 Vijay Shankar - Bugs#3949408. File version : 115.5
1188 Commented the redundant code for MATCH processing
1189
1190 31. 14/10/2004 ssumaith - Bug# 3878439 File Version - 115.6
1191
1192 When a delivery which consists of multiple sales orders is split into multiple delivery
1193 details at the shipment level only and not at sales order level, for each delivery detail
1194 a shipment line is being created at the receipt level.
1195
1196 As there is no link available between the receipt line and delivery detail at the delivery
1197 detail level, we are unable to exactly apportion the taxes.
1198
1199 devised approach to get the delivery detail based on the shipment line id also ini addition
1200 to the other clauses.
1201
1202 This change has been done in the cursor which fetches the delivery details based on the order and
1203 receipt details.
1204
1205 32 03/01/2005 Vijay Shankar for Bug# 3940588, Version:115.7
1206 Modified the code to execute only for RECEIVE, UNORDERED, MATCH and RETURN TO VENDOR transactions.
1207 Functionality for all the other transactions will be handled through ja_in_receipt_transaction_pkg, which is
1208 coded for Receipts Corrections functionality and from now on will be used for all Localization Receipts
1209 functionality as a gateway.
1210 - added a new parameter p_chk_form (OUT Variable) to return back a value, if the receipt transaction that is
1211 begin processed is created after navigating through Localization form
1212 - Instead of submitting the concurrents JAINRVCTP is submitted for processing
1213 - JAI_RCV_LINES.tax_modified_flag will be set now from above mentioned package when submitted from JAINPORE
1214 - Added Validation at the end of Procedure to error out if this is an RTV and Cenvat is not yet Claimed
1215
1216 ** Please refer to Old Version of the Object incase commented code needs to be looked at **
1217
1218 33 09/02/2005 Vijay Shankar for Bug# 4159557, Version:115.8
1219 Modified the code to assign proper value to JAI_RCV_LINES.tax_modified_flag column based on Receipts
1220 tax modification Value returned by Localization Hook given to customers. the hook is called in ja_in_receipt_tax_insert_trg
1221 trigger and returned value is passed as parameter to this procedure which is used for tax_modified_flag value
1222 determination
1223
1224 * This is a dependancy for Future Versions of the procedure *
1225
1226 34 22/02/2005 Vijay Shankar for Bug# 4199929, Version:115.9
1227 Changes made in the previous version are modified to use new setup at Organization Addl Information instead of
1228 value returned by Hook
1229 Changes are made in internal procedure set_receipt_flag
1230
1231 * This is a dependancy for Future Versions of the procedure *
1232
1233 35 12/03/2005 Bug 4210102. resolved by LGOPALSA Version: 115.10
1234 (1) Added CVD, Excise and customs education cess
1235
1236 36 19/03/2005 Vijay Shankar for Bug#4250236(4245089). FileVersion: 115.12
1237 .Implemented VAT Tax Calculation based on VAT Assessable value by making a call to jai_general_pkg.ja_in_vat_assessable_value
1238 .JAI_RCV_LINE_TAXES.modvat_flag is set to proper value for VAT Taxes based on RECOVERABLE item attribute
1239 of item. If item setup is not done, then Default value is taken as "Y" (meaning, tax is recoverable)
1240 .modified the main SQL's to fetch taxes from various sources to use jai_regime_tax_types_v to fetch regime_code
1241 against each tax so that the information can be used for MODVAT_FLAG setting of tax
1242 .RTV will raise an exception incase recoverable VAT exists in the receipt line and it is neither Claimed or
1243 Unclaimed
1244
1245 37 07/04/2005 Harshita for Bug #4285064 Version : 116.0(115.13)
1246
1247 When a user creates a new receipt against a purchase order, he needs to enter the following information
1248 through a DFF : invoice no, invoice_date, Claim Cenvat On Receipt etc.
1249 This DFF is provided at two places, header and line.
1250 Information from the header DFF is captured into the rcv_shipment_headers table.
1251 Information from the lines DFF is captured into the rcv_transactions table.
1252 This information is retrieved into our base tables JAI_RCV_TRANSACTIONS and JAI_RCV_LINES.
1253 At this time, a facility has been provided for the user to default the information
1254 given at the header level DFF to all the lines only if these columns are null at the
1255 line level. Else the information in the line level DFF is sustained.
1256 For this NVL conditions have been added where this information gets defaulted.
1257
1258 38. 10/05/2005 Vijay Shankar for Bug#4346453. Version: 116.1
1259 Code is modified due to the Impact of Receiving Transactions DFF Elimination
1260
1261 * High Dependancy for future Versions of this object *
1262
1263 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
1264 as required for CASE COMPLAINCE.
1265
1266 40 13-Jun-2005 Ramananda for bug#4428980. File Version: 116.3
1267 Removal of SQL LITERALs is done
1268
1269 41 27-Jul-2005 Bug 4516678. Added by Lakshmi Gopalsami, File Version 120.2
1270 Issue :
1271 a.Whenever a user creates a receipt for a CGIN or
1272 CGEX item, 50% cenvat is claimed. If he/she intends to
1273 return the entire quantity in the receipt, he/she must
1274 claim the remaining 50% cenvat first and then do the
1275 RTV. Else, the system should throw an error.
1276
1277 b.After creating a receipt for a CGIN or CGEX item,
1278 if the user does a partial RTV on that receipt,
1279 the system should allow it although the remaining
1280 50% CENVAT has not been claimed.
1281
1282 Fix :
1283 a. Added code to check this in Package jai_rcv_tax_pkg
1284 (1) Created new procedure pick_register_type to get the
1285 register_type depending on the item_class
1286 (2)Created two new cursors c_fetch_receive_quantity
1287 and c_fetch_transaction_Quantity to get the
1288 quantity received for the receipt and RTV transactions
1289 (3) Added nvl(cenvat_amount,0) in
1290 cursor c_fetch_unclaim_cenvat
1291 b. The cenvat receivable accounts were not getting passed
1292 in case of a CGIN/CGEX item.
1293 Fixed this by passing these values.
1294 Commented the generic assignment for cenvat
1295 accounting entries and added the condition for
1296 CGIN and CGEX item class in procedure
1297 accounting_entries.
1298
1299 42 01-Aug-2005 Ramananda for bug#4519697, File Version 120.3
1300 Changed the value being assigned to variable - v_receipt_modify_flag from X to N at 2 places
1301 As a part of this bug, the minus sign which got introducted during 'Removal of SQL Literals' is removed
1302
1303 Dependency due to this Bug
1304 --------------------------
1305 jai_rcv_trx_prc.plb (120.3)
1306 jai_rcv_rt_t1.sql (120.2)
1307
1308 43 01-Aug-2005 Ramananda for bug#4530112. File Version 120.4
1309 Problem
1310 -------
1311 In case of RTV, if VAT Claim is not done, system is giving error
1312
1313 Fix
1314 ---
1315 1) Commented the Condition -
1316 "IF lv_vat_recoverable_for_item = jai_constants.yes
1317 AND NVL(ln_chk_vat, 0) <> 0
1318 AND r_rcv_rgm_dtl.process_status_flag <> 'U' --Not Unclaimed
1319 AND r_rcv_rgm_dtl.invoice_no IS NULL"
1320
1321 Dependency Due to this Bug -
1322 File jai_rcv_rgm_clm.plb (120.2) is changed as part of this Bug,
1323 so this object is dependent on current Bug and object jai_rcv_rgm_clm.plb (120.2)
1324
1325 44 05-Aug-2005 Ramananda for Bug#4533114, File Version 120.5
1326
1327 1) Added a new cursor - c_hdr_attribute5_1
1328 2) Added a new begin end part and fetched the values from cursor - c_hdr_attribute5_1
1329 3) In the cursor - line_rec, changed the condition -
1330 "and spl.delivery_id = rsh.shipment_num"
1331 to
1332 "and spl.delivery_id = decode(ltrim(translate(shipment_num,'0123456789','~'),'~'),NULL,
1333 rsh.shipment_num,(select delivery_id from wsh_new_deliveries where name=rsh.shipment_num))"
1334
1335 44 19-Aug-2005 Ramananda for Bug#4562844, File Version 120.6
1336 Problem
1337 -------
1338 System is creating receiving accounting entry and generating tax invoice against disable taxes
1339
1340 Fix
1341 ---
1342 1) In the cursor for selecting the taxes from JAI_PO_TAXES,
1343 changed the columns tax_rate, qty_rate and tax_amount
1344
1345 Dependency due to this Bug-
1346 None
1347
1348 45. 24-Aug-2005 Bug4568090. Added by Lakshmi Gopalsami Version 120.7
1349 Added check for trading items to set the modvat_flag
1350 For trading items the modvat flag should be set to 'Y'
1351 on tax lines if the item_trading_flag at item level is 'Y'
1352 and modvat percentage is specified in the taxes
1353
1354 46. 02/11/2006 For Bug 5228046, File version 120.9
1355 Forward porting the change in 11i bug 5365523 (Additional CVD Enhancement).
1356 This bug has datamodel and spec changes.
1357
1358 47. 21/02/2007 CSahoo for Bug#5344225, File Version 120.10
1359 Forward Porting of 11i Bug#5343848.
1360 Issue :
1361 India - Receiving Transaction Processor Concurrent Program was called
1362 for each transaction on a shipment line.
1363 Fix :
1364 In the procedure jai_rcv_tax_pkg,
1365 Added code to set the Tax Modified Flag based on whether the Call was
1366 made from India - Receipts Localized Form or not.
1367 48. 13/04/2007 bduvarag for the Bug#5989740, file version 120.11
1368 Forward porting the changes done in 11i bug#5907436
1369 49. 13/04/2007 bduvarag for the Bug#4644524, file version 120.11
1370 Forward porting the changes done in 11i bug#4593273
1371
1372 50. 27/06/2007 CSahoo for bug#6154234, File Version 120.12
1373 commmented the line v_receipt_modify_flag = 'N'
1374
1375
1376 Dependency Due to this Bug : Yes.
1377 51. 16-07-2007 iSuppleir forward porting
1378 Changed shipment_num to shipment_number
1379 excise_inv_num to excise_inv_number in jai_cmn_lines table
1380
1381 52. 01-08-2007 rchandan for bug#6030615 , Version 120.17
1382 Issue : Inter org Forward porting
1383
1384 53. 09/10/2007 CSahoo for bug#6209911, File Version 120.20
1385 Added a IF block in the set_receipt_flag procedure.
1386
1387 54. 15/10/2007 bgowrava for Bug#6459894, File Version 120.22
1388 Uncommented statements which were wrongly commented.
1389
1390 55. 16/10/2007 rchandan for bug#6504410, File Version 120.23
1391 Issue : R12RUP04.I/ORG.QA.ST1:RTP GOING INTO ERROR FOR MFG-MFG INTRANSIT RCPT WITH VAT
1392 Fix : Few utl_file debugs were added in previous version without checking for the v_debug_flag
1393 Added the check now.
1394
1395 56. 16/10/2007 bgowrava for Bug#6459894, File Version 120.24
1396 removed the lv_object_name from the JAI_PROCESS_MESSAGE of JAI_EXCEPTION
1397 to avoid truncation of the error message text.
1398 57. 01/01/2007 Walton for Inclusive Tax Computation
1399
1400 58. 05/03/2008 Kevin Cheng for bug 6853787
1401 Add a condition to prevent receipt type like RMA from trapping into the tax calculation loop.
1402
1403 59. 08/04/2008 JMEENA for Bug#6917520, File Version 120.27
1404 Assigned the error message to variable 'errormsg' and printed the message in exception section.
1405
1406 60. 19/08/2009 JMEENA for bug#8302581
1407 Changed the v_tax_modvat_flag from No to Yes for RMA order where VAT recoverable flag is No for the Item
1408 As VAT entries need to reverse for the RMA order.
1409
1410 61. 16/09/2009 Jia for bug#8904043
1411 Issue: If Error definition's price list is assigned to Supplier, Advanced Pricing Error message is
1412 not shown in log during receipt creation process .
1413 Fix: Added Item-UOM validation logic before get AV, and catched validation error message handle.
1414 62. 16-10-2009 vkaranam for bug#8880760
1415 Issue:
1416 TST1212.XB1.QA VAT TAX IS NOT RIGHT WHEN RECEIVE PART OF INTER-ORG TRANSACTION
1417 Fix:
1418 Chnages are done in default_taxes_onto_line procedure.
1419 Interorg vat assessable value has been proportioned with the receipt qty.
1420
1421 62. 23/09/2009 Jia for bug#8932471
1422 Issue: If Error definition's price list is assigned to Supplier null site level, Advanced Pricing
1423 Error message is not shown in log during receipt creation process .
1424 Fix: Added Item-UOM validation logic for Supplier null site level before get AV, and catched
1425 validation error message handle.
1426
1427 63. 29/09/2009 CSahoo for bug#8920186, File Version 120.27.12010000.8
1428 Issue: TST1212.XB1.QA DEFAULT TAX IS NOT RIGHT WHEN UPDATE UOM DURING RECEIPT PROCESS
1429 FIX: Added a IF condition to calculate the assessable value correctly if the receipt UOM and
1430 PO UOM are different.
1431
1432 64. 25/02/2010 vkaranam for bug#9045278
1433 Issue:
1434 Not able to account the ISO receipt.
1435 Fix:
1436 Jai_rcv_lines.tax_modified_flag has been populated as 'Y' .
1437
1438 65. 15/sep/2010 vkaranam for bug#10086567
1439 Issue:
1440 FOR RMA RECEIPT TAX AMOUNTS ARE COMING AS ZERO
1441 Reason:
1442 for rma orders UOM will be the UOM_CODE
1443 for receipt UOM will be the UOM_description.
1444
1445 for getting the UOM_rate we need to pass the UOM_codes.
1446 for rma the UOM_code has been again converted to another UOM_code ,due to which conversion
1447 rate has been arrived as 0 and further based on the UOM rate the taxes has been arrived as 0.
1448
1449 Fix:
1450 for RMA receipts UOM code retrieved in the RMA order has been passed directly to the UOM conversion
1451 function.
1452 UOM shall be converted to UOM code only if the source document code is not equal to 'RMA'.
1453
1454 Modified code is
1455 --start additions for bug#10086567,added the if condition
1456 if p_source_document_code='RMA'
1457 then
1458 v_po_uom_code := v_po_uom;
1459 else
1460
1461 OPEN uom_cur(v_po_uom);
1462 FETCH uom_cur INTO v_po_uom_code;
1463 CLOSE uom_cur;
1464
1465 end if;
1466 --end bug#10086567
1467
1468 66. 19/Sep/2010 Xiao Lv for GST bug#10043656,
1469 Issue: For GST Enhancement.
1470 Fix: Add and calculate tax in GST Regime, based on GST Accssable Value.
1471 67 12/Nov/2010 Shujuan Yan for bug 10100899
1472 Issue: Tax amounts are not getting calculated correctly on receipt-localized form
1473 in foreign currency
1474 Fix: Add the logic for calculating foreign amount
1475
1476 68. 16/Jun/2011 Xiao for Adhoc taxes, reg bug#12589218 on 16-Jun-2011
1477 Issue: Adhoc tax amount will set to 0 when doing the receiving transaction.
1478 Fixed: Adhoc tax amount need to be apportioned in case of Partial receipt from the PO.
1479 69. 07/Jul/2011 Qiong fix bug12717416 Taxes modified on RECEIPT
1480 70. 03-feb-2012 vkaranam for bug#12800001
1481 Issue :RG23D register is not getting updated for ISO receipt
1482 Cause :
1483 jai_rcv_line_taxes.modvat_flag is populated as 'N' even if the tax has the cr percentage >0.
1484 fix:
1485 for trading organization as per the userguide/PM
1486 the below setup has to be there for the item:
1487 item excisable : "Y"
1488 item tradable : "Y"
1489 item modvatable : "N".
1490
1491 item_trading_flag has been added for setting the jai_rcv_line_taxes.modvat_flag
1492
1493 70. 13-Aug-2012 Bug 14010660
1494 Description: Excise Invoice Number and Date provided in ASN is not reflected in Receipts
1495 Localized form.
1496 Fix: Fetched Excise Invoice Number and Date from ASN and populated the same to Localized Receipt Tables
1497
1498
1499 71. 13-Sep-2012 by nkodakan for the bug 14590356
1500
1501 Description: AT RECEIPT LEVEL INDIA LOCAL TAX AMOUNT IS CALCULATING WRONG AMOUNT FOR FOREIGN
1502 Fix: Commented the line which is multiplying assessable value with currency conversion rate if the
1503 assessable amount is defined. And multiplying with p_currency_conversion_rate if the assessable
1504 amount is not defined.
1505 Added variable 'v_assessable_val_defined' and 'ln_vat_assess_val_defined' to hold the assessable value.
1506
1507 Create a PO with foreign currency..Attach taxes.. Then the taxes are calculating below case.
1508 Case 1: if the assessable value is define
1509 For e.g if assessable value is 350, Quantity=100 and price=100 then Calculation is happening as
1510 (Quantity * Assessable INR price/currency rate)*Excise 16% i.e (100*350/50) * 16% = 112 (currency USD)
1511
1512 Case 2: if the assessable valued is not defined
1513 If the assessable value is not defined then
1514 the calculation happening is (Quantity * Price)*Excise 16%
1515 i.e (100*100)*16%=1600.
1516
1517 72. 03-Oct-2012 amandali for bug 14696186
1518 Description:For receipts having non Adhoc UOm based taxes, the tax amount is calculated as zero
1519 Fix:Added a condition for handling non adhoc UOM based taxes along with code for Adhoc UOM based taxes
1520
1521 ===============================================================================================================
1522 Bug Number Dependency
1523 3037075 JAI_RCV_LINE_TAXES has been altered.
1524
1525 3057752 Tables : JAI_RCV_CENVAT_CLAIMS, ja_in_temp_mod_params, ja_in_batch_claim_modvat have been
1526 altered.
1527
1528 3123778 JAI_RCV_CENVAT_CLAIMS has been altered to add the column partial_cenvat_claim
1529
1530 4210102 IN60106 + 4239736 + 4245089
1531 ==============================================================================================================
1532
1533
1534 Dependencies For Future Bugs
1535 -------------------------------------
1536 IN60104d + 3037075
1537 IN60104d + 3037075 + 3057752
1538 IN60104d + 3037075 + 3057752 + 3123778
1539
1540 IN60105D2 + 3655330 + 3848133
1541
1542 IN60106 + 3940588 + 4239736 + 4245089 + 4346453
1543
1544 ----------------------------------------------------------------------------------------------------------------------*/
1545
1546 --Added by Nagaraj.s for Bug#2499017
1547 /*OPEN get_receipt_routing;
1548 FETCH get_receipt_routing into v_receipt_routing;
1549 CLOSE get_receipt_routing;*/
1550 --Ends here........
1551
1552 --File.Sql.35 Cbabu
1553 -- v_form_id := 'JAINPORE';
1554 v_tax_total := 0;
1555 v_duplicate_ship := jai_constants.no;
1556 v_chk_receipt_lines :=0;
1557 v_chk_receipt_tax_lines :=0;
1558 v_precedence_0 :=0;
1559 v_precedence_non_0 :=0;
1560 v_tax_base :=0;
1561 v_debug_flag := jai_constants.no;
1562 v_price_override :=0;
1563 v_po_quantity :=0;
1564 v_assessable_value :=0;
1565 ln_vat_assess_value :=0;
1566 --ln_gst_assess_value :=0; --Add by Xiao for GST refer to bug#10043656 on 14-Sep-2010.
1567
1568
1569 BEGIN
1570 SELECT DECODE(SUBSTR (value,1,INSTR(value,',') -1),NULL,
1571 Value,SUBSTR (value,1,INSTR(value,',') -1))
1572 INTO v_utl_location
1573 FROM v$parameter
1574 WHERE name = 'utl_file_dir';
1575 EXCEPTION
1576 WHEN OTHERS THEN
1577 v_debug_flag := 'N';
1578 END;
1579
1580
1581 IF v_debug_flag = 'Y' THEN
1582 v_myfilehandle := UTL_FILE.FOPEN(v_utl_location,'ja_in_receipts_p3.log','A');
1583 UTL_FILE.PUT_LINE(v_myfilehandle,'************************Start************************************');
1584 UTL_FILE.PUT_LINE(v_myfilehandle,'The Time Stamp this Entry is Created is ' ||TO_CHAR(SYSDATE,'DD/MM/RRRR HH24:MI:SS'));
1585 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of p_transaction_type is ' || p_transaction_type);
1586 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of p_transaction_id is ' || p_transaction_id);
1587 END IF;
1588
1589 IF p_currency_code IS NULL THEN
1590 /* Bug 5243532. Added by Lakshmi Gopalsami
1591 * Removed cursor org_rec and implemented caching logic.
1592 */
1593 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
1594 (p_org_id => p_organization_id );
1595 v_currency_code := l_func_curr_det.currency_code;
1596
1597 ELSIF p_transaction_type = 'MATCH' THEN
1598
1599 FOR po_rec IN (SELECT currency_code,
1600 rate
1601 FROM po_headers_all
1602 WHERE po_header_id = p_po_header_id)
1603 LOOP
1604 v_currency_code := po_rec.currency_code;
1605 v_currency_conversion_rate := po_rec.rate;
1606 END LOOP;
1607
1608 ELSE
1609
1610 v_currency_code := p_currency_code;
1611
1612 END IF;
1613
1614 IF v_debug_flag = 'Y' THEN
1615 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_currency_code is ' || v_currency_code);
1616 END IF;
1617
1618 -- iSupplier porting
1619 If p_line_location_id IS NOT NULL then
1620 OPEN check_rcpt_source;
1621 FETCH check_rcpt_source INTO lv_apps_source_code;
1622 CLOSE check_rcpt_source;
1623 END IF;
1624 -- iSupplier porting
1625
1626 jai_rcv_utils_pkg.get_func_curr(p_organization_id,
1627 v_func_currency,
1628 v_gl_set_of_books_id);
1629 IF v_debug_flag = 'Y' THEN
1630 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_func_currency is ' || v_func_currency);
1631 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_gl_set_of_books_id is ' || v_gl_set_of_books_id);
1632 END IF;
1633
1634 IF v_func_currency <> v_currency_code AND p_transaction_type <> 'MATCH' THEN
1635 v_currency_conversion_rate := p_currency_conversion_rate;
1636 END IF;
1637
1638 FOR row_rec IN (SELECT ROWID,
1639 organization_id
1640 FROM rcv_shipment_headers
1641 WHERE shipment_header_id = p_shipment_header_id)
1642 LOOP
1643 -- v_rowid := row_rec.ROWID;
1644 v_rsh_organization_id := row_rec.organization_id;
1645 END LOOP;
1646
1647 -- added, Harshita for bug #4285064
1648 /* Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
1649 v_attribute1 := p_attribute1;
1650 v_attribute2 := p_attribute2;
1651 v_attribute3 := p_attribute3;
1652 */
1653 -- ended, Harshita for bug #4285064
1654
1655 /* Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. */
1656 open c_ja_rcv_interface(p_interface_transaction_id);
1657 fetch c_ja_rcv_interface into r_ja_rcv_interface;
1658 close c_ja_rcv_interface;
1659 if r_ja_rcv_interface.interface_transaction_id is not null then
1660 lv_excise_invoice_no := r_ja_rcv_interface.excise_invoice_no;
1661 lv_excise_invoice_date := r_ja_rcv_interface.excise_invoice_date;
1662 lv_online_claim_flag := r_ja_rcv_interface.online_claim_flag;
1663 end if;
1664 /*Bug 14010660 - Start*/
1665 lv_asn_type := NULL;
1666 OPEN c_is_shipment_asn(p_shipment_header_id);
1667 FETCH c_is_shipment_asn INTO lv_asn_type;
1668 CLOSE c_is_shipment_asn;
1669 IF lv_asn_type = '1' THEN
1670 OPEN c_excise_inv_number(p_shipment_header_id);
1671 FETCH c_excise_inv_number INTO lv_excise_invoice_no, lv_excise_invoice_date;
1672 CLOSE c_excise_inv_number;
1673 END IF;
1674 /*Bug 14010660 - End*/
1675 /* R12-PADDR
1676 IF p_transaction_type IN ('RECEIVE', 'DELIVER', 'UNORDERED') THEN
1677
1678
1679 -- Vijay Shankar for Bug# 3028040
1680 -- IF loop added by vijay shankar to add EXPRESS receipt functionality by Removing PADDR
1681 if p_attribute15 = 'EXPRESS' then
1682 v_paddr := NULL;
1683 else
1684 v_paddr := HEXTORAW(p_attribute15);
1685 end if;
1686
1687 IF v_debug_flag = 'Y' THEN
1688 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_paddr is ' || v_paddr);
1689 END IF;
1690
1691 END IF;
1692
1693 IF v_rowid IS NOT NULL AND v_paddr IS NOT NULL THEN
1694 FOR loc_rec IN (SELECT form_id_drop
1695 FROM JAI_CMN_LOCATORS_T
1696 WHERE form_id_drop = v_form_id
1697 AND paddr = v_paddr)
1698 LOOP
1699 v_chk_form := loc_rec.form_id_drop;
1700 END LOOP;
1701
1702 IF v_debug_flag = 'Y' THEN
1703 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_chk_form is ' || v_chk_form);
1704 END IF;
1705
1706 END IF;
1707
1708 IF v_chk_form IS NOT NULL THEN
1709 UPDATE JAI_CMN_LOCATORS_T
1710 SET row_id = v_rowid
1711 WHERE FORM_NAME = v_form_id
1712 AND paddr = v_paddr;
1713 END IF;
1714
1715
1716 IF v_debug_flag = 'Y' THEN
1717 UTL_FILE.PUT_LINE(v_myfilehandle,'VIJAY2 v_express->' || v_express||', v_chk_form->'||v_chk_form);
1718 END IF;
1719
1720 -- following if changed by Vijay Shankar for Bug# 3028040
1721 IF (v_chk_form IS NULL AND nvl(v_express, 'NONEXPRESS') = 'EXPRESS') THEN
1722 v_chk_form := 'JAINPORE';
1723 END IF;
1724 R12-PADDR */
1725
1726 -- Vijay Shankar for Bug# 3028040
1727 OPEN c_hdr_dtl(p_shipment_header_id);
1728 FETCH c_hdr_dtl INTO -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. v_express,
1729 v_shipment_num ; -- ssumaith - bug# 3657662 v_shipment_num added
1730 CLOSE c_hdr_dtl;
1731
1732 --Start Added by Ramananda for Bug#4533114
1733 BEGIN
1734 --checking whether the v_shipment_num is number
1735 ln_test_delivery_id := NULL;
1736 ln_test_delivery_id := v_shipment_num;
1737 EXCEPTION
1738 WHEN VALUE_ERROR THEN
1739 OPEN c_hdr_attribute5_1(v_shipment_num);
1740 FETCH c_hdr_attribute5_1 INTO v_shipment_num;
1741 CLOSE c_hdr_attribute5_1;
1742 END;
1743 --End Added by Ramananda for Bug#4533114
1744
1745 -------------------------------- To retrieve receipt number ---------------------------------
1746 FOR header_rec IN (SELECT receipt_num
1747 FROM rcv_shipment_headers
1748 WHERE shipment_header_id = p_shipment_header_id)
1749 LOOP
1750 v_receipt_num := header_rec.receipt_num;
1751 END LOOP;
1752 ----------------------------- to retrieve receipt_source_code -------------------------------
1753 IF v_debug_flag = 'Y' THEN
1754 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_receipt_num is ' || v_receipt_num);
1755 END IF;
1756
1757 IF p_transaction_type = 'RECEIVE' THEN
1758 FOR head_rec IN (SELECT receipt_source_code
1759 FROM rcv_shipment_headers
1760 WHERE shipment_header_id = p_shipment_header_id)
1761 LOOP
1762 v_receipt_source_code := head_rec.receipt_source_code;
1763 END LOOP;
1764 END IF;
1765 IF v_debug_flag = 'Y' THEN
1766 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_receipt_source_code is ' || v_receipt_source_code);
1767 END IF;
1768 -------------------------------------------------------------------------------------------------gsr
1769 IF p_source_document_code IN ('PO', 'REQ','RMA','INVENTORY') AND /*rchandan for bug#6030615..added INVENTORY*/
1770 p_destination_type_code IN ('RECEIVING', 'INVENTORY')
1771 THEN
1772
1773 -- Start of addition by Srihari on 04-APR-2000
1774
1775
1776 IF p_transaction_type IN ('RECEIVE', 'UNORDERED') AND
1777 p_source_document_code = 'PO'
1778 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
1779 THEN
1780
1781 FOR excise_rec IN
1782 (SELECT rsl.shipment_header_id
1783 FROM rcv_shipment_lines rsl
1784 Where rsl.to_organization_id = p_organization_id
1785 and exists -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. rsl.po_header_id in
1786 (select pha.po_header_id
1787 from po_headers_all pha
1788 Where pha.vendor_site_id=p_vendor_site_id
1789 and po_header_id = rsl.po_header_id -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
1790 )
1791 and rsl.shipment_line_id in
1792 (select jrl.shipment_line_id
1793 from JAI_RCV_LINES jrl
1794 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. WHERE jrl.excise_invoice_no = p_attribute1
1795 -- AND jrl.excise_invoice_date = p_attribute2
1796 WHERE jrl.excise_invoice_no = lv_excise_invoice_no
1797 AND jrl.excise_invoice_date = lv_excise_invoice_date
1798 )
1799 )
1800 LOOP
1801 IF p_shipment_header_id <> excise_rec.shipment_header_id THEN
1802 IF v_debug_flag = 'Y' THEN
1803 UTL_FILE.PUT_LINE(v_myfilehandle,'error 1 dup exc inv ' );
1804 END IF;
1805 errormsg:='Duplicate Excise invoice NUMBER FOR the same supplier site';
1806 RAISE_APPLICATION_ERROR (-20501, 'Duplicate Excise invoice NUMBER FOR the same supplier site');
1807 END IF;
1808 END LOOP;
1809 END IF; --End if for Transaction Type in RECEIVE, UNORDERED.
1810
1811 IF v_debug_flag = 'Y' THEN
1812 UTL_FILE.PUT_LINE(v_myfilehandle,'Before UOM Cursor for Receipt ');
1813 END IF;
1814 -- End of addition by Srihari on 04-APR-2000
1815 -- Start of addition by Srihari on 30-NOV-99
1816 OPEN uom_cur(p_unit_of_measure);
1817 FETCH uom_cur INTO v_rcv_uom_code;
1818 CLOSE uom_cur;
1819
1820 IF v_debug_flag = 'Y' THEN
1821 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_rcv_uom_code is ' || v_rcv_uom_code);
1822 END IF;
1823
1824 IF p_source_document_code = 'PO' THEN
1825 --Changed by Nagaraj.s for Bug2531667
1826 FOR ll_rec IN (SELECT plla.unit_meas_lookup_code ll_uom,
1827 pla.unit_meas_lookup_code l_uom
1828 FROM po_line_locations_all plla,
1829 po_lines_all pla
1830 WHERE plla.line_location_id = p_line_location_id
1831 AND pla.po_header_id = plla.po_header_id
1832 AND pla.po_line_id = p_po_line_id)
1833 LOOP
1834 IF ll_rec.ll_uom IS NOT NULL THEN
1835 v_po_uom := ll_rec.ll_uom;
1836 ELSE
1837 v_po_uom := ll_rec.l_uom;
1838 END IF;
1839
1840 END LOOP;
1841
1842 ELSIF p_source_document_code = 'REQ' THEN
1843
1844 FOR req_rec IN (SELECT unit_meas_lookup_code r_uom
1845 FROM po_requisition_lines_all
1846 WHERE requisition_line_id = p_requisition_line_id)
1847 LOOP
1848 v_po_uom := req_rec.r_uom;
1849 END LOOP;
1850 --Gsr
1851 ELSIF p_source_document_code = 'RMA' THEN
1852
1853 FOR rma_rec IN (SELECT order_quantity_uom rma_uom
1854 FROM oe_order_lines_all
1855 WHERE HEADER_ID = p_oe_order_header_id)
1856 LOOP
1857 v_po_uom := rma_rec.rma_uom;
1858 END LOOP;
1859
1860 ELSIF p_source_document_code = 'INVENTORY' THEN /*rchandan for bug#6030615...start*/
1861
1862 open c_rec_ship_txn(p_shipment_line_id);
1863 fetch c_rec_ship_txn into r_rec_ship_txn;
1864 close c_rec_ship_txn;
1865
1866 OPEN c_get_inv_trx_info(r_rec_ship_txn.mmt_transaction_id);
1867 FETCH c_get_inv_trx_info INTO ln_trx_qty , lv_trx_uom , ln_orig_id , ln_item_cost;
1868 CLOSE c_get_inv_trx_info;
1869
1870 /*
1871 ln_orig_id : this field has the original_transaction_Temp_id
1872 */
1873
1874 OPEN c_jai_mtl_Trxs(ln_orig_id);
1875 FETCH c_jai_mtl_Trxs INTO r_jai_mtl_Trxs;
1876 CLOSE c_jai_mtl_Trxs;
1877
1878 v_po_uom := lv_trx_uom; /*rchandan for bug#6030615...end*/
1879
1880 END IF; --End if for p_source_document_code
1881 --Gsr
1882
1883 --start additions for bug#10086567,added the if condition
1884 if p_source_document_code='RMA'
1885 then
1886 v_po_uom_code := v_po_uom;
1887 else
1888
1889 OPEN uom_cur(v_po_uom);
1890 FETCH uom_cur INTO v_po_uom_code;
1891 CLOSE uom_cur;
1892
1893 end if;
1894 --end bug#10086567
1895 IF v_debug_flag = 'Y' THEN
1896 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_po_uom_code is ' || v_po_uom_code);
1897 END IF;
1898
1899
1900 jai_rcv_utils_pkg.get_organization(p_shipment_line_id,
1901 v_organization_id,
1902 v_item_id);
1903 IF v_debug_flag = 'Y' THEN
1904 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_organization_id is ' || v_organization_id);
1905 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_item_id is ' || v_item_id);
1906 END IF;
1907
1908 --Pick Item Modvat Flag---------------------------------------------------------------------
1909 --Moved this Piece of Code from the Bottom to Ensure that Item Modvat Flag and Trading Flag
1910 --are picked well in advance by Nagaraj.s for Bug2915783..................................
1911 FOR mod_rec IN (SELECT
1912 modvat_flag,
1913 item_trading_flag,
1914 item_class --Added by Nagaraj.s for Bug3202319
1915 FROM JAI_INV_ITM_SETUPS
1916 WHERE organization_id = v_organization_id
1917 AND inventory_item_id = v_item_id)
1918 LOOP
1919 v_item_modvat_flag := NVL(mod_rec.modvat_flag, 'N');
1920 v_item_trading_flag := NVL(mod_rec.item_trading_flag, 'N');
1921 v_item_class := mod_rec.item_class; --Added by Nagaraj.s for Bug3202319
1922 END LOOP;
1923
1924 IF v_debug_flag = 'Y' THEN
1925 UTL_FILE.PUT_LINE(v_myfilehandle,'The value of v_item_modvat_flag is ' || v_item_modvat_flag);
1926 UTL_FILE.PUT_LINE(v_myfilehandle,'The value of v_item_trading_flag is ' || v_item_trading_flag);
1927 END IF;
1928 v_item_modvat_flag := NVL(v_item_modvat_flag, 'N');
1929 /* Bug 4568090. Added by Lakshmi Gopalsami
1930 Value should be 'N' and not 'M' if the value is null */
1931 v_item_trading_flag := NVL(v_item_trading_flag, 'N');
1932 --Pick Item Modvat Flag---------------------------------------------------------------------
1933
1934 /* Start, following call added by Vijay Shankar for Bug#4250236(4245089). VAT Impl. */
1935 jai_inv_items_pkg.jai_get_attrib(
1936 p_regime_code => jai_constants.vat_regime,
1937 p_organization_id => p_organization_id,
1938 p_inventory_item_id => v_item_id,
1939 p_attribute_code => jai_constants.rgm_attr_item_recoverable,
1940 p_attribute_value => lv_vat_recoverable_for_item,
1941 p_process_flag => lv_process_flag,
1942 p_process_msg => lv_process_msg
1943 );
1944
1945 IF lv_process_flag = jai_constants.unexpected_error THEN
1946 errormsg:='Error from jai_inv_items_pkg.jai_get_attrib: Error:'||lv_process_msg;
1947 RAISE_APPLICATION_ERROR( -20099, 'Error from jai_inv_items_pkg.jai_get_attrib: Error:'||lv_process_msg);
1948 END IF;
1949
1950 -- Default value for following variable is set as YES
1951 lv_vat_recoverable_for_item := nvl(lv_vat_recoverable_for_item, jai_constants.yes); -- CHK
1952 /* End, Vijay Shankar for Bug#4250236(4245089) */
1953 jai_cmn_utils_pkg.write_fnd_log_msg('jai_rcv_trx_prc.process_begin',' v_rcv_uom_code '||v_rcv_uom_code||' v_po_uom_code '||v_po_uom_code ||' v_item_id '||v_item_id);
1954 IF v_rcv_uom_code <> v_po_uom_code THEN
1955
1956 /*12990372 : inv_convert.inv_um_conversion will fetch the uom_rate with the below logic :
1957
1958 PROCEDURE inv_um_conversion(
1959 from_unit varchar2,
1960 to_unit varchar2,
1961 item_id number,
1962 uom_rate out number );
1963
1964 uom_rate := from_unit /to_unit ;
1965
1966 ex: from_unit (Ea)
1967 to_unit (Dozen ).
1968
1969 uom_rate := Ea/Dz
1970 =1/12
1971 =0.08
1972 */
1973
1974 Inv_Convert.inv_um_conversion(v_rcv_uom_code,
1975 v_po_uom_code,
1976 v_item_id,
1977 v_uom_rate);
1978
1979 jai_cmn_utils_pkg.write_fnd_log_msg('jai_rcv_trx_prc.process_begin',' v_uom_rate value after the INV call '||v_uom_rate);
1980
1981 IF v_uom_rate = -99999 THEN
1982 v_uom_rate := 0;
1983 END IF;
1984
1985 ELSE
1986
1987 v_uom_rate := 1;
1988
1989 END IF;
1990
1991 v_uom_rate := NVL(v_uom_rate, 1);
1992 jai_cmn_utils_pkg.write_fnd_log_msg('jai_rcv_trx_prc.process_begin',' The Value of v_uom_rate is'||v_uom_rate);
1993
1994 IF v_debug_flag = 'Y' THEN
1995 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_uom_rate is ' || v_uom_rate);
1996 END IF;
1997
1998 -- End of addition by Srihari on 30-NOV-99
1999 -- Not an unordered receipt --
2000
2001 Duplicate_shipment_update;
2002
2003 IF v_debug_flag = 'Y' THEN
2004 UTL_FILE.PUT_LINE(v_myfilehandle,'After Duplicate Shipment Update, v_receipt_source_code ->'||v_receipt_source_code);
2005 UTL_FILE.PUT_LINE(v_myfilehandle,'After Duplicate Shipment Update, p_transaction_type ->'||p_transaction_type);
2006 UTL_FILE.PUT_LINE(v_myfilehandle,'After Duplicate Shipment Update, v_duplicate_ship ->'||v_duplicate_ship);
2007 End IF;
2008 /* R12-PADDR
2009 IF v_debug_flag = 'Y' THEN
2010 UTL_FILE.PUT_LINE(v_myfilehandle,'After Duplicate Shipment Update, v_chk_form ->'||v_chk_form);
2011 END IF;
2012 R12-PADDR */
2013 ---------- First Receipt for this line location ------------
2014
2015 IF (
2016 ( v_receipt_source_code IS NOT NULL AND
2017 p_transaction_type = 'RECEIVE' AND
2018 v_receipt_source_code in ('VENDOR', 'INVENTORY') -- AND was commented by GSRI on 21-OCT-01 and OR was added
2019 /* R12-PADDR or v_chk_form IS NOT NULL */
2020 )
2021 OR
2022 ( v_receipt_source_code IS NOT NULL AND
2023 p_transaction_type = 'RECEIVE' AND
2024 v_receipt_source_code = 'CUSTOMER' -- AND changed to OR - ssumaith - bug# 3683666
2025 /* R12-PADDR or v_chk_form IS NOT NULL */
2026 )
2027 OR
2028 p_transaction_type = 'MATCH'
2029 )
2030 AND nvl(v_duplicate_ship, 'N') = 'N'
2031 THEN
2032
2033
2034 IF v_debug_flag = 'Y' THEN
2035 UTL_FILE.PUT_LINE(v_myfilehandle,'Inside the main If Condition');
2036 UTL_FILE.PUT_LINE(v_myfilehandle,'p_source_document_code = ' || p_source_document_code);
2037 END IF;
2038
2039 -------------------------- To retrieve po quantity -------------------------------------
2040
2041 FOR qty_rec IN (SELECT quantity
2042 FROM po_line_locations_all
2043 WHERE line_location_id = p_line_location_id)
2044 LOOP
2045 v_loc_quantity := qty_rec.quantity;
2046 END LOOP;
2047
2048 IF p_transaction_type = 'RECEIVE' -- AND was commented by GSRI on 21-OCT-01 and OR was added
2049 /* R12-PADDR OR v_chk_form IS NOT NULL */
2050 THEN
2051 set_receipt_flag;
2052 insert_receipt_line;
2053
2054 ELSIF p_transaction_type = 'MATCH' THEN
2055 set_receipt_flag;
2056 v_receipt_modify_flag := 'N';
2057 END IF;
2058
2059
2060 IF p_source_document_code = 'RMA' THEN
2061 --Gsr
2062 IF v_debug_flag = 'Y' THEN
2063 UTL_FILE.PUT_LINE(v_myfilehandle,'Inside the RMA Condition');
2064 END IF;
2065
2066
2067
2068 FOR lines_rec IN (SELECT
2069 rtl.tax_line_no,
2070 rtl.tax_id,
2071 rtl.tax_rate,
2072 rtl.qty_rate,
2073 rtl.uom,
2074 rtl.tax_amount,
2075 jtc.tax_type,
2076 jtc.tax_name,
2077 jtc.vendor_id,
2078 NVL(jtc.mod_cr_percentage, 0) modcp,
2079 NVL(jtc.rounding_factor, 0) rounding_factor,
2080 jtc.duty_drawback_percentage duty,
2081 --3848133
2082 rtl.precedence_1,
2083 rtl.precedence_2,
2084 rtl.precedence_3,
2085 rtl.precedence_4,
2086 rtl.precedence_5,
2087 rtl.precedence_6, -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
2088 rtl.precedence_7,
2089 rtl.precedence_8,
2090 rtl.precedence_9,
2091 rtl.precedence_10,
2092 tax_types.regime_code regime_code
2093 --3848133
2094 FROM JAI_OM_OE_RMA_TAXES rtl,
2095 JAI_CMN_TAXES_ALL jtc
2096 , jai_regime_tax_types_v tax_types
2097 WHERE rtl.rma_line_id = p_oe_order_line_id
2098 AND jtc.tax_id = rtl.tax_id
2099 AND tax_types.tax_type(+) = jtc.tax_type
2100 )
2101 LOOP
2102
2103 -- Start of addition by Srihari on 30-NOV-99
2104 --Gsr
2105 /* Added by LGOPALSa. Bug 4210102.
2106 * ADded Excise and CVD education cess */
2107
2108 /* Bug 4568090. Added by LGOPALSA
2109 Added check for trading flag to ensure that
2110 recoverable flag is properly set for trading items*/
2111
2112 IF v_item_modvat_flag = 'N' AND
2113 v_item_trading_flag = 'N' AND
2114 upper(lines_rec.tax_type) IN ('EXCISE', 'ADDL. EXCISE', 'OTHER EXCISE', 'CVD',
2115 JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS,jai_constants.tax_type_exc_edu_cess,
2116 JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS,jai_constants.tax_type_cvd_edu_cess)/*Bug 5989740 bduvarag*/
2117 THEN
2118 v_tax_modvat_flag := 'N';
2119
2120 /* following elsif added by Vijay Shankar for Bug#4250236(4245089). VAT Impl. */
2121 ELSIF lv_vat_recoverable_for_item <> jai_constants.yes
2122 AND lines_rec.regime_code = jai_constants.vat_regime
2123 THEN
2124 v_tax_modvat_flag := jai_constants.yes; --Changed the constant to yes so that VAT taxes can be recovered in case of recoverable flag is No. For bug#8302581
2125
2126 ELSIF lines_rec.modcp > 0 THEN
2127 v_tax_modvat_flag := 'Y';
2128 END IF;
2129 --Gsr
2130 -- End of addition by Srihari on 30-NOV-99
2131 IF p_currency_code <> v_func_currency THEN
2132 v_conv_factor := NVL(v_currency_conversion_rate, 1);
2133 ELSE
2134 v_conv_factor := 1;
2135 END IF;
2136
2137 FOR pick_rec IN (SELECT quantity
2138 FROM JAI_OM_OE_RMA_LINES rel
2139 WHERE rel.rma_line_id = p_oe_order_line_id)
2140 LOOP
2141 v_loc_quantity := pick_rec.quantity;
2142 END LOOP;
2143
2144 IF NVL(v_loc_quantity, 0) <> 0 THEN
2145 v_cor_amount := ROUND((P_qty_received * lines_rec.tax_amount * v_uom_rate / v_loc_quantity),
2146 NVL(lines_rec.rounding_factor, 0));
2147 END IF;
2148 /* Added by LGOPALSA. Bug 4210102
2149 * Added CVD, Excise and Customs edcuation cess */
2150
2151 IF upper(lines_rec.tax_type) IN ('EXCISE', 'ADDL. EXCISE', 'OTHER EXCISE', 'CUSTOMS', 'CVD',
2152 JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS,jai_constants.tax_type_Exc_edu_cess,
2153 JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS,jai_constants.tax_type_cvd_Edu_cess,
2154 JAI_CONSTANTS.TAX_TYPE_SH_CUSTOMS_EDU_CESS,jai_constants.tax_type_customs_edu_cess)/*Bug5989740 bduvarag*/
2155 THEN
2156 v_claimable_amount := NVL(v_cor_amount * v_conv_factor, 0) * NVL(lines_rec.duty, 0) / 100;
2157 ELSE
2158 v_claimable_amount := 0;
2159 END IF;
2160
2161 --Added by GSRI on 21-OCT-01
2162 SELECT COUNT(*)
2163 INTO v_chk_receipt_tax_lines
2164 FROM JAI_RCV_LINE_TAXES
2165 WHERE shipment_line_id = p_shipment_line_id
2166 AND shipment_header_id = p_shipment_header_id
2167 AND tax_id = lines_rec.tax_id;
2168
2169 IF v_chk_receipt_tax_lines = 0 THEN
2170 /*
2171 DELETE FROM JAI_RCV_LINE_TAXES
2172 WHERE shipment_line_id = p_shipment_line_id AND
2173 shipment_header_id = p_shipment_header_id AND
2174 tax_id = lines_rec.tax_id;*/
2175 --End Addition by on GSRI 21-OCT-01
2176
2177 v_tax_modvat_flag := NVL(v_tax_modvat_flag,'N') ;
2178 INSERT INTO JAI_RCV_LINE_TAXES
2179 (
2180 shipment_line_id,
2181 tax_line_no,
2182 shipment_header_id,
2183 tax_id,
2184 tax_name,
2185 currency,
2186 tax_rate,
2187 qty_rate,
2188 uom,
2189 tax_amount,
2190 tax_type,
2191 modvat_flag,
2192 creation_date,
2193 created_by,
2194 last_update_date,
2195 last_updated_by,
2196 last_update_login,
2197 vendor_id,
2198 claimable_amount,
2199 --3848133
2200 precedence_1,
2201 precedence_2,
2202 precedence_3,
2203 precedence_4,
2204 precedence_5,
2205 precedence_6, -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
2206 precedence_7,
2207 precedence_8,
2208 precedence_9,
2209 precedence_10,
2210 transaction_id
2211 --3848133
2212 )
2213 VALUES
2214 (
2215 p_shipment_line_id,
2216 lines_rec.tax_line_no,
2217 p_shipment_header_id,
2218 lines_rec.tax_id,
2219 lines_rec.tax_name,
2220 p_currency_code,
2221 lines_rec.tax_rate,
2222 lines_rec.qty_rate,
2223 lines_rec.uom,
2224 v_cor_amount,
2225 lines_rec.tax_type,
2226 v_tax_modvat_flag, --NVL(v_tax_modvat_flag,'N'),
2227 p_creation_date,
2228 p_created_by,
2229 p_last_update_date,
2230 p_last_updated_by,
2231 p_last_update_login,
2232 lines_rec.vendor_id,
2233 v_claimable_amount,
2234 --3848133
2235 lines_rec.precedence_1,
2236 lines_rec.precedence_2,
2237 lines_rec.precedence_3,
2238 lines_rec.precedence_4,
2239 lines_rec.precedence_5,
2240 lines_rec.precedence_6, -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
2241 lines_rec.precedence_7,
2242 lines_rec.precedence_8,
2243 lines_rec.precedence_9,
2244 lines_rec.precedence_10,
2245 p_transaction_id
2246 --3848133
2247 );
2248
2249 END IF;
2250
2251 IF lines_rec.tax_type NOT IN ('TDS', 'Modvat Recovery') THEN
2252 v_tax_total := v_tax_total + NVL(v_cor_amount * v_conv_factor, 0);
2253 END IF;
2254
2255 END LOOP;
2256 --Gsr
2257 --ELSE -- commented by sriram bug # 2514719
2258 --Changed by Nagaraj.s for Bug # 2588096
2259
2260 ELSIF p_source_document_code = 'PO' AND p_transaction_type <> 'UNORDERED' THEN
2261
2262 -- the above elsif added by sriram bug # 2514719 on aug 20th
2263 -- ISO CYCLE AS the following STATEMENT IS ONLY applicable FOR PO transactions
2264 -- Start addition by Aparajita for bug#2415767 on 17th june 2002
2265
2266 BEGIN
2267 SELECT currency_code,
2268 rate
2269 INTO v_po_currency,
2270 v_po_rate
2271 FROM po_headers_all
2272 WHERE po_header_id = p_po_header_id;
2273 EXCEPTION
2274 WHEN OTHERS THEN
2275 IF v_debug_flag = 'Y' THEN
2276 UTL_FILE.PUT_LINE(v_myfilehandle,'error 2 fetch po curr ' );
2277 END IF;
2278 errormsg:='Error while fetching PO currency details :' || SQLERRM;
2279 RAISE_APPLICATION_ERROR (-20501, 'Error while fetching PO currency details :' || SQLERRM);
2280 END;
2281 /*Bug 4644524 start bduvarag*/
2282 OPEN c_rcv_shipment_lines(p_shipment_line_id);
2283 FETCH c_rcv_shipment_lines INTO r_rcv_shipment_lines;
2284 CLOSE c_rcv_shipment_lines;
2285 /*Bug 4644524 End bduvarag*/
2286
2287 IF v_debug_flag = 'Y' THEN
2288 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_po_currency is ' || v_po_currency);
2289 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_po_rate is ' || v_po_rate);
2290 END IF;
2291 -- end addition by Aparajita for bug#2415767 on 17th june 2002
2292
2293
2294 /* iSupplier porting
2295 || start - ssumaith - ASBN
2296 */
2297 OPEN c_check_asbn;
2298 FETCH c_check_asbn INTO lv_asbn_type , lv_shipment_num;
2299 CLOSE c_check_asbn;
2300
2301 IF lv_asbn_type = '1' THEN
2302 lv_asbn_type := 'TRUE';
2303 ELSE
2304 lv_asbn_type := 'FALSE';
2305 END IF;
2306
2307 IF p_source_document_code = 'PO' AND p_transaction_type <> 'UNORDERED' AND lv_asbn_type <> 'TRUE' THEN
2308
2309 OPEN c_po_tax_cur FOR
2310 SELECT tax_line_no,
2311 llt.tax_id,
2312 DECODE(SIGN(jtc.end_date - SYSDATE), -1, 0, llt.tax_rate) tax_rate,
2313 DECODE(SIGN(jtc.end_date - SYSDATE), -1, 0, llt.qty_rate) qty_rate,
2314 uom,
2315 --DECODE(SIGN(jtc.end_date - SYSDATE), -1, 0, llt.tax_amount) tax_amount,
2316 DECODE(SIGN(jtc.end_date - SYSDATE),
2317 -1,
2318 0,
2319 DECODE(llt.currency,
2320 v_func_currency,
2321 llt.tax_amount,
2322 llt.tax_amount *
2323 nvl(v_currency_conversion_rate, 1))) tax_amount, --changed by pezheng for bug # 10630487
2324 llt.tax_type,
2325 jtc.tax_name,
2326 llt.modvat_flag,
2327 llt.vendor_id,
2328 jtc.vendor_id tax_vendor_id,
2329 jtc.vendor_site_id,
2330 llt.currency,
2331 jtc.rounding_factor,
2332 jtc.duty_drawback_percentage duty,
2333 llt.precedence_1,
2334 llt.precedence_2,
2335 llt.precedence_3,
2336 llt.precedence_4,
2337 llt.precedence_5
2338 , tax_types.regime_code regime_code,
2339 llt.precedence_6,
2340 llt.precedence_7,
2341 llt.precedence_8,
2342 llt.precedence_9,
2343 llt.precedence_10
2344 FROM JAI_PO_TAXES llt,
2345 JAI_CMN_TAXES_ALL jtc
2346 , jai_regime_tax_types_v tax_types
2347 WHERE line_location_id = p_line_location_id
2348 AND jtc.tax_id = llt.tax_id
2349 AND jtc.tax_type = tax_types.tax_type (+)
2350 order by tax_line_no;
2351
2352 ELSIF p_source_document_code = 'PO' AND lv_asbn_type = 'TRUE' THEN
2353
2354
2355 /*
2356 Code to populate the excise invoice number and date into the
2357 ja_in_Receipt_lines procedure in case of an asbn receipt.
2358 */
2359
2360
2361 OPEN c_jai_cmn_lines(v_shipment_num);
2362 FETCH c_jai_cmn_lines INTO r_jai_cmn_lines;
2363 CLOSE c_jai_cmn_lines;
2364
2365 v_loc_quantity := r_jai_cmn_lines.quantity;
2366
2367 update JAI_RCV_LINES
2368 set excise_invoice_no = r_jai_cmn_lines.excise_inv_number,
2369 excise_invoice_date=r_jai_cmn_lines.excise_inv_Date
2370 where shipment_line_id = p_shipment_line_id;
2371
2372 OPEN c_po_tax_cur FOR
2373 SELECT tax_line_no,
2374 llt.tax_id,
2375 DECODE(SIGN(jtc.end_date - SYSDATE), -1, 0, llt.tax_rate) tax_rate,
2376 DECODE(SIGN(jtc.end_date - SYSDATE), -1, 0, llt.qty_rate) qty_rate,
2377 llt.uom,
2378 DECODE(SIGN(jtc.end_date - SYSDATE), -1, 0, llt.tax_amt) tax_amount,
2379 llt.tax_type,
2380 jtc.tax_name,
2381 llt.modvat_flag,
2382 nvl(jtc.vendor_id,p_vendor_id) vendor_id, /*rchandan for bug#6030615*/
2383 jtc.vendor_id tax_vendor_id,
2384 jtc.vendor_site_id,
2385 llt.currency_code currency ,
2386 jtc.rounding_factor,
2387 jtc.duty_drawback_percentage duty,
2388 llt.precedence_1,
2389 llt.precedence_2,
2390 llt.precedence_3,
2391 llt.precedence_4,
2392 llt.precedence_5
2393 , tax_types.regime_code regime_code,
2394 llt.precedence_6,
2395 llt.precedence_7,
2396 llt.precedence_8,
2397 llt.precedence_9,
2398 llt.precedence_10
2399 FROM jai_cmn_document_taxes llt,
2400 jai_cmn_lines cml,
2401 JAI_CMN_TAXES_ALL jtc ,
2402 jai_regime_tax_types_v tax_types
2403 WHERE cml.po_line_location_id = p_line_location_id
2404 AND cml.cmn_line_id = llt.source_doc_line_id
2405 AND cml.shipment_number = lv_shipment_num
2406 AND llt.source_doc_type = 'ASBN'
2407 AND jtc.tax_id = llt.tax_id
2408 AND jtc.tax_type = tax_types.tax_type (+)
2409 order by tax_line_no;
2410 --iSupplier porting
2411
2412 END IF ; /* end if for
2413 p_source_document_code = 'PO' AND p_transaction_type <> 'UNORDERED' AND lv_asbn_type <> 'TRUE'
2414 */
2415 ELSIF p_source_document_code = 'INVENTORY' THEN
2416
2417 v_po_currency := 'INR';
2418 v_po_rate := 1;
2419
2420 open c_rec_ship_txn(p_shipment_line_id);
2421 fetch c_rec_ship_txn into r_rec_ship_txn;
2422 close c_rec_ship_txn;
2423
2424 OPEN c_rcv_shipment_lines(p_shipment_line_id);
2425 FETCH c_rcv_shipment_lines INTO r_rcv_shipment_lines;
2426 CLOSE c_rcv_shipment_lines;
2427
2428 OPEN c_get_inv_trx_info(r_rec_ship_txn.mmt_transaction_id);
2429 FETCH c_get_inv_trx_info INTO ln_trx_qty , lv_trx_uom , ln_orig_id , ln_item_cost;
2430 CLOSE c_get_inv_trx_info;
2431
2432 OPEN c_jai_mtl_Trxs(ln_orig_id);
2433 FETCH c_jai_mtl_Trxs INTO r_jai_mtl_Trxs;
2434 CLOSE c_jai_mtl_Trxs;
2435
2436 v_loc_quantity := ln_trx_qty;
2437
2438 update jai_rcv_lines
2439 set excise_invoice_no = r_jai_mtl_trxs.excise_invoice_no ,
2440 excise_invoice_Date = r_jai_mtl_trxs.creation_Date
2441 where shipment_line_id = p_shipment_line_id;
2442
2443 OPEN c_po_tax_cur FOR
2444 SELECT tax_line_no,
2445 llt.tax_id,
2446 DECODE(SIGN(jtc.end_date - SYSDATE), -1, 0, llt.tax_rate) tax_rate,
2447 DECODE(SIGN(jtc.end_date - SYSDATE), -1, 0, llt.qty_rate) qty_rate,
2448 llt.uom,
2449 DECODE(SIGN(jtc.end_date - SYSDATE), -1, 0, llt.tax_amt) tax_amount,
2450 llt.tax_type,
2451 jtc.tax_name,
2452 llt.modvat_flag,
2453 NVL(jtc.vendor_id,r_jai_mtl_Trxs.from_organization) vendor_id, /* 6030615*/
2454 jtc.vendor_id tax_vendor_id,
2455 jtc.vendor_site_id,
2456 llt.currency_code currency ,
2457 jtc.rounding_factor,
2458 jtc.duty_drawback_percentage duty,
2459 llt.precedence_1,
2460 llt.precedence_2,
2461 llt.precedence_3,
2462 llt.precedence_4,
2463 llt.precedence_5,
2464 tax_types.regime_code regime_code,
2465 llt.precedence_6,
2466 llt.precedence_7,
2467 llt.precedence_8,
2468 llt.precedence_9,
2469 llt.precedence_10
2470 FROM jai_cmn_document_taxes llt,
2471 JAI_CMN_TAXES_ALL jtc ,
2472 jai_regime_tax_types_v tax_types,
2473 mtl_material_transactions mtl
2474 WHERE llt.source_doc_line_id = mtl.original_transaction_temp_id
2475 AND llt.source_doc_type = 'INTERORG_XFER'
2476 AND jtc.tax_id = llt.tax_id
2477 AND mtl.transaction_id = r_rec_ship_txn.MMT_TRANSACTION_ID
2478 AND jtc.tax_type = tax_types.tax_type (+)
2479 order by tax_line_no;
2480 END IF; -- RMA
2481 --Added/Modified by walton for inclusive tax
2482 -------------------------------------------------------------
2483 IF p_source_document_code = 'PO' AND lv_asbn_type <> 'TRUE'
2484 THEN
2485 SELECT price_override , quantity
2486 INTO v_price_override,v_po_quantity
2487 FROM po_line_locations_all
2488 WHERE line_location_id = p_line_location_id;
2489 ELSIF p_source_document_code = 'PO' AND lv_asbn_type = 'TRUE'
2490 THEN
2491 v_po_quantity := r_jai_cmn_lines.quantity;
2492 v_price_override := r_jai_cmn_lines.po_unit_price;
2493 ELSIF p_source_document_code = 'INVENTORY' THEN
2494 v_price_override := ln_item_cost; /* currently hard coded */
2495 v_po_quantity := ln_trx_qty; /* currently hard coded */
2496 END IF;
2497
2498 -- Added by Jia for bug#8904043, Begin
2499 --------------------------------------------------------------------------------------------
2500 BEGIN
2501 Jai_Avlist_Validate_Pkg.Check_AvList_Validation( pn_party_id => p_vendor_id
2502 , pn_party_site_id => p_vendor_site_id
2503 , pn_inventory_item_id => v_item_id
2504 , pd_ordered_date => SYSDATE
2505 , pv_party_type => 'V'
2506 , pn_pricing_list_id => NULL
2507 );
2508 EXCEPTION
2509 WHEN OTHERS THEN
2510 errormsg := SQLERRM ;
2511 app_exception.raise_exception;
2512 END;
2513 --------------------------------------------------------------------------------------------
2514 -- Added by Jia for bug#8904043, Begin
2515
2516 -- Added by Jia for bug#8932471, Begin
2517 --------------------------------------------------------------------------------------------
2518 BEGIN
2519 Jai_Avlist_Validate_Pkg.Check_AvList_Validation( pn_party_id => p_vendor_id
2520 , pn_party_site_id => 0
2521 , pn_inventory_item_id => v_item_id
2522 , pd_ordered_date => SYSDATE
2523 , pv_party_type => 'V'
2524 , pn_pricing_list_id => NULL
2525 );
2526 EXCEPTION
2527 WHEN OTHERS THEN
2528 errormsg := SQLERRM ;
2529 app_exception.raise_exception;
2530 END;
2531 --------------------------------------------------------------------------------------------
2532 -- Added by Jia for bug#8932471, Begin
2533
2534
2535 v_assessable_value := NVL(jai_cmn_setup_pkg.get_po_assessable_value
2536 ( p_vendor_id, p_vendor_site_id, v_item_id, p_uom_code ),v_price_override);
2537
2538 IF p_source_document_code = 'INVENTORY'
2539 THEN
2540 v_price_override:=r_jai_mtl_Trxs.selling_price;
2541 v_assessable_value:=NVL(r_jai_mtl_Trxs.assessable_Value,v_price_override);
2542 END IF;
2543
2544
2545 OPEN c_jai_cmn_lines(v_shipment_num);
2546 FETCH c_jai_cmn_lines INTO r_jai_cmn_lines;
2547 CLOSE c_jai_cmn_lines;
2548
2549 ln_po_unit_price := 0;
2550 IF lv_asbn_type = 'TRUE'
2551 THEN
2552 ln_po_unit_price := r_jai_cmn_lines.po_unit_price;
2553 END IF ;
2554 If p_source_document_code <> 'INVENTORY' THEN /*rchandan - 6030615*/
2555 ln_vat_assess_value := jai_general_pkg.ja_in_vat_assessable_value (
2556 p_party_id => p_vendor_id,
2557 p_party_site_id => p_vendor_site_id,
2558 p_inventory_item_id => v_item_id,
2559 p_uom_code => p_uom_code,
2560 p_default_price => ln_po_unit_price * r_jai_cmn_lines.quantity,
2561 p_ass_value_date => trunc(sysdate) ,
2562 p_party_type => 'V'
2563 );
2564 IF ln_vat_assess_value=0
2565 THEN
2566 ln_vat_assess_value:=v_price_override;
2567 END IF;
2568
2569 else /*rchandan - 6030615*/
2570 v_price_override := r_jai_mtl_Trxs.selling_price;
2571 ln_vat_assess_value:=NVL(r_jai_mtl_Trxs.vat_assessable_Value/r_jai_mtl_Trxs.quantity,v_price_override); --added /r_jai_mtl_Trxs.quantity for bug#8880760
2572 /*vat assessable value in interorg is stored as vat_assessable_value per qty *shipment qty,hence divided with r_jai_mtl_Trxs.quantity for bug#8880760*/
2573 --r_jai_mtl_Trxs.quantity to get the per qty vat asse
2574 end if;
2575
2576 --Add by Xiao for GST refer to bug#10043656 on 14-Sep-2010, begin
2577 ------------------------------------------------------------------------------
2578 /* IF p_source_document_code <> 'INVENTORY' THEN
2579 ln_gst_assess_value := JAI_GST_GENERAL_PKG.GET_GST_ASSESSABLE_VALUE (
2580 p_party_id => p_vendor_id,
2581 p_party_site_id => p_vendor_site_id,
2582 p_inventory_item_id => v_item_id,
2583 p_uom_code => p_uom_code,
2584 p_default_price => ln_po_unit_price * r_jai_cmn_lines.quantity,
2585 p_ass_value_date => trunc(SYSDATE) ,
2586 p_party_type => 'V'
2587 );
2588 IF ln_gst_assess_value=0
2589 THEN
2590 ln_gst_assess_value:=v_price_override;
2591 END IF;
2592 ELSE
2593 v_price_override := r_jai_mtl_Trxs.selling_price;
2594 ln_gst_assess_value:=NVL(r_jai_mtl_Trxs.gst_assessable_Value/r_jai_mtl_Trxs.quantity,v_price_override);
2595 END IF;
2596 */
2597 ------------------------------------------------------------------------------
2598 --Add by Xiao for GST refer to bug#10043656 on 14-Sep-2010, end
2599 --added the IF block for bug#8920186
2600 IF v_rcv_uom_code <> v_po_uom_code and v_uom_rate > 0 THEN
2601
2602 jai_cmn_utils_pkg.write_fnd_log_msg('jai_rcv_trx_prc.process_begin',' Inside IF v_rcv_uom_code <> v_po_uom_code and v_uom_rate > 0 THEN ');
2603 jai_cmn_utils_pkg.write_fnd_log_msg('jai_rcv_trx_prc.process_begin',' Inside IF v_rcv_uom_code <> v_po_uom_code and v_uom_rate > 0 THEN v_po_quantity is '||v_po_quantity);
2604 -- ln_vat_assess_value:=ln_vat_assess_value*v_po_quantity/v_uom_rate; commented for bug#12990372
2605 ln_vat_assess_value:=ln_vat_assess_value*v_po_quantity*v_uom_rate; --12990372 24 nov
2606 /*12990372 conversion of vat assessable value from PO uom to receipt UOM
2607 i.e we will get the vat assessable value as the unit price of the item for receipt uom code*/
2608
2609 --v_assessable_value:=v_assessable_value*v_po_quantity/v_uom_rate; commented for bug#12990372
2610
2611 v_assessable_value:=v_assessable_value*v_po_quantity*v_uom_rate; --12990372 24 nov
2612
2613 jai_cmn_utils_pkg.write_fnd_log_msg('jai_rcv_trx_prc.process_begin',' Inside IF v_rcv_uom_code <> v_po_uom_code and v_uom_rate > 0 THEN ln_vat_assess_value is '|| ln_vat_assess_value);
2614
2615 ELSE
2616 --start additions for bug#8880760
2617 ln_vat_assess_value:=ln_vat_assess_value*v_po_quantity;
2618 v_assessable_value:=v_assessable_value*v_po_quantity;
2619 --ln_gst_assess_value:=ln_gst_assess_value*v_po_quantity;--Add by Xiao for GST refer to bug#10043656 on 14-Sep-2010
2620 END IF;
2621
2622 /* proportionate the assessable value based on transaction quantity and receipt quantity
2623 here v_loc quantity is the transaction qty i.e the PO qty */
2624
2625 jai_cmn_utils_pkg.write_fnd_log_msg('jai_rcv_trx_prc.process_begin',' transaction quantity v_loc_quantity is '||v_loc_quantity||' P_qty_received '||P_qty_received);
2626 -- ln_vat_assess_value:=(ln_vat_assess_value / v_loc_quantity) * P_qty_received * v_uom_rate; commented for bug#12990372
2627 ln_vat_assess_value:=(ln_vat_assess_value / v_loc_quantity) * P_qty_received ; --added for bug#12990372
2628 jai_cmn_utils_pkg.write_fnd_log_msg('jai_rcv_trx_prc.process_begin',' final vat_assessable value arrived is ln_vat_assess_value '||ln_vat_assess_value);
2629 --v_assessable_value:=(v_assessable_value / v_loc_quantity) * P_qty_received * v_uom_rate; commented for bug#12990372
2630 v_assessable_value:=(v_assessable_value / v_loc_quantity) * P_qty_received ;--added for bug#12990372
2631 v_precedence_0 := v_price_override * v_po_quantity;
2632
2633 /*v_precedence_0 is the taxable basis on which the tax rate will be applied (i.e line amount /assessable value).*/
2634
2635 v_precedence_0 :=(v_precedence_0 / v_loc_quantity) * P_qty_received * v_uom_rate;
2636 --end additions for bug#8880760
2637
2638 /*commented the code and added whatever is required inside the loop
2639
2640 If p_source_document_code = 'INVENTORY'/*rchandan - 6030615..start*
2641 and po_lines_rec.regime_code <> jai_constants.vat_regime
2642 and upper(po_lines_rec.tax_type) NOT IN ('EXCISE', 'ADDL. EXCISE', 'OTHER EXCISE')
2643 THEN
2644 v_price_override := r_jai_mtl_Trxs.selling_price;
2645 END IF;
2646 -- bug 6488829 uncommented the if condition *
2647 IF p_source_document_code = 'INVENTORY' AND po_lines_rec.regime_code = jai_constants.vat_regime THEN
2648 v_precedence_0 := v_price_override;
2649 ELSE
2650 ln_vat_assess_value:=ln_vat_assess_value*v_po_quantity;
2651 v_assessable_value:=v_assessable_value*v_po_quantity;
2652 v_precedence_0 := v_price_override * v_po_quantity;
2653 END IF; /*rchandan - 6030615 end
2654
2655 IF v_po_currency <> po_lines_rec.currency THEN
2656 v_precedence_0 := v_precedence_0 * p_currency_conversion_rate;
2657 ln_vat_assess_value:=ln_vat_assess_value*p_currency_conversion_rate;
2658 v_assessable_value:=v_assessable_value*p_currency_conversion_rate;
2659 END IF;
2660
2661 -- proportionate the tax amount based on quantity in PO and receipt
2662 v_precedence_0 :=(v_precedence_0 / v_loc_quantity) * P_qty_received * v_uom_rate;
2663 -- v_uom_rate is added in the above line by Aparajita for bug#2929171
2664
2665 ln_vat_assess_value:=(ln_vat_assess_value / v_loc_quantity) * P_qty_received * v_uom_rate;
2666
2667 v_assessable_value:=(v_assessable_value / v_loc_quantity) * P_qty_received * v_uom_rate;
2668 */
2669 -------------------------------------------------------------------------------------------
2670
2671 ln_curflag := 0; --Add by Kevin Cheng for bug 6853787 Mar 5, 2008
2672
2673 --commented out by shyan for bug 10100899 on 12/11/2010, Begin
2674 -------------------------------------------------------------------
2675 -- p_index := 1; --Added by nprashar for bug # 10100899
2676 -------------------------------------------------------------------
2677 --commented out by shyan for bug 10100899 on 12/11/2010, End
2678
2679 IF c_po_tax_cur%ISOPEN THEN /*rchandan for bug#6030615*/
2680 ln_curflag := 1; --Add by Kevin Cheng for bug 6853787 Mar 5, 2008
2681
2682 --added by pezheng for bug 10630487, v_precedence_0 should be calculated out of the tax loop rather than in
2683 v_assessable_val_defined := jai_cmn_setup_pkg.get_po_assessable_value( p_vendor_id, p_vendor_site_id, v_item_id, p_uom_code ); -- added by nkodakan for bug 14590356.
2684 ln_vat_assess_val_defined:=r_jai_mtl_Trxs.vat_assessable_Value/r_jai_mtl_Trxs.quantity; -- added by nkodakan for bug 14590356.
2685 -- v_assessable_val_defined,ln_vat_assess_val_defined holds the assessable if it is defined..If not defined then its value becomes null;
2686 IF v_po_currency <> v_func_currency THEN
2687 -- by nkodakan for the bug 14590356 starts
2688 IF (v_assessable_val_defined is null) THEN
2689 v_assessable_value := v_assessable_value*p_currency_conversion_rate;
2690 END IF;
2691
2692 IF (ln_vat_assess_val_defined is null) THEN
2693 ln_vat_assess_value := ln_vat_assess_value*p_currency_conversion_rate;
2694 END IF;
2695 -- by nkodakan for the bug 14590356 ends
2696 v_precedence_0 := v_precedence_0 * p_currency_conversion_rate;
2697 -- commented the below two lines by nkodakan for the bug 14590356
2698 --v_assessable_value := v_assessable_value*p_currency_conversion_rate;
2699 --ln_vat_assess_value := ln_vat_assess_value*p_currency_conversion_rate;
2700 --ln_gst_assess_value:=ln_gst_assess_value*p_currency_conversion_rate;
2701 END IF;
2702 --added by pezheng for bug 10630487 ends
2703 LOOP
2704 fetch c_po_tax_cur INTO po_lines_rec;
2705 exit when c_po_tax_cur%notFOUND;
2706 --start additions for bug#8880760
2707
2708 --commented out by shyan for bug 10100899 on 12/11/2010, Begin
2709 ----------------------------------------------------------------------------------------
2710 --p_curr_check(p_index) := po_lines_rec.currency; --Added by nprashar for bug # 10100899
2711 --p_index := p_index + 1; --Added by nprashar for bug # 10100899
2712 ----------------------------------------------------------------------------------------
2713 --commented out by shyan for bug 10100899 on 12/11/2010, End
2714
2715 /*comment this if section by pezheng for bug 10630487, this should be done out of loop*/
2716 /*IF v_po_currency <> po_lines_rec.currency THEN
2717
2718 -- commented out by shyan for bug 10100899 on 12/11/2010, Begin
2719 ----------------------------------------------------------------------
2720 --Commented this condition for bug # 10100899 by nprashar IF c_po_tax_cur%ROWCOUNT = 1 Then \*Added by nprashar for bug #9868750*\
2721 -- IF ln_check_curr_conv = 0 Then --Added by nprashar for bug # 10100899
2722 --------------------------------------------------------------------------------
2723 -- commented out by shyan for bug 10100899 on 12/11/2010, End
2724
2725 v_precedence_0 := v_precedence_0 * p_currency_conversion_rate;
2726 v_assessable_value:=v_assessable_value*p_currency_conversion_rate;
2727
2728 -- commented out by shyan for bug 10100899 on 12/11/2010, Begin
2729 ----------------------------------------------------------------------
2730 -- ln_check_curr_conv := 1; --Added by nprashar for bug # 10100899
2731 ----------------------------------------------------------------------
2732 -- commented out by shyan for bug 10100899 on 12/11/2010, End
2733
2734 ln_vat_assess_value:=ln_vat_assess_value*p_currency_conversion_rate;
2735 ln_gst_assess_value:=ln_gst_assess_value*p_currency_conversion_rate;--Add by Xiao for GST refer to bug#10043656 on 19-Sep-2010
2736 --end if; --(IF ln_check_curr_conv = 0) commented out by shyan for bug 10100899 on 12/11/2010
2737 end if; \*Ends here for bug # 9868750 *\*/
2738 /*comment this if section by pezheng for bug 10630487, end*/
2739 --end additions for bug#8880760
2740
2741
2742 --added by walton for inclusive tax on 01-Jan-08
2743 -----------------------------------------------------------------
2744 lt_tax_table(lt_tax_table.count+1) := po_lines_rec;
2745 p1(row_count) := nvl(po_lines_rec.precedence_1,-1);
2746 p2(row_count) := nvl(po_lines_rec.precedence_2,-1);
2747 p3(row_count) := nvl(po_lines_rec.precedence_3,-1);
2748 p4(row_count) := nvl(po_lines_rec.precedence_4,-1);
2749 p5(row_count) := nvl(po_lines_rec.precedence_5,-1);
2750 p6(row_count) := nvl(po_lines_rec.precedence_6,-1);
2751 p7(row_count) := nvl(po_lines_rec.precedence_7,-1);
2752 p8(row_count) := nvl(po_lines_rec.precedence_8,-1);
2753 p9(row_count) := nvl(po_lines_rec.precedence_9,-1);
2754 p10(row_count):= nvl(po_lines_rec.precedence_10,-1);
2755 tax_rate_tab(row_count) := NVL(po_lines_rec.tax_rate,0);
2756
2757 IF po_lines_rec.tax_rate is null
2758 THEN
2759 tax_rate_zero_tab(row_count) := 0;
2760 ELSIF po_lines_rec.tax_rate = 0
2761 THEN
2762 tax_rate_zero_tab(row_count) := -9999;
2763 ELSE
2764 tax_rate_zero_tab(row_count) := po_lines_rec.tax_rate;
2765 END IF; --End of po_lines_rec.tax_rate is null
2766
2767 round_factor_tab(row_count):=NVL(po_lines_rec.rounding_factor,0);
2768
2769 lt_tax_rate_per_rupee(row_count):=NVL(po_lines_rec.tax_rate,0)/100;
2770 ln_total_tax_per_rupee:=0;
2771 tax_amt_tab(row_count) := 0;
2772 base_tax_amt_tab(row_count) := 0;
2773 lt_tax_amt_rate_tax_tab(row_count):=0;
2774 lt_tax_amt_non_rate_tab(row_count):=0;
2775 OPEN c_get_inclusive_flag ( po_lines_rec.tax_id);
2776 FETCH c_get_inclusive_flag
2777 INTO lt_inclusive_tax_tab(row_count),
2778 lv_valid_date,
2779 lt_adhoc_tax_tab(row_count);--added by qiong.liu for bug12717416
2780 CLOSE c_get_inclusive_flag;
2781
2782 IF lv_valid_date IS NULL OR lv_valid_date >= SYSDATE THEN
2783 end_date_tab(row_count) := 1;
2784 ELSE
2785 end_date_tab(row_count) := 0;
2786 END IF;
2787
2788 IF upper(po_lines_rec.tax_type) IN('EXCISE', 'ADDL. EXCISE', 'OTHER EXCISE', 'CVD',
2789 JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS,jai_constants.tax_type_exc_edu_cess,
2790 JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS,jai_constants.tax_type_cvd_edu_cess)
2791 THEN
2792 tax_type_tab(row_count) := 1;
2793 ELSIF po_lines_rec.regime_code=jai_constants.vat_regime
2794 THEN
2795 tax_type_tab(row_count) := 4;
2796 --Add by Xiao for GST refer to bug#10043656 on 14-Sep-2010, begin
2797 ------------------------------------------------------------------
2798 /* ELSIF upper(po_lines_rec.regime_code) IN (jai_constants.cgst_regime, jai_constants.sgst_regime)
2799 THEN
2800 tax_type_tab(row_count) := 7; */
2801 -----------------------------------------------------------------
2802 --Add by Xiao for GST refer to bug#10043656 on 14-Sep-2010, end
2803 ELSE
2804 tax_type_tab(row_count) := 0;
2805 END IF;
2806 ------------------------------------------------------------------
2807
2808 IF v_item_modvat_flag = 'N' AND
2809 v_item_trading_flag = 'N' AND
2810 upper(po_lines_rec.tax_type) IN ('EXCISE', 'ADDL. EXCISE', 'OTHER EXCISE', 'CVD',
2811 JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS,jai_constants.tax_type_exc_edu_cess,
2812 JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS,jai_constants.tax_type_cvd_edu_cess)/*bug5989740 bduvarag*/
2813 THEN
2814 v_tax_modvat_flag := 'N';
2815 /* following elsif added by Vijay Shankar for Bug#4250236(4245089). VAT Impl. */
2816 /*Commented for Bug 4644524 bduvarag*/
2817 -- ELSIF lv_vat_recoverable_for_item <> jai_constants.yes
2818 ELSIF (lv_vat_recoverable_for_item <> jai_constants.yes OR r_rcv_shipment_lines.item_id IS NULL)
2819 AND po_lines_rec.regime_code = jai_constants.vat_regime
2820 THEN
2821 v_tax_modvat_flag := jai_constants.no;
2822
2823 ELSE
2824 v_tax_modvat_flag := po_lines_rec.modvat_flag;
2825 END IF;
2826 -- End of addition by Srihari on 30-NOV-99
2827
2828 IF v_debug_flag = 'Y' THEN
2829 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_tax_modvat_flag is ' || v_tax_modvat_flag);
2830 END IF;
2831 lt_tax_modvat_flag(row_count):=v_tax_modvat_flag; --Added by walton for inclusive
2832
2833 IF po_lines_rec.currency <> v_func_currency THEN
2834 v_conv_factor := NVL(v_currency_conversion_rate, 1);
2835 ELSE
2836 v_conv_factor := 1;
2837 END IF;
2838
2839 --Added by Nagaraj.s for Bug3037075
2840 --This is to set the Third Party Flag for proper value.
2841 if po_lines_rec.vendor_id <> p_vendor_id
2842 and upper(po_lines_rec.tax_type) not in ('TDS', 'MODVAT RECOVERY')
2843 and po_lines_rec.vendor_id > 0
2844 then
2845 v_third_party_flag := 'Y';
2846 --To ensure that proper vendor site id is populated into default_taxes_onto_line
2847 if po_lines_rec.vendor_id = po_lines_rec.tax_vendor_id then
2848 v_tax_vendor_site_id(row_count) := po_lines_rec.vendor_site_id; /*Added by nprshar for bug # 10193326 */
2849 else
2850 v_tax_vendor_site_id(row_count) := null; /*Added by nprshar for bug # 10193326 */
2851 end if;
2852 else
2853 v_third_party_flag := 'N';
2854 v_tax_vendor_site_id (row_count) := null; /*Added by nprshar for bug # 10193326 */ --Added by Nagaraj.s for Bug3237536.
2855 --This was to be done as a part of Bug3037075
2856 --And as this variable was not reinitialized, hence in case of Receipts
2857 --where Excise was present after Adhoc, the Vendor site id was populated
2858 --for the Non Third Party line also.
2859
2860 end if; -- End of po_lines_rec.vendor_id <> p_vendor_id
2861
2862 lt_third_party_flag(row_count):=v_third_party_flag;--Added by walton for inclusive
2863
2864
2865 IF po_lines_rec.precedence_1 IS NOT NULL OR
2866 po_lines_rec.precedence_2 IS NOT NULL OR
2867 po_lines_rec.precedence_3 IS NOT NULL OR
2868 po_lines_rec.precedence_4 IS NOT NULL OR
2869 po_lines_rec.precedence_5 IS NOT NULL OR
2870 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 )
2871 po_lines_rec.precedence_7 IS NOT NULL OR
2872 po_lines_rec.precedence_8 IS NOT NULL OR
2873 po_lines_rec.precedence_9 IS NOT NULL OR
2874 po_lines_rec.precedence_10 IS NOT NULL
2875 THEN
2876 lt_tax_amt_non_rate_tab(row_count):=0;
2877 ----Changed by qiong.liu for bug12717416 begin
2878 -----------------------------------------------
2879 END IF;--End of po_lines_rec.precedence_1 IS NOT NULL OR
2880 IF lt_adhoc_tax_tab(row_count)='Y' or (lt_adhoc_tax_tab(row_count)='N' and po_lines_rec.qty_rate is not null) THEN /* Added or condition for bug 14696186 */
2881 lt_tax_amt_non_rate_tab(row_count) := (po_lines_rec.tax_amount / v_loc_quantity) * P_qty_received * v_uom_rate ;
2882 tax_amt_tab(row_count) := (po_lines_rec.tax_amount / v_loc_quantity) * P_qty_received * v_uom_rate ;
2883 base_tax_amt_tab(row_count) := (po_lines_rec.tax_amount / v_loc_quantity) * P_qty_received * v_uom_rate ;
2884 /*IF v_po_currency <> v_func_currency THEN
2885 lt_tax_amt_non_rate_tab(row_count) := lt_tax_amt_non_rate_tab(row_count)*p_currency_conversion_rate ;
2886 tax_amt_tab(row_count) := tax_amt_tab(row_count) * p_currency_conversion_rate ;
2887 base_tax_amt_tab(row_count) := base_tax_amt_tab(row_count) * p_currency_conversion_rate ;
2888 END IF;
2889 */
2890 END IF;
2891 ------------------------------------------------
2892 --Changed by qiong.liu for bug12717416 end
2893
2894 row_count := row_count + 1;
2895 END LOOP;
2896 CLOSE c_po_tax_cur; /*rchandan for bug#6030615*/
2897 row_count := row_count - 1;
2898 END IF; /* OF if ISOPEN*/
2899
2900 IF ln_curflag = 1 THEN--Add by Kevin Cheng for bug 6853787 Mar 5, 2008
2901 IF ln_vat_assess_value<>v_precedence_0
2902 THEN
2903 ln_vat_assessable_value:=ln_vat_assess_value;
2904 ELSE
2905 ln_vat_assessable_value:=1;
2906 END IF; --End p_vat_assessable_value<>p_tax_amount
2907
2908 IF v_assessable_value<>v_precedence_0
2909 THEN
2910 ln_assessable_value:=v_assessable_value;
2911 ELSE
2912 ln_assessable_value:=1;
2913 END IF; --End p_assessable_value<>p_tax_amount
2914 --Add by Xiao for GST refer to bug#10043656 on 14-Sep-2010, begin
2915 -------------------------------------------------------------
2916 /* IF ln_gst_assess_value<>v_precedence_0
2917 THEN
2918 ln_gst_assessable_value:=ln_gst_assess_value;
2919 ELSE
2920 ln_gst_assessable_value:=1;
2921 END IF; */
2922 -------------------------------------------------------------
2923 --Add by Xiao for GST refer to bug#10043656 on 14-Sep-2010, begin
2924 --Added by walton for inclusive tax computation
2925 -----------------------------------------------
2926 -------loop1-------------------------
2927 FOR I IN 1..row_count LOOP
2928 IF end_date_tab(I) <> 0 THEN
2929 IF tax_type_tab(I) = 1 THEN
2930 IF ln_assessable_value =1
2931 THEN
2932 bsln_amt:=1;
2933 ln_bsln_amt_nr :=0;
2934 ELSE
2935 bsln_amt :=0;
2936 ln_bsln_amt_nr :=ln_assessable_value;
2937 END IF;
2938 ELSIF tax_type_tab(I) = 4 THEN
2939 IF ln_vat_assessable_value =1
2940 THEN
2941 bsln_amt:=1;
2942 ln_bsln_amt_nr :=0;
2943 ELSE
2944 bsln_amt :=0;
2945 ln_bsln_amt_nr :=ln_vat_assessable_value;
2946 END IF;
2947 ELSIF tax_type_tab(I) = 6 THEN
2948 bsln_amt:=0;
2949 ln_bsln_amt_nr :=0;
2950 --Add by Xiao for GST refer to bug#10043656 on 14-Sep-2010, begin
2951 ----------------------------------------------------------------------------
2952 /* ELSIF tax_type_tab(I) = 7 THEN
2953 IF ln_gst_assessable_value =1
2954 THEN
2955 bsln_amt:=1;
2956 ln_bsln_amt_nr :=0;
2957 ELSE
2958 bsln_amt :=0;
2959 ln_bsln_amt_nr :=ln_gst_assessable_value;
2960 END IF; */
2961 ----------------------------------------------------------------------------
2962 --Add by Xiao for GST refer to bug#10043656 on 14-Sep-2010, end
2963 ELSE
2964 bsln_amt:=1;
2965 ln_bsln_amt_nr :=0;
2966 END IF;
2967
2968 IF tax_rate_tab(I) <> 0 THEN
2969 /* Commented out by Shyan for bug 10358786 on 28-Jan-2011 , Begin
2970 IF P1(I) < I AND P1(I) NOT IN (-1,0) THEN
2971 vamt := vamt + NVL(tax_amt_tab(P1(I)),0);
2972 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P1(I)),0);
2973 ELSIF P1(I) = 0 THEN
2974 vamt := vamt + bsln_amt;
2975 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
2976 END IF;
2977 IF p2(I) < I AND p2(I) NOT IN (-1,0) THEN
2978 vamt := vamt + NVL(tax_amt_tab(p2(I)),0);
2979 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P2(I)),0);
2980 ELSIF p2(I) = 0 THEN
2981 vamt := vamt + bsln_amt;
2982 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
2983 END IF;
2984 IF p3(I) < I AND p3(I) NOT IN (-1,0) THEN
2985 vamt := vamt + NVL(tax_amt_tab(p3(I)),0);
2986 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P3(I)),0);
2987 ELSIF p3(I) = 0 THEN
2988 vamt := vamt + bsln_amt;
2989 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
2990 END IF;
2991 IF p4(I) < I AND p4(I) NOT IN (-1,0) THEN
2992 vamt := vamt + NVL(tax_amt_tab(p4(I)),0);
2993 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P4(I)),0);
2994 ELSIF p4(I) = 0 THEN
2995 vamt := vamt + bsln_amt;
2996 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
2997 END IF;
2998 IF p5(I) < I AND p5(I) NOT IN (-1,0) THEN
2999 vamt := vamt + NVL(tax_amt_tab(p5(I)),0);
3000 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P5(I)),0);
3001 ELSIF p5(I) = 0 THEN
3002 vamt := vamt + bsln_amt;
3003 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
3004 END IF;
3005 IF P6(I) < I AND P6(I) NOT IN (-1,0) THEN
3006 vamt := vamt + NVL(tax_amt_tab(P6(I)),0);
3007 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P6(I)),0);
3008 ELSIF P6(I) = 0 THEN
3009 vamt := vamt + bsln_amt;
3010 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
3011 END IF;
3012 IF p7(I) < I AND p7(I) NOT IN (-1,0) THEN
3013 vamt := vamt + NVL(tax_amt_tab(p7(I)),0);
3014 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P7(I)),0);
3015 ELSIF p7(I) = 0 THEN
3016 vamt := vamt + bsln_amt;
3017 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
3018 END IF;
3019 IF p8(I) < I AND p8(I) NOT IN (-1,0) THEN
3020 vamt := vamt + NVL(tax_amt_tab(p8(I)),0);
3021 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P8(I)),0);
3022 ELSIF p8(I) = 0 THEN
3023 vamt := vamt + bsln_amt;
3024 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
3025 END IF;
3026 IF p9(I) < I AND p9(I) NOT IN (-1,0) THEN
3027 vamt := vamt + NVL(tax_amt_tab(p9(I)),0);
3028 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P9(I)),0);
3029 ELSIF p9(I) = 0 THEN
3030 vamt := vamt + bsln_amt;
3031 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
3032 END IF;
3033 IF p10(I) < I AND p10(I) NOT IN (-1,0) THEN
3034 vamt := vamt + NVL(tax_amt_tab(p10(I)),0);
3035 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P10(I)),0);
3036 ELSIF p10(I) = 0 THEN
3037 vamt := vamt + bsln_amt;
3038 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
3039 END IF;
3040 Commented out by Shyan for bug 10358786 on 28-Jan-2011, End*/
3041 --Added by Shujuan for 10358786 on 28-Jan-2011, Begin
3042 -------------------------------------------------------
3043 lt_tax_amt_non_rate_tab(I):=0;
3044 IF P1(I) < I AND P1(I) NOT IN (-1, 0) THEN
3045 IF lt_inclusive_tax_tab(P1(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P1(I)) IS NOT NULL then
3046 vamt := vamt + NVL(tax_amt_tab(P1(I)), 0);
3047 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P1(I)), 0);
3048 END IF;
3049
3050 ELSIF P1(I) = 0 THEN
3051 IF lt_inclusive_tax_tab(I) = 'Y' then
3052 vamt := vamt + bsln_amt;
3053 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr;
3054 ELSE
3055 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + NVL(v_precedence_0,0);
3056
3057 END IF;
3058 END IF;
3059 IF p2(I) < I AND p2(I) NOT IN (-1, 0) THEN
3060 IF lt_inclusive_tax_tab(P2(I)) = 'Y' OR
3061 lt_tax_amt_rate_tax_tab(P2(I)) IS NOT NULL then
3062 vamt := vamt + NVL(tax_amt_tab(p2(I)), 0);
3063 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P2(I)), 0);
3064 END IF;
3065
3066 ELSIF p2(I) = 0 THEN
3067 IF lt_inclusive_tax_tab(I) = 'Y' then
3068 vamt := vamt + bsln_amt;
3069 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr;
3070 ELSE
3071 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + NVL(v_precedence_0,0);
3072 END IF;
3073
3074 END IF;
3075 IF p3(I) < I AND p3(I) NOT IN (-1, 0) THEN
3076 IF lt_inclusive_tax_tab(P3(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P3(I)) IS NOT NULL then
3077 vamt := vamt + NVL(tax_amt_tab(p3(I)), 0);
3078 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P3(I)), 0);
3079 END IF;
3080
3081 ELSIF p3(I) = 0 THEN
3082 IF lt_inclusive_tax_tab(I) = 'Y' then
3083 vamt := vamt + bsln_amt;
3084 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr;
3085 ELSE
3086 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + NVL(v_precedence_0,0);
3087 END IF;
3088 END IF;
3089
3090 IF p4(I) < I AND p4(I) NOT IN (-1, 0) THEN
3091 IF lt_inclusive_tax_tab(P4(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P4(I)) IS NOT NULL then
3092 vamt := vamt + NVL(tax_amt_tab(p4(I)), 0);
3093 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P4(I)), 0);
3094 END IF;
3095 ELSIF p4(I) = 0 THEN
3096 IF lt_inclusive_tax_tab(I) = 'Y' then
3097 vamt := vamt + bsln_amt;
3098 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr;
3099 ELSE
3100 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + NVL(v_precedence_0,0);
3101 END IF;
3102 END IF;
3103
3104 IF p5(I) < I AND p5(I) NOT IN (-1, 0) THEN
3105 IF lt_inclusive_tax_tab(P5(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P5(I)) IS NOT NULL then
3106 vamt := vamt + NVL(tax_amt_tab(p5(I)), 0);
3107 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P5(I)), 0);
3108 END IF;
3109 ELSIF p5(I) = 0 THEN
3110 IF lt_inclusive_tax_tab(I) = 'Y' then
3111 vamt := vamt + bsln_amt;
3112 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr;
3113 ELSE
3114 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + NVL(v_precedence_0,0);
3115 END IF;
3116 END IF;
3117 IF p6(I) < I AND p6(I) NOT IN (-1, 0) THEN
3118 IF lt_inclusive_tax_tab(P6(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P6(I)) IS NOT NULL then
3119 vamt := vamt + NVL(tax_amt_tab(p6(I)), 0);
3120 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P6(I)), 0);
3121 END IF;
3122
3123 ELSIF p6(I) = 0 THEN
3124 IF lt_inclusive_tax_tab(I) = 'Y' then
3125 vamt := vamt + bsln_amt;
3126 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr;
3127 ELSE
3128 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + NVL(v_precedence_0,0);
3129 END IF;
3130 END IF;
3131
3132 IF p7(I) < I AND p7(I) NOT IN (-1, 0) THEN
3133 IF lt_inclusive_tax_tab(P7(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P7(I)) IS NOT NULL then
3134 vamt := vamt + NVL(tax_amt_tab(p7(I)), 0);
3135 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P7(I)), 0);
3136 END IF;
3137
3138 ELSIF p7(I) = 0 THEN
3139 IF lt_inclusive_tax_tab(I) = 'Y' then
3140 vamt := vamt + bsln_amt;
3141 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr;
3142 ELSE
3143 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + NVL(v_precedence_0,0);
3144 END IF;
3145 END IF;
3146
3147 IF p8(I) < I AND p8(I) NOT IN (-1, 0) THEN
3148 IF lt_inclusive_tax_tab(P4(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P8(I)) IS NOT NULL then
3149 vamt := vamt + NVL(tax_amt_tab(p8(I)), 0);
3150 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P8(I)), 0);
3151 END IF;
3152
3153 ELSIF p8(I) = 0 THEN
3154 IF lt_inclusive_tax_tab(I) = 'Y' then
3155 vamt := vamt + bsln_amt;
3156 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr;
3157 ELSE
3158 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + NVL(v_precedence_0,0);
3159 END IF;
3160 END IF;
3161
3162 IF p9(I) < I AND p9(I) NOT IN (-1, 0) THEN
3163 IF lt_inclusive_tax_tab(P9(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P9(I)) IS NOT NULL then
3164 vamt := vamt + NVL(tax_amt_tab(p4(I)), 0);
3165 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P9(I)), 0);
3166 END IF;
3167
3168 ELSIF p9(I) = 0 THEN
3169 IF lt_inclusive_tax_tab(I) = 'Y' then
3170 vamt := vamt + bsln_amt;
3171 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr;
3172 ELSE
3173 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + NVL(v_precedence_0,0);
3174 END IF;
3175 END IF;
3176
3177 IF p10(I) < I AND p10(I) NOT IN (-1, 0) THEN
3178 IF lt_inclusive_tax_tab(P10(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P10(I)) IS NOT NULL then
3179 vamt := vamt + NVL(tax_amt_tab(p10(I)), 0);
3180 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P10(I)), 0);
3181 END IF;
3182
3183 ELSIF p10(I) = 0 THEN
3184 IF lt_inclusive_tax_tab(I) = 'Y' then
3185 vamt := vamt + bsln_amt;
3186 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr;
3187 ELSE
3188 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + NVL(v_precedence_0,0);
3189 END IF;
3190 END IF;
3191 -------------------------------------------------------
3192 --Added by Shujuan for 10358786 on 28-Jan-2011, End
3193 v_tax_amt := v_tax_amt + (vamt * (tax_rate_tab(I)/100));
3194 ln_tax_amt_nr:=ln_tax_amt_nr+(ln_vamt_nr*(tax_rate_tab(I)/100));
3195 base_tax_amt_tab(I) := vamt;
3196 tax_amt_tab(I) := NVL(tax_amt_tab(I),0) + v_tax_amt;
3197 lt_tax_amt_non_rate_tab(I):=NVL(lt_tax_amt_non_rate_tab(I),0)+ln_tax_amt_nr;
3198 --Commented out by Shyan for bug 10358786 on 28-Jan-2011 , Begin
3199 -------------------------------------------------------------------
3200 --lt_tax_amt_rate_tax_tab(I):= tax_amt_tab(I);
3201 -------------------------------------------------------------------
3202 -- Commented out by Shyan for bug 10358786 on 28-Jan-2011 , End
3203
3204 -- Added by Shyan for bug 10358786 on 28-Jan-2011 , Begin
3205 -------------------------------------------------------------------
3206 IF lt_inclusive_tax_tab(I) = 'Y' then
3207 lt_tax_amt_rate_tax_tab(I) := tax_amt_tab(I);
3208 END IF;
3209 -------------------------------------------------------------------
3210 -- Added by Shyan for bug 10358786 on 28-Jan-2011, End
3211 vamt := 0;
3212 v_tax_amt := 0;
3213 ln_tax_amt_nr:=0; --added by walton for inclusive tax
3214 ln_vamt_nr:=0; --added by walton for inclusive tax
3215 END IF;
3216
3217 /* Commented below ELSE for bug 14696186 */
3218 /* ELSE
3219 --Added by qiong.liu for bug12717416 begin
3220 ------------------------------------------
3221 IF lt_adhoc_tax_tab(I)='N' THEN
3222
3223 tax_amt_tab(I) := 0;
3224 base_tax_amt_tab(I) := 0;
3225 END IF; */
3226 --------------------------------------------
3227 --Added by qiong.liu for bug12717416 end
3228 END IF;
3229
3230 END LOOP;
3231 ----------------loop2-------------------------------------
3232 FOR I IN 1..row_count LOOP
3233 IF end_date_tab( I ) <> 0 THEN
3234 IF tax_rate_tab(I) <> 0 THEN
3235 IF P1(I) > I THEN
3236 vamt := vamt + NVL(tax_amt_tab(P1(I)),0);
3237 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P1(I)),0);
3238 END IF;
3239 IF p2(I) > I THEN
3240 vamt := vamt + NVL(tax_amt_tab(p2(I)),0);
3241 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P2(I)),0);
3242 END IF;
3243 IF p3(I) > I THEN
3244 vamt := vamt + NVL(tax_amt_tab(p3(I)),0);
3245 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P3(I)),0);
3246 END IF;
3247 IF p4(I) > I THEN
3248 vamt := vamt + NVL(tax_amt_tab(p4(I)),0);
3249 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P4(I)),0);
3250 END IF;
3251 IF p5(I) > I THEN
3252 vamt := vamt + NVL(tax_amt_tab(p5(I)),0);
3253 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P5(I)),0);
3254 END IF;
3255 IF P6(I) > I THEN
3256 vamt := vamt + NVL(tax_amt_tab(P6(I)),0);
3257 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P6(I)),0);
3258 END IF;
3259 IF p7(I) > I THEN
3260 vamt := vamt + NVL(tax_amt_tab(p7(I)),0);
3261 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P7(I)),0);
3262 END IF;
3263 IF p8(I) > I THEN
3264 vamt := vamt + NVL(tax_amt_tab(p8(I)),0);
3265 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P8(I)),0);
3266 END IF;
3267 IF p9(I) > I THEN
3268 vamt := vamt + NVL(tax_amt_tab(p9(I)),0);
3269 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P9(I)),0);
3270 END IF;
3271 IF p10(I) > I THEN
3272 vamt := vamt + NVL(tax_amt_tab(p10(I)),0);
3273 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P10(I)),0);
3274 END IF;
3275 base_tax_amt_tab(I) := vamt;
3276 v_tax_amt := v_tax_amt + (vamt * (tax_rate_tab(I)/100));
3277 ln_tax_amt_nr:=ln_tax_amt_nr+(ln_vamt_nr * (tax_rate_tab(I)/100));
3278 IF vamt <> 0 THEN
3279 base_tax_amt_tab(I) := base_tax_amt_tab(I) + vamt;
3280 END IF;
3281 --Added by qiong.liu for bug12717416 2011.07.07 begin
3282 ------------------------------------------------------
3283 IF lt_adhoc_tax_tab(I) = 'N' THEN
3284 tax_amt_tab(I) := NVL(tax_amt_tab(I),0) + v_tax_amt;
3285 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
3286 lt_tax_amt_rate_tax_tab(I):= tax_amt_tab(I); --added by walton for inclusive tax
3287 tax_amt_tab(I) := round(tax_amt_tab(I) ,round_factor_tab(I)); --added by csahoo for bug#6077133
3288 END IF;
3289 -------------------------------------------------------
3290 --Added by qiong.liu for bug12717416 2011.07.07 end
3291 vamt := 0;
3292 v_tax_amt := 0;
3293 ln_vamt_nr :=0; --added by walton for inclusive tax
3294 ln_tax_amt_nr :=0; --added by walton for inclusive tax
3295 END IF; --End of tax_rate_tab(I) <> 0
3296 ELSE
3297 base_tax_amt_tab(I) := vamt;
3298 tax_amt_tab(I) := 0;
3299 END IF; --End of end_date_tab( I ) <> 0
3300 END LOOP;
3301 ----------------loop3-------------------------------
3302 FOR counter IN 1 .. max_iter LOOP
3303 vamt := 0;
3304 v_tax_amt := 0;
3305 ln_vamt_nr:= 0; --added by walton for inclusive tax
3306 ln_tax_amt_nr:=0; --added by walton for inclusive tax
3307
3308 FOR i IN 1 .. row_count LOOP
3309
3310 /*
3311 || Modified by aiyer for the fwd porting bug 4691616.
3312 || The following if clause will restrict the taxes whose tax_rate is null
3313 || i.e when tax_rate is null, tax_rate_tab(i) is 0.
3314 */
3315 IF ( tax_rate_tab( i ) <> 0 OR
3316 tax_rate_zero_tab(I) = -9999
3317 ) AND
3318 end_date_tab( I ) <> 0
3319 THEN
3320
3321 IF tax_type_tab( I ) = 1 THEN
3322 IF ln_assessable_value =1
3323 THEN
3324 v_amt:=1;
3325 ln_bsln_amt_nr :=0;
3326 ELSE
3327 v_amt :=0;
3328 ln_bsln_amt_nr :=ln_assessable_value;
3329 END IF;
3330 ELSIF tax_type_tab(I) = 4 THEN
3331 IF ln_vat_assessable_value =1
3332 THEN
3333 v_amt:=1;
3334 ln_bsln_amt_nr :=0;
3335 ELSE
3336 v_amt :=0;
3337 ln_bsln_amt_nr :=ln_vat_assessable_value;
3338 END IF;
3339 ELSIF tax_type_tab(I) = 6 THEN
3340 v_amt:=0;
3341 ln_bsln_amt_nr :=0;
3342 --Add by Xiao for GST refer to bug#10043656 on 14-Sep-2010, begin
3343 -----------------------------------------------------------------
3344 /* ELSIF tax_type_tab(I) = 7 THEN
3345 IF ln_gst_assessable_value =1
3346 THEN
3347 v_amt:=1;
3348 ln_bsln_amt_nr :=0;
3349 ELSE
3350 v_amt :=0;
3351 ln_bsln_amt_nr :=ln_gst_assessable_value;
3352 END IF; */
3353
3354 -----------------------------------------------------------------
3355 --Add by Xiao for GST refer to bug#10043656 on 14-Sep-2010, end
3356 ELSE
3357 IF ln_assessable_value IN ( 0, -1 ) OR tax_type_tab( I ) <> 1 THEN
3358 /* v_amt := NVL(v_precedence_0,0);*/
3359 v_amt:=1; --Added by walton for inclusive tax
3360 ln_bsln_amt_nr :=0; --Added by walton for inclusive tax
3361 ELSIF ln_vat_assessable_value IN ( 0, -1 ) OR tax_type_tab( I ) <> 4 THEN
3362 /* v_amt := NVL(v_precedence_0,0);*/
3363 v_amt:=1; --Added by walton for inclusive tax
3364 ln_bsln_amt_nr :=0; --Added by walton for inclusive tax
3365 --Add by Xiao for GST refer to bug#10043656 on 14-Sep-2010, begin
3366 -----------------------------------------------------------------
3367 /* ELSIF ln_gst_assessable_value IN ( 0, -1 ) OR tax_type_tab( I ) <> 7 THEN
3368 v_amt:=1;
3369 ln_bsln_amt_nr :=0; */
3370 -----------------------------------------------------------------
3371 --Add by Xiao for GST refer to bug#10043656 on 14-Sep-2010, end
3372 END IF;
3373 END IF; --End of tax_type_tab( I ) = 1
3374 /* Commented out by Shyan for bug 10358786 on 28-Jan-2011 , Begin
3375 --------------------------------------------------------------------
3376 IF P1( i ) <> -1 THEN
3377 IF P1( i ) <> 0 THEN
3378 vamt := vamt + tax_amt_tab( P1( I ) );
3379 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P1(I)),0);
3380 ELSIF P1(i) = 0 THEN
3381 vamt := vamt + v_amt;
3382 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
3383 END IF;
3384 END IF;
3385
3386 IF p2( i ) <> -1 THEN
3387 IF p2( i ) <> 0 THEN
3388 vamt := vamt + tax_amt_tab( p2( I ) );
3389 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P2(I)),0);
3390 ELSIF p2(i) = 0 THEN
3391 vamt := vamt + v_amt;
3392 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
3393 END IF;
3394 END IF;
3395 IF p3( i ) <> -1 THEN
3396 IF p3( i ) <> 0 THEN
3397 vamt := vamt + tax_amt_tab( p3( I ) );
3398 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P3(I)),0);
3399 ELSIF p3(i) = 0 THEN
3400 vamt := vamt + v_amt;
3401 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
3402 END IF;
3403 END IF;
3404
3405 IF p4( i ) <> -1 THEN
3406 IF p4( i ) <> 0 THEN
3407 vamt := vamt + tax_amt_tab( p4( i ) );
3408 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P4(I)),0);
3409 ELSIF p4(i) = 0 THEN
3410 vamt := vamt + v_amt;
3411 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
3412 END IF;
3413 END IF;
3414
3415 IF p5( i ) <> -1 THEN
3416 IF p5( i ) <> 0 THEN
3417 vamt := vamt + tax_amt_tab( p5( i ) );
3418 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P5(I)),0);
3419 ELSIF p5(i) = 0 THEN
3420 vamt := vamt + v_amt;
3421 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
3422 END IF;
3423 END IF;
3424
3425 IF P6( i ) <> -1 THEN
3426 IF P6( i ) <> 0 THEN
3427 vamt := vamt + tax_amt_tab( P6( I ) );
3428 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P6(I)),0);
3429 ELSIF P6(i) = 0 THEN
3430 vamt := vamt + v_amt;
3431 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
3432 END IF;
3433 END IF;
3434
3435 IF p7( i ) <> -1 THEN
3436 IF p7( i ) <> 0 THEN
3437 vamt := vamt + tax_amt_tab( p7( I ) );
3438 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P7(I)),0);
3439 ELSIF p7(i) = 0 THEN
3440 vamt := vamt + v_amt;
3441 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
3442 END IF;
3443 END IF;
3444 IF p8( i ) <> -1 THEN
3445 IF p8( i ) <> 0 THEN
3446 vamt := vamt + tax_amt_tab( p8( I ) );
3447 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P8(I)),0);
3448 ELSIF p8(i) = 0 THEN
3449 vamt := vamt + v_amt;
3450 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
3451 END IF;
3452 END IF;
3453
3454 IF p9( i ) <> -1 THEN
3455 IF p9( i ) <> 0 THEN
3456 vamt := vamt + tax_amt_tab( p9( i ) );
3457 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P9(I)),0);
3458 ELSIF p9(i) = 0 THEN
3459 vamt := vamt + v_amt;
3460 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
3461 END IF;
3462 END IF;
3463
3464 IF p10( i ) <> -1 THEN
3465 IF p10( i ) <> 0 THEN
3466 vamt := vamt + tax_amt_tab( p10( i ) );
3467 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P10(I)),0);
3468 ELSIF p10(i) = 0 THEN
3469 vamt := vamt + v_amt;
3470 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
3471 END IF;
3472 END IF;
3473 ----------------------------------------------------------------
3474 Commented out by Shyan for bug 10358786 on 28-Jan-2011, End */
3475
3476 -- Added by shujuan for bug 10358786 on 28-Jan-2011, Begin
3477 -----------------------------------------------------------
3478 --Qiong fix bug12717416 2011.07.07 begin
3479 --for P1 to P10 changed :
3480 --And IF lt_tax_amt_rate_tax_tab(P1(I)) IS NOT NULL AND lt_adhoc_tax_tab(P1(I))='N' THEN
3481 --Changed logic of ELSIF P1(i) = 0 THEN
3482 -------------------------------------------------------------------------------------------------------------------
3483 ln_vamt_nr :=0;
3484 lt_tax_amt_non_rate_tab(I):=0;
3485 IF P1(i) <> -1 THEN
3486 IF P1(i) <> 0 THEN
3487 IF lt_inclusive_tax_tab(P1(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P1(I)) IS NOT NULL then
3488 IF lt_tax_amt_rate_tax_tab(P1(I)) IS NOT NULL AND lt_adhoc_tax_tab(P1(I))='N' THEN
3489 vamt:= vamt + tax_amt_tab(P1(I));
3490 END IF;
3491 END IF;
3492 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P1(I)), 0);
3493 ELSIF P1(i) = 0 THEN
3494 IF lt_inclusive_tax_tab(I) = 'Y' then
3495 vamt := vamt + v_amt;
3496 END IF;
3497 IF ln_bsln_amt_nr=0 AND lt_inclusive_tax_tab(I) = 'N' THEN
3498 ln_vamt_nr := NVL(v_precedence_0,0);
3499 ELSE
3500 ln_vamt_nr := ln_bsln_amt_nr;
3501 END IF;
3502 END IF;
3503 END IF;
3504
3505 IF p2(i) <> -1 THEN
3506 IF p2(i) <> 0 THEN
3507 IF lt_inclusive_tax_tab(P2(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P2(I)) IS NOT NULL then
3508 IF lt_tax_amt_rate_tax_tab(P2(I)) IS NOT NULL AND lt_adhoc_tax_tab(P2(I))='N' THEN
3509 vamt := vamt + tax_amt_tab(p2(I));
3510 END IF;
3511 END IF;
3512 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P2(I)), 0);
3513 ELSIF p2(i) = 0 THEN
3514 IF lt_inclusive_tax_tab(I) = 'Y' then
3515 vamt := vamt + v_amt;
3516 END IF;
3517 IF ln_bsln_amt_nr=0 AND lt_inclusive_tax_tab(I) = 'N' THEN
3518 ln_vamt_nr := NVL(v_precedence_0,0);
3519 ELSE
3520 ln_vamt_nr := ln_bsln_amt_nr;
3521 END IF;
3522 END IF;
3523 END IF;
3524
3525 IF p3(i) <> -1 THEN
3526 IF p3(i) <> 0 THEN
3527 IF lt_inclusive_tax_tab(P3(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P3(I)) IS NOT NULL then
3528 IF lt_tax_amt_rate_tax_tab(P3(I)) IS NOT NULL AND lt_adhoc_tax_tab(P3(I))='N' THEN
3529 vamt := vamt + tax_amt_tab(p3(I));
3530 END IF;
3531 END IF;
3532 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P3(I)), 0);
3533 ELSIF p3(i) = 0 THEN
3534 IF lt_inclusive_tax_tab(I) = 'Y' then
3535 vamt := vamt + v_amt;
3536 END IF;
3537 IF ln_bsln_amt_nr=0 AND lt_inclusive_tax_tab(I) = 'N' THEN
3538 ln_vamt_nr := NVL(v_precedence_0,0);
3539 ELSE
3540 ln_vamt_nr := ln_bsln_amt_nr;
3541 END IF;
3542 END IF;
3543 END IF;
3544
3545 IF p4(i) <> -1 THEN
3546 IF p4(i) <> 0 THEN
3547 IF lt_inclusive_tax_tab(P4(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P4(I)) IS NOT NULL then
3548 IF lt_tax_amt_rate_tax_tab(P4(I)) IS NOT NULL AND lt_adhoc_tax_tab(P4(I))='N' THEN
3549 vamt := vamt + tax_amt_tab(p4(I));
3550 END IF;
3551 END IF;
3552 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P4(I)), 0);
3553 ELSIF p4(i) = 0 THEN
3554 IF lt_inclusive_tax_tab(I) = 'Y' then
3555 vamt := vamt + v_amt;
3556 END IF;
3557 IF ln_bsln_amt_nr=0 AND lt_inclusive_tax_tab(I) = 'N' THEN
3558 ln_vamt_nr := NVL(v_precedence_0,0);
3559 ELSE
3560 ln_vamt_nr := ln_bsln_amt_nr;
3561 END IF;
3562 END IF;
3563 END IF;
3564
3565 IF p5(i) <> -1 THEN
3566 IF p5(i) <> 0 THEN
3567 IF lt_inclusive_tax_tab(P5(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P5(I)) IS NOT NULL then
3568 IF lt_tax_amt_rate_tax_tab(P5(I)) IS NOT NULL AND lt_adhoc_tax_tab(P5(I))='N' THEN
3569 vamt := vamt + tax_amt_tab(p5(I));
3570 END IF;
3571 END IF;
3572 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P5(I)), 0);
3573 ELSIF p5(i) = 0 THEN
3574 IF lt_inclusive_tax_tab(I) = 'Y' then
3575 vamt := vamt + v_amt;
3576 END IF;
3577 IF ln_bsln_amt_nr=0 AND lt_inclusive_tax_tab(I) = 'N' THEN
3578 ln_vamt_nr := NVL(v_precedence_0,0);
3579 ELSE
3580 ln_vamt_nr := ln_bsln_amt_nr;
3581 END IF;
3582 END IF;
3583 END IF;
3584
3585 IF p6(i) <> -1 THEN
3586 IF p6(i) <> 0 THEN
3587 IF lt_inclusive_tax_tab(P6(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P6(I)) IS NOT NULL then
3588 IF lt_tax_amt_rate_tax_tab(P6(I)) IS NOT NULL AND lt_adhoc_tax_tab(P6(I))='N' THEN
3589 vamt := vamt + tax_amt_tab(p6(I));
3590 END IF;
3591 END IF;
3592 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P6(I)), 0);
3593 ELSIF p6(i) = 0 THEN
3594 IF lt_inclusive_tax_tab(I) = 'Y' then
3595 vamt := vamt + v_amt;
3596 END IF;
3597 IF ln_bsln_amt_nr=0 AND lt_inclusive_tax_tab(I) = 'N' THEN
3598 ln_vamt_nr := NVL(v_precedence_0,0);
3599 ELSE
3600 ln_vamt_nr := ln_bsln_amt_nr;
3601 END IF;
3602 END IF;
3603 END IF;
3604 IF p7(i) <> -1 THEN
3605 IF p7(i) <> 0 THEN
3606 IF lt_inclusive_tax_tab(P7(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P7(I)) IS NOT NULL then
3607 IF lt_tax_amt_rate_tax_tab(P7(I)) IS NOT NULL AND lt_adhoc_tax_tab(P7(I))='N' THEN
3608 vamt := vamt + tax_amt_tab(p7(I));
3609 END IF;
3610 END IF;
3611 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P7(I)), 0);
3612 ELSIF p7(i) = 0 THEN
3613 IF lt_inclusive_tax_tab(I) = 'Y' then
3614 vamt := vamt + v_amt;
3615 END IF;
3616 IF ln_bsln_amt_nr=0 AND lt_inclusive_tax_tab(I) = 'N' THEN
3617 ln_vamt_nr := NVL(v_precedence_0,0);
3618 ELSE
3619 ln_vamt_nr := ln_bsln_amt_nr;
3620 END IF;
3621 END IF;
3622 END IF;
3623
3624 IF p8(i) <> -1 THEN
3625 IF p8(i) <> 0 THEN
3626 IF lt_inclusive_tax_tab(P8(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P8(I)) IS NOT NULL then
3627 IF lt_tax_amt_rate_tax_tab(P8(I)) IS NOT NULL AND lt_adhoc_tax_tab(P8(I))='N' THEN
3628 vamt := vamt + tax_amt_tab(p8(I));
3629 END IF;
3630 END IF;
3631 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P8(I)), 0);
3632 ELSIF p8(i) = 0 THEN
3633 IF lt_inclusive_tax_tab(I) = 'Y' then
3634 vamt := vamt + v_amt;
3635 END IF;
3636 IF ln_bsln_amt_nr=0 AND lt_inclusive_tax_tab(I) = 'N' THEN
3637 ln_vamt_nr := NVL(v_precedence_0,0);
3638 ELSE
3639 ln_vamt_nr := ln_bsln_amt_nr;
3640 END IF;
3641 END IF;
3642 END IF;
3643
3644 IF p9(i) <> -1 THEN
3645 IF p9(i) <> 0 THEN
3646 IF lt_inclusive_tax_tab(P9(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P9(I)) IS NOT NULL then
3647 IF lt_tax_amt_rate_tax_tab(P9(I)) IS NOT NULL AND lt_adhoc_tax_tab(P9(I))='N' THEN
3648 vamt := vamt + tax_amt_tab(p9(I));
3649 END IF;
3650 END IF;
3651 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P9(I)), 0);
3652 ELSIF p9(i) = 0 THEN
3653 IF lt_inclusive_tax_tab(I) = 'Y' then
3654 vamt := vamt + v_amt;
3655 END IF;
3656 IF ln_bsln_amt_nr=0 AND lt_inclusive_tax_tab(I) = 'N' THEN
3657 ln_vamt_nr := NVL(v_precedence_0,0);
3658 ELSE
3659 ln_vamt_nr := ln_bsln_amt_nr;
3660 END IF;
3661 END IF;
3662 END IF;
3663
3664 IF p10(i) <> -1 THEN
3665 IF p10(i) <> 0 THEN
3666 IF lt_inclusive_tax_tab(P10(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P10(I)) IS NOT NULL then
3667 IF lt_tax_amt_rate_tax_tab(P10(I)) IS NOT NULL AND lt_adhoc_tax_tab(P10(I))='N' THEN
3668 vamt := vamt + tax_amt_tab(p10(I));
3669 END IF;
3670 END IF;
3671 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P10(I)), 0);
3672 ELSIF p10(i) = 0 THEN
3673 IF lt_inclusive_tax_tab(I) = 'Y' then
3674 vamt := vamt + v_amt;
3675 END IF;
3676 IF ln_bsln_amt_nr=0 AND lt_inclusive_tax_tab(I) = 'N' THEN
3677 ln_vamt_nr := NVL(v_precedence_0,0);
3678 ELSE
3679 ln_vamt_nr := ln_bsln_amt_nr;
3680 END IF;
3681 END IF;
3682 END IF;
3683 -----------------------------------------------------------
3684 -- Added by shujuan for bug 10358786 on 28-Jan-2011, End
3685 base_tax_amt_tab(I) := vamt;
3686 tax_target_tab(I) := vamt;
3687 v_func_tax_amt := v_tax_amt + ( vamt * ( tax_rate_tab( i )/100));
3688 v_tax_amt := v_tax_amt + ( vamt * ( tax_rate_tab( i )/100));
3689 --Added by qiong.liu for bug12717416 on 2011.07.07 begin
3690 --------------------------------------------------------
3691 ln_tax_amt_nr := ln_vamt_nr * ( tax_rate_tab( i )/100);
3692 lt_tax_amt_non_rate_tab(I) := ln_tax_amt_nr;
3693 --------------------------------------------------------
3694 --Added by qiong.liu for bug12717416 on 2011.07.07 end
3695 ELSIF tax_rate_tab(I) = 0 THEN
3696 base_tax_amt_tab(I) := tax_amt_tab(i);
3697 v_tax_amt := tax_amt_tab( i );
3698 tax_target_tab(I) := v_tax_amt;
3699 ELSIF end_date_tab( I ) = 0 THEN
3700 tax_amt_tab(I) := 0;
3701 base_tax_amt_tab(I) := 0;
3702 tax_target_tab(I) := 0;
3703 END IF;
3704
3705 tax_amt_tab( I ) := NVL( v_tax_amt, 0 );
3706
3707 -- Commented out by Shyan for bug 10358786 on 28-Jan-2011 , Begin
3708 -------------------------------------------------------------------
3709 -- lt_tax_amt_rate_tax_tab(I) := tax_amt_tab(I);
3710 -------------------------------------------------------------------
3711 -- Commented out by Shyan for bug 10358786 on 28-Jan-2011 , End
3712
3713 -- Added by Shyan for bug 10358786 on 28-Jan-2011 , Begin
3714 -------------------------------------------------------------------
3715 --Add lt_adhoc_tax_tab by qiong for bug12717416 begin
3716 ------------------------------------------------------------
3717 IF lt_inclusive_tax_tab(I) = 'Y' and lt_adhoc_tax_tab(I)='N' THEN
3718 lt_tax_amt_rate_tax_tab(I) := tax_amt_tab(I);
3719 END IF;
3720 -------------------------------------------------------------
3721 --Add lt_adhoc_tax_tab by qiong for bug12717416 end
3722 -------------------------------------------------------------------
3723 -- Added by Shyan for bug 10358786 on 28-Jan-2011 , End
3724 func_tax_amt_tab(I) := NVL(v_func_tax_amt,0);
3725
3726 IF counter = max_iter THEN
3727 IF end_date_tab(I) = 0 THEN
3728 tax_amt_tab( i ) := 0;
3729 func_tax_amt_tab(i) := 0;
3730 END IF;
3731 END IF;
3732
3733 vamt := 0;
3734 v_amt := 0;
3735 v_tax_amt := 0;
3736 v_func_tax_amt := 0;
3737 ln_vamt_nr :=0; --added by walton for inclusive tax
3738 ln_tax_amt_nr:=0; --added by walton for inclusive tax
3739
3740 END LOOP;
3741
3742 END LOOP;
3743
3744 FOR I IN 1 .. ROW_COUNT --Compute Factor
3745 LOOP
3746 jai_cmn_utils_pkg.print_log('utils.log','lt_tax_amt_rate_tax_tab(I) = ' || lt_tax_amt_rate_tax_tab(I));
3747 jai_cmn_utils_pkg.print_log('utils.log','lt_tax_amt_non_rate_tab(I) = ' || lt_tax_amt_non_rate_tab(I));
3748 jai_cmn_utils_pkg.print_log('utils.log','inclu flag = ' || lt_inclusive_tax_tab(I));
3749 IF lt_inclusive_tax_tab(I) = 'Y'
3750 THEN
3751 ln_total_tax_per_rupee := ln_total_tax_per_rupee + nvl(lt_tax_amt_rate_tax_tab(I),0) ;
3752 ln_total_non_rate_tax := ln_total_non_rate_tax + nvl(lt_tax_amt_non_rate_tab(I),0);
3753 END IF;
3754 END LOOP; --End Compute Factor
3755
3756 ln_total_tax_per_rupee := ln_total_tax_per_rupee + 1;
3757
3758 IF ln_total_tax_per_rupee <> 0
3759 THEN
3760 ln_exclusive_price := (NVL(v_precedence_0,0) - ln_total_non_rate_tax ) / ln_total_tax_per_rupee;
3761 END If;
3762 jai_cmn_utils_pkg.print_log('utils.log','tot tax per rupee = ' || ln_total_tax_per_rupee
3763 || 'totl non tax = ' || ln_total_non_rate_tax );
3764 jai_cmn_utils_pkg.print_log('utils.log','incl sp = ' || v_precedence_0 || 'excl price = ' || ln_exclusive_price);
3765
3766 FOR i in 1 .. row_count --Compute Tax Amount
3767 Loop
3768
3769 -- Commented out by Shyan for bug 10358786 on 28-Jan-2011 , Begin
3770 ------------------------------------------------------------------
3771 --tax_amt_tab (i) := (lt_tax_amt_rate_tax_tab(I) * ln_exclusive_price ) + lt_tax_amt_non_rate_tab(I);
3772 ------------------------------------------------------------------
3773 -- Commented out by Shyan for bug 10358786 on 28-Jan-2011 , End
3774
3775 -- Added by Shyan for bug 10358786 on 28-Jan-2011 , Begin
3776 ------------------------------------------------------------------
3777 --Added by qiong for bug 12717416 begin
3778 ----------------------------------------------------------------
3779 IF tax_rate_tab( i ) <> 0 THEN
3780 --Added by qiong for bug12717416 end
3781 tax_amt_tab (i):=0;
3782 IF lt_inclusive_tax_tab(I) = 'Y' THEN
3783 IF (tax_type_tab(I) = 1 and ln_assessable_value>1) OR
3784 (tax_type_tab(I) = 4 and ln_vat_assessable_value> 1)
3785 --OR (tax_type_tab(I) = 7 and ln_gst_assessable_value >1)
3786 THEN
3787
3788 IF tax_type_tab(I) = 1 and ln_assessable_value >1 Then
3789 ln_amount:= ln_assessable_value;
3790 ELSIF tax_type_tab(I) = 4 and ln_vat_assessable_value >1 Then
3791 ln_amount:= ln_vat_assessable_value;
3792 /*ELSIF tax_type_tab(I) = 7 and ln_gst_assessable_value >1 Then
3793 ln_amount:= ln_gst_assessable_value; */
3794 END IF;
3795
3796 IF P1(I) < I AND p1(I) NOT IN (-1, 0) then
3797 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P1(i))* tax_rate_tab(i)/100;
3798 ELSIF P1(I)=0 Then
3799 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3800 END IF;
3801
3802 IF P2(I) < I AND p2(I) NOT IN (-1, 0) then
3803 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P2(i))* tax_rate_tab(i)/100;
3804 ELSIF P2(I)=0 Then
3805 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3806 END IF;
3807
3808 IF P3(I) < I AND p3(I) NOT IN (-1, 0) then
3809 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P3(i))* tax_rate_tab(i)/100;
3810 ELSIF P3(I)=0 Then
3811 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3812 END IF;
3813
3814 IF P4(I) < I AND p4(I) NOT IN (-1, 0) then
3815 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P4(i))* tax_rate_tab(i)/100;
3816 ELSIF P4(I)=0 Then
3817 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3818 END IF;
3819
3820 IF P5(I) < I AND p5(I) NOT IN (-1, 0) then
3821 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P5(i))* tax_rate_tab(i)/100;
3822 ELSIF P5(I)=0 Then
3823 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3824 END IF;
3825
3826 IF P6(I) < I AND p6(I) NOT IN (-1, 0) then
3827 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P6(i))* tax_rate_tab(i)/100;
3828 ELSIF P6(I)=0 Then
3829 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3830 END IF;
3831
3832 IF P7(I) < I AND p7(I) NOT IN (-1, 0) then
3833 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P7(i))* tax_rate_tab(i)/100;
3834 ELSIF P7(I)=0 Then
3835 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3836 END IF;
3837
3838 IF P8(I) < I AND p8(I) NOT IN (-1, 0) then
3839 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P8(i))* tax_rate_tab(i)/100;
3840 ELSIF P8(I)=0 Then
3841 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3842 END IF;
3843
3844 IF P9(I) < I AND p9(I) NOT IN (-1, 0) then
3845 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P9(i))* tax_rate_tab(i)/100;
3846 ELSIF P9(I)=0 Then
3847 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3848 END IF;
3849
3850 IF P10(I) < I AND p10(I) NOT IN (-1, 0) then
3851 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P10(i))* tax_rate_tab(i)/100;
3852 ELSIF P10(I)=0 Then
3853 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3854 END IF;
3855 ELSE
3856 tax_amt_tab (i) := (lt_tax_amt_rate_tax_tab(I) * ln_exclusive_price ) + lt_tax_amt_non_rate_tab(I);
3857 END IF;
3858 ELSE
3859 IF (tax_type_tab(I) = 1 and ln_assessable_value >1) OR
3860 (tax_type_tab(I) = 4 and ln_vat_assessable_value > 1)
3861 -- OR (tax_type_tab(I) = 7 and ln_gst_assessable_value >1)
3862 THEN
3863
3864 IF tax_type_tab(I) = 1 and ln_assessable_value >1 Then
3865 ln_amount:= ln_assessable_value;
3866 ELSIF tax_type_tab(I) = 4 and ln_vat_assessable_value >1 Then
3867 ln_amount:= ln_vat_assessable_value;
3868 /* ELSIF tax_type_tab(I) = 7 and ln_gst_assessable_value >1 Then
3869 ln_amount:= ln_gst_assessable_value; */
3870 END IF;
3871 ELSE ln_amount:= NVL(v_precedence_0,0);
3872 END IF;
3873 IF P1(I) < I AND p1(I) NOT IN (-1, 0) then
3874 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P1(i))* tax_rate_tab(i)/100;
3875 ELSIF P1(I)=0 Then
3876 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3877 END IF;
3878
3879 IF P2(I) < I AND p2(I) NOT IN (-1, 0) then
3880 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P2(i))* tax_rate_tab(i)/100;
3881 ELSIF P2(I)=0 Then
3882 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3883 END IF;
3884
3885 IF P3(I) < I AND p3(I) NOT IN (-1, 0) then
3886 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P3(i))* tax_rate_tab(i)/100;
3887 ELSIF P3(I)=0 Then
3888 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3889 END IF;
3890
3891 IF P4(I) < I AND p4(I) NOT IN (-1, 0) then
3892 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P4(i))* tax_rate_tab(i)/100;
3893 ELSIF P4(I)=0 Then
3894 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3895 END IF;
3896
3897 IF P5(I) < I AND p5(I) NOT IN (-1, 0) then
3898 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P5(i))* tax_rate_tab(i)/100;
3899 ELSIF P5(I)=0 Then
3900 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3901 END IF;
3902
3903 IF P6(I) < I AND p6(I) NOT IN (-1, 0) then
3904 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P6(i))* tax_rate_tab(i)/100;
3905 ELSIF P6(I)=0 Then
3906 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3907 END IF;
3908
3909 IF P7(I) < I AND p7(I) NOT IN (-1, 0) then
3910 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P7(i))* tax_rate_tab(i)/100;
3911 ELSIF P7(I)=0 Then
3912 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3913 END IF;
3914
3915 IF P8(I) < I AND p8(I) NOT IN (-1, 0) then
3916 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P8(i))* tax_rate_tab(i)/100;
3917 ELSIF P8(I)=0 Then
3918 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3919 END IF;
3920
3921 IF P9(I) < I AND p9(I) NOT IN (-1, 0) then
3922 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P9(i))* tax_rate_tab(i)/100;
3923 ELSIF P9(I)=0 Then
3924 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3925 END IF;
3926
3927 IF P10(I) < I AND p10(I) NOT IN (-1, 0) then
3928 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P10(i))* tax_rate_tab(i)/100;
3929 ELSIF P10(I)=0 Then
3930 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3931 END IF;
3932 END IF;
3933 ------------------------------------------------------------------
3934 -- Added by Shyan for bug 10358786 on 28-Jan-2011 , End
3935 END IF;--Added by qiong.liu for bug12717416
3936 tax_amt_tab(I) := round(tax_amt_tab(I) ,round_factor_tab(I));
3937 jai_cmn_utils_pkg.print_log('utils.log','in final loop , tax amt is ' ||tax_amt_tab(I));
3938 END LOOP; --End Compute Tax Amount
3939
3940
3941 --commented out by shyan for bug 10100899 on 12/11/2010, Begin
3942 ---------------------------------------------------------------------
3943 /*
3944 --IF condition added by nprashar for bug # 10100899
3945 If ln_check_curr_conv = 1 then --the PO tax lines are in different currencies
3946 For i in 1 .. row_count Loop
3947 If p_curr_check(i) = v_po_currency Then
3948 tax_amt_tab(i) := tax_amt_tab(i) / p_currency_conversion_rate;
3949 tax_amt_tab(i) := round(tax_amt_tab(i) ,round_factor_tab(I));
3950 End If;
3951 End Loop;
3952 End IF;
3953 */
3954 ---------------------------------------------------------------------
3955 --commented out by shyan for bug 10100899 on 12/11/2010, End
3956
3957 -- Added by Eric Ma for bug 10100899 on 17-Nov-2010,Begin
3958 ---------------------------------------------------------------
3959 --1. When populating the JAI_RCV_LINE_TAXES (OFI Receipt Tax Line), amount should be with the currency in corresponding PO tax line
3960 --2. When populating the JAI_RCV_LINES (OFI Receipt Line Detail), amount should be converted to its corresponding PO currency amount.
3961 --3. tax_amt_tab(i) saved the amount in 'INR' before currency conversion
3962 --4. v_cor_amount saved the amount in 'INR' before currency conversion
3963 ---------------------------------------------------------------
3964 -- Added by Eric Ma for bug 10100899 on 17-Nov-2010,Begin
3965
3966 FOR i in 1.. lt_tax_table.count LOOP
3967 po_lines_rec := lt_tax_table(i);
3968
3969 --Add by Xiao for Adhoc taxes, reg bug#12589218 on 16-Jun-2011, begin
3970 -----------------------------------------------------------------------
3971 --commented by qiong.liu for bug12717416 2011.07.06 begin
3972 ----------------------------------------------------------
3973 /*
3974 OPEN get_adhoc_flag(po_lines_rec.tax_id);
3975 FETCH get_adhoc_flag INTO lv_adhoc_flag;
3976 CLOSE get_adhoc_flag;
3977 */
3978
3979 /*
3980 IF lt_adhoc_tax_tab(i) = 'Y' THEN
3981 IF nvl(v_loc_quantity, 0) <> 0 THEN
3982 tax_amt_tab(i) := po_lines_rec.tax_amount * p_qty_received/v_loc_quantity;
3983 END IF;
3984 END IF;
3985 */
3986 -----------------------------------------------------------
3987 --commented by qiong.liu for bug12717416 2011.07.06 end
3988 ----------------------------------------------------------------------
3989 --Add by Xiao for Adhoc taxes, reg bug#12589218 on 16-Jun-2011, end
3990
3991
3992
3993
3994 v_cor_amount := nvl(tax_amt_tab(i), 0);
3995 -- Added by shyan for bug 10100899 on 12-Nov-2010,Begin
3996 ---------------------------------------------------------------
3997 --replace v_conv_factor with v_currency_conversion_rate by pezheng for bug 10630487
3998 IF po_lines_rec.currency <> v_func_currency
3999 THEN
4000 tax_amt_tab(i) := ROUND(nvl(tax_amt_tab(i), 0) / NVL(v_currency_conversion_rate, 1)/*v_conv_factor*/,round_factor_tab(I));
4001 ELSE
4002 tax_amt_tab(i) := nvl(tax_amt_tab(i), 0);
4003 End IF;
4004 ---------------------------------------------------------------
4005 -- Added by shyan for bug 10100899 on 12-Nov-2010,End
4006
4007 /* Added by LGOPLASA. Bug 4210102.
4008 * Added CVD, Excise and customs education cess */
4009 IF upper(po_lines_rec.tax_type) IN ('EXCISE', 'ADDL. EXCISE', 'OTHER EXCISE', 'CUSTOMS', 'CVD',
4010 JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS,jai_constants.tax_type_exc_edu_cess,
4011 JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS,jai_constants.tax_type_cvd_edu_cess,
4012 JAI_CONSTANTS.TAX_TYPE_SH_CUSTOMS_EDU_CESS,jai_constants.tax_type_customs_edu_cess)/*Bug5904736 bduvarag*/ THEN
4013 -- v_claimable_amount := NVL(v_cor_amount * v_conv_factor, 0) * NVL(lines_rec.duty, 0) / 100;
4014 -- above line commented by Aparajita for bug#2929171
4015 v_claimable_amount := NVL(v_cor_amount, 0) * NVL(po_lines_rec.duty, 0) / 100;
4016 ELSE
4017 v_claimable_amount := 0;
4018 END IF;
4019
4020 --Added by GSRI on 21-OCT-01
4021 SELECT COUNT(*)
4022 INTO v_chk_receipt_tax_lines
4023 FROM JAI_RCV_LINE_TAXES
4024 WHERE shipment_line_id = p_shipment_line_id
4025 AND shipment_header_id = p_shipment_header_id
4026 AND tax_id = po_lines_rec.tax_id;
4027
4028 IF v_chk_receipt_tax_lines = 0 THEN
4029 /*DELETE FROM JAI_RCV_LINE_TAXES
4030 WHERE shipment_line_id = p_shipment_line_id AND
4031 shipment_header_id = p_shipment_header_id AND
4032 tax_id = tax_rec.tax_id;*/
4033 --End Addition by on GSRI 21-OCT-01
4034
4035 INSERT INTO JAI_RCV_LINE_TAXES
4036 (
4037 shipment_line_id,
4038 tax_line_no,
4039 shipment_header_id,
4040 tax_id,
4041 tax_name,
4042 currency,
4043 tax_rate,
4044 qty_rate,
4045 uom,
4046 tax_amount,
4047 tax_type,
4048 modvat_flag,
4049 creation_date,
4050 created_by,
4051 last_update_date,
4052 last_updated_by,
4053 last_update_login,
4054 vendor_id,
4055 claimable_amount,
4056 vendor_site_id, --Added by Nagaraj.s for Bug3037075
4057 third_party_flag,
4058 --3848133
4059 precedence_1,
4060 precedence_2,
4061 precedence_3,
4062 precedence_4,
4063 precedence_5,
4064 precedence_6, -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
4065 precedence_7,
4066 precedence_8,
4067 precedence_9,
4068 precedence_10,
4069 transaction_id
4070 --3848133
4071 )
4072 VALUES
4073 (
4074 p_shipment_line_id,
4075 po_lines_rec.tax_line_no,
4076 p_shipment_header_id,
4077 po_lines_rec.tax_id,
4078 po_lines_rec.tax_name,
4079 po_lines_rec.currency,
4080 po_lines_rec.tax_rate,
4081 po_lines_rec.qty_rate,
4082 po_lines_rec.uom,
4083 tax_amt_tab(i),
4084 po_lines_rec.tax_type,
4085 lt_tax_modvat_flag(i),
4086 p_creation_date,
4087 p_created_by,
4088 p_last_update_date,
4089 p_last_updated_by,
4090 p_last_update_login,
4091 po_lines_rec.vendor_id,
4092 v_claimable_amount,
4093 v_tax_vendor_site_id(i) , /*Added by nprshar for bug # 10193326 */ --Added by Nagaraj.s for Bug3037075
4094 lt_third_party_flag(i),
4095 --3848133
4096 po_lines_rec.precedence_1,
4097 po_lines_rec.precedence_2,
4098 po_lines_rec.precedence_3,
4099 po_lines_rec.precedence_4,
4100 po_lines_rec.precedence_5,
4101 po_lines_rec.precedence_6, -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
4102 po_lines_rec.precedence_7,
4103 po_lines_rec.precedence_8,
4104 po_lines_rec.precedence_9,
4105 po_lines_rec.precedence_10,
4106 p_transaction_id
4107 --3848133
4108 );
4109 END IF;
4110
4111 IF po_lines_rec.tax_type NOT IN ('TDS', 'Modvat Recovery') THEN
4112 -- v_tax_total := v_tax_total + NVL(v_cor_amount * v_conv_factor, 0); -- Commneted out by shyan for bug 10100899 on 12-Nov-2010
4113 v_tax_total := v_tax_total + NVL(v_cor_amount / v_conv_factor, 0); -- Added by shyan for bug 10100899 on 12-Nov-2010
4114 END IF;
4115 IF v_debug_flag = 'Y' THEN
4116 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_tax_total is ' ||v_tax_total);
4117 END IF;
4118 END LOOP;
4119 END IF; ----Add by Kevin Cheng for bug 6853787 Mar 5, 2008
4120
4121 IF p_transaction_type = 'RECEIVE' -- AND was commented by GSRI on 21-OCT-01 and OR was added
4122 /* R12-PADDR OR v_chk_form IS NOT NULL */
4123 THEN
4124 update_receipt_line;
4125 END IF;
4126
4127
4128 IF p_transaction_type = 'MATCH' THEN
4129
4130 UPDATE JAI_RCV_LINES
4131 SET -- tax_modified_flag = 'N', /* Vijay Shankar for Bug#3940588 RECEIPTS DEPLUG*/
4132 line_location_id = p_line_location_id,
4133 tax_amount = NVL(tax_amount, 0) + v_tax_total,
4134 last_update_date = p_last_update_date,
4135 last_updated_by = p_last_updated_by,
4136 last_update_login = p_last_update_login
4137 WHERE shipment_line_id = p_shipment_line_id;
4138 END IF;
4139
4140
4141 END IF;
4142 -- ADDED FOR BAR-CODING BY GSri 21-OCT-01
4143
4144
4145 /* R12-PADDR p_chk_form := v_chk_form; -- Vijay Shankar for Bug#3940588. RECEIPTS DEPLUG
4146 */
4147
4148 -- END FOR BAR-CODING BY GSri and on 21-OCT-01
4149 IF v_receipt_source_code IS NOT NULL AND
4150 p_transaction_type = 'RECEIVE' AND
4151 v_receipt_source_code = 'INTERNAL ORDER' AND
4152 NVL(v_duplicate_ship, 'N') = 'N'
4153 THEN
4154 set_receipt_flag;
4155 -- v_receipt_modify_flag := 'N'; --commented by csahoo for bug#6154234
4156 insert_receipt_line;
4157
4158 -- ssumaith - bug# 3657662
4159 open c_order_cur(v_shipment_num);
4160 fetch c_order_cur into v_order_header_id;
4161 close c_order_cur;
4162
4163 if v_debug_flag = 'Y' THEN
4164 UTL_FILE.PUT_LINE(v_myfilehandle,'The Value of v_order_header_id is ' || v_order_header_id);
4165 end if;
4166 -- ssumaith - bug# 3657662
4167
4168 --Changed by Nagaraj.s on 30/07/2003 for Bug#2993865
4169 FOR line_rec IN (
4170 SELECT
4171 line_id,
4172 sha.transactional_curr_code,
4173 spl.delivery_detail_id,
4174 spl.quantity,
4175 spl.unit_code
4176
4177 FROM
4178
4179 oe_order_lines_all sla,
4180 oe_order_headers_all sha,
4181 po_requisition_headers_all prha,
4182 po_requisition_lines_all prla,
4183 JAI_OM_WSH_LINES_ALL spl,
4184 rcv_shipment_headers rsh
4185
4186 WHERE
4187
4188 prha.requisition_header_id = prla.requisition_header_id
4189 and sha.source_document_id = prha.requisition_header_id
4190 AND sla.header_id = sha.header_id
4191 AND sha.header_id in /* following subquery added by ssumaith - 3772135*/
4192 (
4193 select order_header_id
4194 from JAI_OM_WSH_LINES_ALL
4195 where delivery_id = v_shipment_num
4196 )
4197 /* = v_order_header_id -- ssumaith - bug# 3657662*/
4198 and sla.source_document_line_id = prla.requisition_line_id
4199 /*and to_char(spl.delivery_id) = rsh.shipment_num*/
4200 --and spl.delivery_id = rsh.shipment_num
4201 --commented the above and added the below by Ramananda for Bug#4533114
4202 and spl.delivery_id = decode(ltrim(translate(shipment_num,'0123456789','~'),'~'),NULL,rsh.shipment_num,
4203 (select delivery_id from wsh_new_deliveries where name=rsh.shipment_num))
4204 and spl.order_line_id = sla.line_id
4205 and prla.requisition_line_id = p_requisition_line_id
4206 AND rsh.shipment_header_id = p_shipment_header_id
4207 and rownum <= (select line_num from rcv_shipment_lines where shipment_line_id = p_shipment_line_id) -- bug#3878439
4208 )
4209
4210
4211 LOOP
4212 v_line_id := line_rec.line_id;
4213 v_so_currency := line_rec.transactional_curr_code;
4214 v_loc_quantity := line_rec.quantity;
4215 v_picking_line_id := line_rec.delivery_detail_id;
4216
4217 -- start adding by Aparajita for bug # 2813244 on 05/03/2003
4218 IF v_rcv_uom_code <> line_rec.unit_code THEN
4219
4220 Inv_Convert.inv_um_conversion
4221 (
4222 v_rcv_uom_code,
4223 line_rec.unit_code,
4224 v_item_id,
4225 v_uom_rate
4226 );
4227
4228 IF v_uom_rate = -99999 THEN
4229 v_uom_rate := 0;
4230 END IF;
4231
4232 ELSE
4233
4234 v_uom_rate := 1;
4235
4236 END IF; --End if for v_rcv_uom_code
4237
4238
4239 v_uom_rate := NVL(v_uom_rate, 1);
4240
4241 IF v_debug_flag = 'Y' THEN
4242 UTL_FILE.PUT_LINE(v_myfilehandle, ' v_uom_rate:' || v_uom_rate
4243 ||', v_line_id:' || v_line_id|| ', v_so_currency:' || v_so_currency
4244 ||', v_loc_quantity:' || v_loc_quantity||', delivery_detail_id:' || v_picking_line_id
4245 );
4246 END IF;
4247 -- end adding by Aparajita for bug # 2813244 on 05/03/2003
4248
4249 END LOOP; --Added by Jagdish 13-sep-01
4250
4251 IF v_currency_code <> v_func_currency THEN
4252 v_conv_factor := NVL(v_currency_conversion_rate, 1);
4253 ELSE
4254 v_conv_factor := 1;
4255 END IF;
4256
4257 FOR tax_rec IN
4258 (
4259 SELECT tax_line_no,
4260 stl.tax_id,
4261 stl.tax_rate,
4262 stl.qty_rate,
4263 uom,
4264 stl.tax_amount,
4265 base_tax_amount,
4266 func_tax_amount,
4267 jtc.tax_name,
4268 jtc.tax_type,
4269 jtc.vendor_id,
4270 jtc.mod_cr_percentage,
4271 jtc.rounding_factor,
4272 jtc.duty_drawback_percentage duty,
4273 --3848133
4274 stl.precedence_1,
4275 stl.precedence_2,
4276 stl.precedence_3,
4277 stl.precedence_4,
4278 stl.precedence_5,
4279 stl.precedence_6, -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
4280 stl.precedence_7,
4281 stl.precedence_8,
4282 stl.precedence_9,
4283 stl.precedence_10,
4284 tax_types.regime_code regime_code
4285 --3848133
4286 FROM JAI_OM_WSH_LINE_TAXES stl,
4287 JAI_CMN_TAXES_ALL jtc,
4288 jai_regime_tax_types_v tax_types
4289 WHERE delivery_detail_id = v_picking_line_id --added by GSri and Jagdish on 5-may-01
4290 AND jtc.tax_id = stl.tax_id
4291 AND tax_types.tax_type(+) = jtc.tax_type
4292 )
4293 LOOP
4294
4295 IF NVL(v_loc_quantity, 0) <> 0 THEN
4296 v_cor_amount := ROUND(( P_qty_received * tax_rec.tax_amount * v_uom_rate / v_loc_quantity),
4297 NVL(tax_rec.rounding_factor, 0));
4298 END IF;
4299
4300 -- Start of addition by Srihari on 30-NOV-99
4301 --added v_item_trading_flag for bug#12800001
4302 IF v_item_modvat_flag = 'N' AND v_item_trading_flag = 'N' and upper(tax_rec.tax_type) IN ('EXCISE', 'ADDL. EXCISE', 'OTHER EXCISE',
4303 '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*/
4304 THEN
4305 v_tax_modvat_flag := 'N';
4306 -- End of addition by Srihari on 30-NOV-99
4307
4308 /* following elsif added by Vijay Shankar for Bug#4250236(4245089). VAT Impl. */
4309 ELSIF lv_vat_recoverable_for_item <> jai_constants.yes
4310 AND tax_rec.regime_code = jai_constants.vat_regime
4311 THEN
4312 v_tax_modvat_flag := jai_constants.no;
4313
4314 ELSIF tax_rec.mod_cr_percentage > 0 THEN
4315 v_tax_modvat_flag := 'Y';
4316 ELSE
4317 v_tax_modvat_flag := 'N';
4318 END IF;
4319
4320 IF tax_rec.vendor_id IS NULL THEN
4321 v_internal_vendor := - v_rsh_organization_id;
4322 ELSE
4323 v_internal_vendor := tax_rec.vendor_id;
4324 END IF;
4325
4326 /* Added by LGOPALSa. Bug 4210102.
4327 * ADded Excise, Customs and CVD education cess */
4328
4329 IF upper(tax_rec.tax_type) IN ('EXCISE', 'ADDL. EXCISE', 'OTHER EXCISE', 'CUSTOMS', 'CVD',
4330 JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS,jai_constants.tax_type_exc_edu_cess,
4331 JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS,jai_constants.tax_type_cvd_edu_cess,
4332 JAI_CONSTANTS.TAX_TYPE_SH_CUSTOMS_EDU_CESS,jai_constants.tax_type_customs_edu_cess/*Bug5989740 bduvarag*/
4333 )
4334 THEN
4335 v_claimable_amount := NVL(v_cor_amount * v_conv_factor, 0) * NVL(tax_rec.duty, 0) / 100;
4336 ELSE
4337 v_claimable_amount := 0;
4338 END IF;
4339
4340 -- Added by GSRI on 21-OCT-01
4341 SELECT COUNT(*)
4342 INTO v_chk_receipt_tax_lines
4343 FROM JAI_RCV_LINE_TAXES
4344 WHERE shipment_line_id = p_shipment_line_id
4345 AND shipment_header_id = p_shipment_header_id
4346 AND tax_id = tax_rec.tax_id;
4347
4348 -- END of Addition by GSRI on 21-OCT-01
4349 IF v_chk_receipt_tax_lines = 0 THEN
4350
4351 INSERT INTO JAI_RCV_LINE_TAXES
4352 (
4353 shipment_line_id,
4354 tax_line_no,
4355 shipment_header_id,
4356 tax_id,
4357 tax_name,
4358 currency,
4359 tax_rate,
4360 qty_rate,
4361 uom,
4362 tax_amount,
4363 tax_type,
4364 modvat_flag,
4365 creation_date,
4366 created_by,
4367 last_update_date,
4368 last_updated_by,
4369 last_update_login,
4370 vendor_id,
4371 --3848133
4372 precedence_1,
4373 precedence_2,
4374 precedence_3,
4375 precedence_4,
4376 precedence_5,
4377 precedence_6, -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
4378 precedence_7,
4379 precedence_8,
4380 precedence_9,
4381 precedence_10,
4382 transaction_id
4383 --3848133
4384 )
4385 VALUES
4386 (
4387 p_shipment_line_id,
4388 tax_rec.tax_line_no,
4389 p_shipment_header_id,
4390 tax_rec.tax_id,
4391 tax_rec.tax_name,
4392 p_currency_code,
4393 tax_rec.tax_rate,
4394 tax_rec.qty_rate,
4395 tax_rec.uom,
4396 v_cor_amount,
4397 tax_rec.tax_type,
4398 v_tax_modvat_flag,
4399 p_creation_date,
4400 p_created_by,
4401 p_last_update_date,
4402 p_last_updated_by,
4403 p_last_update_login,
4404 v_internal_vendor,
4405 --3848133
4406 tax_rec.precedence_1,
4407 tax_rec.precedence_2,
4408 tax_rec.precedence_3,
4409 tax_rec.precedence_4,
4410 tax_rec.precedence_5,
4411 tax_rec.precedence_6, -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
4412 tax_rec.precedence_7,
4413 tax_rec.precedence_8,
4414 tax_rec.precedence_9,
4415 tax_rec.precedence_10,
4416 p_transaction_id
4417 --3848133
4418 );
4419 END IF;
4420
4421 IF tax_rec.tax_type NOT IN ('TDS', 'Modvat Recovery') THEN
4422 v_tax_total := v_tax_total + NVL(v_cor_amount * v_conv_factor, 0);
4423 END IF;
4424
4425 END LOOP;
4426
4427 update_receipt_line;
4428
4429 END IF;
4430
4431 IF v_debug_flag = 'Y' THEN
4432 UTL_FILE.PUT_LINE(v_myfilehandle,'Before the condition if p_transaction_type is UNORDERED ');
4433 END IF;
4434
4435 IF p_transaction_type = 'UNORDERED'
4436 /* R12-PADDR AND v_chk_form IS NOT NULL */
4437 THEN
4438
4439 IF v_debug_flag = 'Y' THEN
4440 UTL_FILE.PUT_LINE(v_myfilehandle,'Before set receipt flag');
4441 END IF;
4442
4443 set_receipt_flag;
4444
4445 IF v_debug_flag = 'Y' THEN
4446 UTL_FILE.PUT_LINE(v_myfilehandle,'After set receipt flag');
4447 END IF;
4448
4449 -- v_receipt_modify_flag := 'N'; /*Commented by nprashar for bug # 9797881 */
4450
4451 IF v_debug_flag = 'Y' THEN
4452 UTL_FILE.PUT_LINE(v_myfilehandle,'Before Insert Receipt Line Procedure');
4453 END IF;
4454
4455 insert_receipt_line;
4456
4457 IF v_debug_flag = 'Y' THEN
4458 UTL_FILE.PUT_LINE(v_myfilehandle,'After Insert Receipt Line Procedure');
4459 END IF;
4460
4461 END IF;
4462
4463 END IF;
4464
4465
4466 -- Vijay Shankar for Bug#3940588. no more processing is required. so return back
4467 -- GOTO end_of_procedure;
4468 -- NULL;
4469
4470 /* A lot of Code is COMMENTED by Vijay Shankar for Bug#3940588. RECEIPTS DEPLUG
4471 Please refer to Old Version of the Object incase commented code needs to be looked at
4472 */
4473
4474 -- <<end_of_procedure>>
4475
4476 /* Validation for RETURN TO VENDOR transactions. following check is not required for Correct transactions because
4477 RTV creation should have taken care of this check
4478 Vijay Shankar for Bug#3940588. RECEIPTS DEPLUG */
4479 FOR exc_rec IN (SELECT jrtl.tax_amount, jrtl.tax_type tax_type, nvl(mod_cr_percentage, 0) mod_cr_percentage
4480 , nvl(tax_types.regime_code, 'XXXX') regime_code
4481 FROM JAI_RCV_LINE_TAXES jrtl, JAI_CMN_TAXES_ALL jtc, jai_regime_tax_types_v tax_types
4482 WHERE shipment_line_id = p_shipment_line_id
4483 AND NVL(modvat_flag, 'N') = 'Y'
4484 AND jtc.tax_type = tax_types.tax_type(+)
4485 AND jrtl.tax_id = jtc.tax_id)
4486 LOOP
4487
4488 if upper(exc_rec.tax_type) IN ('EXCISE', 'CVD', 'ADDL. EXCISE', 'OTHER EXCISE',
4489 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*/
4490 then
4491 v_chk_excise := NVL(v_chk_excise, 0) + (exc_rec.tax_amount * exc_rec.mod_cr_percentage / 100);
4492
4493 /* following added by Vijay Shankar for Bug#4250236(4245089). VAT Impl. */
4494 elsif exc_rec.regime_code = jai_constants.vat_regime then
4495 ln_chk_vat := nvl(ln_chk_vat, 0) + (exc_rec.tax_amount * exc_rec.mod_cr_percentage / 100);
4496 end if;
4497
4498 END LOOP;
4499
4500 /* following if condition for receive and match added by Vijay shankar for Bug#4250236(4245089) VAT Impl. */
4501 IF p_transaction_type in ('RECEIVE', 'MATCH') THEN
4502
4503 IF v_rg_location_id = 0 THEN
4504 v_rg_location_id := null;
4505 END IF;
4506
4507 open c_rgm_setup_for_orgn_loc(jai_constants.vat_regime, jai_constants.orgn_type_io,
4508 p_organization_id, v_rg_location_id);
4509 fetch c_rgm_setup_for_orgn_loc into ln_vat_setup_chk;
4510 close c_rgm_setup_for_orgn_loc;
4511
4512 fnd_file.put_line( fnd_file.log, 'VAT SetupChkforOrgnLoc:'||ln_vat_setup_chk
4513 ||', ChkVat:'||nvl(ln_chk_vat, -999999)
4514 ||', Orgn:'||p_organization_id||', Loc:'||v_rg_location_id
4515 );
4516
4517 IF nvl(ln_vat_setup_chk,0) = 0 and nvl(ln_chk_vat,0) <> 0 THEN
4518 errormsg:='Organization and Location is not attached to VAT Regime';
4519 RAISE_APPLICATION_ERROR (-20502, 'Organization and Location is not attached to VAT Regime');
4520 END IF;
4521
4522 ELSIF p_transaction_type = 'RETURN TO VENDOR' THEN
4523
4524 -- NULL; --commented by bgowrava for Bug#6459894
4525 /* Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.*/
4526 /* Uncommented below statements by bgowrava for Bug#6459894*/
4527 open c_fetch_unclaim_cenvat;
4528 fetch c_fetch_unclaim_cenvat into v_unclaim_cenvat_flag,
4529 v_cenvat_claimed_ptg,
4530 v_non_bonded_delivery_flag,
4531 v_cenvat_amount;
4532 close c_fetch_unclaim_cenvat;
4533
4534 /* IF v_debug_flag = 'Y' THEN
4535 UTL_FILE.PUT_LINE(v_myfilehandle,' ABC1. v_register_type:' || v_register_type
4536 || ', v_chk_excise:' || v_chk_excise||', v_item_modvat_flag:' || v_item_modvat_flag
4537 ||', v_item_class:' || v_item_class||', chkVat:'||ln_chk_vat );
4538 UTL_FILE.PUT_LINE(v_myfilehandle,' ABC2. v_cenvat_claimed_ptg:' || v_cenvat_claimed_ptg
4539 ||', v_manufacturing:' || v_manufacturing ||', v_non_bonded_flag:' || v_non_bonded_delivery_flag
4540 ||', unclaim_flag:'||v_unclaim_cenvat_flag);
4541 END IF;*/
4542
4543 FOR i IN (select manufacturing from JAI_CMN_INVENTORY_ORGS where organization_id=p_organization_id) loop
4544 v_manufacturing := i.manufacturing;
4545 EXIT WHEN v_manufacturing IS NOT NULL;
4546 END LOOP;
4547
4548 open c_rcv_rgm_dtl(jai_constants.vat_regime, p_shipment_line_id);
4549 fetch c_rcv_rgm_dtl into r_rcv_rgm_dtl;
4550 close c_rcv_rgm_dtl;
4551
4552 /* Bug 4516678. Added by Lakshmi Gopalsami */
4553 pick_register_type(p_organization_id,
4554 v_item_id,
4555 v_register_type );
4556
4557 IF NVL(v_item_modvat_flag, 'N') = 'Y'
4558 AND NVL(v_chk_excise, 0) <> 0
4559 AND v_item_class in ('RMIN','RMEX','CGIN', 'CGEX','CCIN','CCEX')
4560 AND nvl(v_cenvat_claimed_ptg,0) = 0
4561 AND nvl(v_manufacturing, 'N') = 'Y'
4562 AND nvl(v_non_bonded_delivery_flag,'N') = 'N'
4563 AND nvl(v_unclaim_cenvat_flag,'N') = 'N'
4564 THEN
4565 errormsg:='RTV not allowed as the CENVAT has not been claimed';
4566 RAISE_APPLICATION_ERROR (-20501, 'RTV not allowed as the CENVAT has not been claimed');
4567 --END IF; commented for bug4516678
4568
4569 /* Bug 4516678. Added by Lakshmi Gopalsami
4570 Added the following code
4571 */
4572 ELSIF v_register_type ='C' -- implies item is CGIN /CGEX
4573 and v_cenvat_amount > 0
4574 and v_cenvat_claimed_ptg < 100 then
4575
4576 -- fetch the receipt quantity
4577 open c_fetch_receive_quantity(p_shipment_header_id,
4578 p_shipment_line_id);
4579 fetch c_fetch_receive_quantity into v_receipt_quantity;
4580 close c_fetch_receive_quantity;
4581
4582 -- fetch the total quantity returned
4583
4584 open c_fetch_transaction_quantity(p_shipment_header_id,
4585 p_shipment_line_id,'RETURN TO VENDOR');
4586 fetch c_fetch_transaction_quantity into v_sum_rtv_quantity;
4587 close c_fetch_transaction_quantity;
4588
4589 if v_receipt_quantity = v_sum_rtv_quantity and nvl(v_non_bonded_delivery_flag,'N') ='N' then --3456636,3273075
4590 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 ');
4591 FND_FILE.PUT_LINE(FND_FILE.LOG, ' 6_31 Please avail the remaining Cenvat credit and then proceed with RTV ' );
4592 errormsg:='RETURN TO VENDOR not allowed.Please claim remaining 50% cenvat' ;
4593 raise_application_error(-20110,'RETURN TO VENDOR not allowed.Please claim remaining 50% cenvat' );
4594 end if;
4595 End if; -- End for bug 4516678
4596
4597
4598 fnd_file.put_line( fnd_file.log, 'ItmModFlg:'||v_item_modvat_flag
4599 ||', ChkVat:'||nvl(ln_chk_vat, -999999)
4600 ||', procStaFlg:'||r_rcv_rgm_dtl.process_status_flag
4601 ||', invNo:'||nvl(r_rcv_rgm_dtl.invoice_no,'ABCDEF')
4602 );
4603
4604 -- following added by Vijay Shankar for Bug#4250236(4245089). VAT Impl.
4605 -- Before VAT is fully Claimed if RTV is being done, then we should error out the transaction
4606 /* IF lv_vat_recoverable_for_item = jai_constants.yes
4607 AND NVL(ln_chk_vat, 0) <> 0
4608 AND r_rcv_rgm_dtl.process_status_flag <> 'U' -- Not Unclaimed
4609 AND r_rcv_rgm_dtl.invoice_no IS NULL -- if this is given it means then VAT is eligible for claim
4610 THEN
4611 RAISE_APPLICATION_ERROR (-20502, 'RETURN TO VENDOR not allowed as VAT Amount is not Claimed');
4612 END IF;
4613 */ --commented by Ramananda for Bug #4530112
4614 END IF;
4615
4616 IF v_debug_flag = 'Y' THEN
4617 UTL_FILE.PUT_LINE(v_myfilehandle,'*********End of the Receipt taxes procedure********');
4618 UTL_FILE.FCLOSE(v_myfileHandle);
4619 END IF;
4620
4621
4622 /* Added by Ramananda for bug# exc_objects */
4623 EXCEPTION
4624 WHEN OTHERS THEN
4625 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
4626 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', '. Err:'||sqlerrm ); --bgowrava for bug#6459894
4627 app_exception.raise_exception (
4628 EXCEPTION_TYPE => 'APP',
4629 EXCEPTION_CODE => -20110 ,
4630 EXCEPTION_TEXT => errormsg
4631 );
4632
4633
4634 END default_taxes_onto_line;
4635
4636 END jai_rcv_tax_pkg;
[Home] [Help]