[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_last_update_login IN NUMBER,
p_organization_id IN NUMBER,
p_customer_id IN NUMBER,
p_order_type_id IN NUMBER,
p_delivery_id IN NUMBER DEFAULT null,
p_delivery_detail_id IN NUMBER DEFAULT null,
p_order_header_id IN NUMBER DEFAULT null,
p_line_id IN NUMBER DEFAULT null
)
IS
/*------------------------------------------------------------------------------------------------------------------
Sl.No. dd/mm/yyyy Author and Details
----- ---------- ----------------
1 12/07/2002 Vijay Created this procedure for Bug#2083127
This procedure is used to match all the delivery details present in a delivery, with the corresponding receipts
present in the register. Receipts are matched based on the inventory_item_id, organization AND location combination.
The order of the matching the receipts is FIFO.
Parameter for this procedure are
errbuf, p_organization_id, p_customer_id, p_order_type_id, p_delivery_id,
p_delivery_detail_id, p_order_header_id, p_line_number
2 13/12/2002 cbabu for Bug# 2689425, FileVersion# 615.2
Quantity available is calculated as JAI_CMN_RG_23D_TRXS.quantity_received - quantity that is matched, but it not
including the RTV quantity, that is calculating wrongly. Changes made
Quantity Available is modified to be calculated as
( JAI_CMN_RG_23D_TRXS.qty_to_adjust - deliver quantity matched with receipt but not ship confirmed )
3. 08-Jun-2005 Version 116.2 jai_cmn_rcv_mach -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
as required for CASE COMPLAINCE.
4. 13-Jun-2005 File Version: 116.3
Ramananda for bug#4428980. Removal of SQL LITERALs is done
5. 26-FEB-2007 SSAWANT , File version 120.5
Forward porting the change in 11.5 bug 5068418 to R12 bug no 5091874.
Issue :
Discount Tax is appearing as zero for order in Ship Confirm Localized screen
Cause :
An if condition is placed for restricting all tax precedance caluculations for
tax_rates <=0. This condition is restricting the calculation of precedance based 'Discount'
taxes ( which have tax_rate_tab (i) < 0 ). Thus the tax amount is calculated as zero.
The calculation should not be done for Adhoc / "Excise" and "CVD" taxes which have corresponding
tax rates defined in matched receipts.
Fix :
Old Condition : IF tax_rate_tab( i ) > 0 AND End_Date_Tab(I) <> 0 THEN
Modified Condition : IF tax_rate_tab( i ) <> 0 AND End_Date_Tab(I) <> 0 AND adhoc_flag_tab(i) = 'N' AND NOT ( tax_rate_tab(i) = -1 AND tax_type_tab(i) IN (1,3) ) THEN
Dependency Introduced due to this bug:
None
6. 13/04/2007 bduvarag for the Bug#5989740, file version 120.6
Forward porting the changes done in 11i bug#5907436
7. 26/09/2007 rchandan for bug#6447097. File version 120.10
Issue : QA observations of Inter org
Fix ; Insert statement into Jai_cmn_match_Taxes did not included the PK
UPDATE Jai_cmn_document_Taxes
SET Tax_Amt = tax_amt_tab(i),
Func_Tax_Amt = tax_amt_tab(i) * NVL( p_curr_conv_factor, 1 )
WHERE source_doc_line_id = p_ref_line_id
AND source_doc_type = 'INTERORG_XFER'
AND Tax_Line_No = i;
Modified the update stmt as
UPDATE Jai_cmn_document_Taxes
SET Tax_Amt = nvl(tax_amt,0)+tax_amt_tab(i), --added nvl(tax_amt,0)
for bug#8445390
Func_Tax_Amt = nvl(Func_Tax_Amt,0)+tax_amt_tab(i) * NVL(
p_curr_conv_factor, 1 ) --added nvl(Func_Tax_Amt,0) for bug#8445390
WHERE source_doc_line_id = p_ref_line_id
AND source_doc_type = 'INTERORG_XFER'
AND Tax_Line_No = i;
SELECT customer_id, inventory_item_id,
picking_line_id delivery_detail_id,
organization_id, subinventory sub_inventory_name, order_no order_number,
order_header_id header_id, release_qty requested_quantity, delivery_id,
order_type_id, location_id ship_from_location_id, uom requested_quantity_uom
FROM JAI_OE_MATCH_LINES_V
WHERE organization_id = p_organization_id
AND order_type_id = p_order_type_id
AND delivery_id = nvl(p_delivery_id, delivery_id)
AND picking_line_id = nvl(p_delivery_detail_id, picking_line_id)
AND customer_id = nvl(p_customer_id, customer_id)
AND order_header_id = nvl(p_order_header_id, order_header_id)
AND line_id= nvl(p_line_id, line_id);
SELECT SUM(receipt_quantity_applied)
FROM JAI_CMN_MATCH_RECEIPTS
WHERE receipt_id = p_receipt_id
AND ship_status IS NULL; -- cbabu for Bug# 2689425
SELECT SUM(quantity_applied)
FROM JAI_CMN_MATCH_RECEIPTS
WHERE ref_line_id = p_delivery_detail_id
and order_invoice = 'O';
SELECT * FROM JAI_CMN_RG_23D_TRXS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND inventory_item_id = p_inventory_item_id
AND qty_to_adjust > 0
ORDER BY register_id;
SELECT count(receipt_id)
FROM JAI_CMN_MATCH_RECEIPTS
WHERE receipt_id = p_register_id
AND ref_line_id = p_delivery_detail_id
AND order_invoice = 'O';
INSERT INTO JAI_CMN_MATCH_RECEIPTS (
receipt_id, ref_line_id,
subinventory, quantity_applied,
issue_uom, receipt_quantity_applied, receipt_quantity_uom,
order_invoice, ship_status,
creation_date, created_by, last_update_date, last_update_login, last_updated_by)
VALUES (
rg23d_entry.register_id, match_detail.delivery_detail_id,
match_detail.sub_inventory_name, v_QtyApplied,
match_detail.requested_quantity_uom, v_QtyAppliedInRcptUom, rg23d_entry.primary_uom_code
, 'O', null,
sysdate, p_created_by, sysdate, p_last_update_login, p_created_by);
UPDATE JAI_CMN_MATCH_RECEIPTS
SET quantity_applied = v_QtyApplied,
receipt_quantity_applied = v_QtyAppliedInRcptUom,
last_update_login = p_last_update_login,
last_update_date = sysdate
WHERE receipt_id = rg23d_entry.register_id
AND ref_line_id = match_detail.delivery_detail_id
AND order_invoice = 'O';
SELECT
a.tax_id ,
a.tax_line_no lno ,
a.precedence_1 p_1 ,
a.precedence_2 p_2 ,
a.precedence_3 p_3 ,
a.precedence_4 p_4 ,
a.precedence_5 p_5 ,
a.precedence_6 p_6 , -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
a.precedence_7 p_7 ,
a.precedence_8 p_8 ,
a.precedence_9 p_9 ,
a.precedence_10 p_10 ,
a.qty_rate ,
a.tax_rate ,
a.tax_amount ,
a.uom ,
nvl( to_char(b.end_date,'DD-MON-YYYY'),
to_char(sysdate,'DD-MON-YYYY')
) valid_date , -- Changed by aiyer for the bug 3433634
decode(
upper(b.tax_type) ,
'EXCISE' ,
1 ,
'ADDL. EXCISE' ,
1 ,
'OTHER EXCISE' ,
1 ,
'CVD' ,
1 ,
'TDS' ,
2 ,
0
) tax_type_val ,
b.mod_cr_Percentage ,
b.tax_type ,
NVL( b.rounding_factor, 0 ) rnd
FROM
JAI_OPM_SO_PICK_TAXES a,
JAI_OPM_TAXES b
WHERE
a.bol_id = p_ref_line_id AND
a.bolline_no = p_line_no AND
a.tax_id = b.tax_id
ORDER BY
a.tax_line_no;
SELECT a.tax_id, a.tax_line_no lno,
a.precedence_1 p_1, a.precedence_2 p_2, a.precedence_3 p_3, a.precedence_4 p_4, a.precedence_5 p_5,
a.precedence_6 p_6, a.precedence_7 p_7, a.precedence_8 p_8, a.precedence_9 p_9, a.precedence_10 p_10, -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
a.qty_rate,
a.tax_rate, a.tax_amount, a.uom, b.end_date valid_date,
decode(upper(b.tax_type),'EXCISE', 1, 'ADDL. EXCISE', 1, 'OTHER EXCISE', 1, 'CVD',1, 'TDS', 2, 0) tax_type_val,
b.mod_cr_Percentage, b.vendor_id, b.tax_type, NVL( b.rounding_factor, 0 ) rnd
FROM JAI_AR_TRX_TAX_LINES a, JAI_CMN_TAXES_ALL b
WHERE a.link_to_cust_trx_line_id = p_line_id
AND a.tax_id = b.tax_id
ORDER BY a.tax_line_no;
SELECT A.uom_class
FROM mtl_units_of_measure A, mtl_units_of_measure B
WHERE A.uom_code = v_unit_code
AND B.uom_code = p_tax_line_uom_code
AND A.uom_class = B.uom_class;
SELECT QUANTITY,price
FROM JAI_OPM_SO_PICK_LINES -- JAI_AR_TRX_LINES
WHERE Order_id = p_line_id;
SELECT Quantity, Unit_Selling_Price, Unit_Code, Inventory_Item_Id
FROM JAI_AR_TRX_LINES
WHERE Customer_Trx_Line_Id = p_line_id;
SELECT NVL( Exempt, 'N' )
FROM JAI_CMN_CUS_ADDRESSES
WHERE Customer_Id = p_customer_id
AND Address_Id = AddressId;
SELECT address_id from ra_site_uses_all where site_use_id=
(select Ship_To_Site_Use_Id
FROM So_Picking_Lines_All
WHERE Picking_Line_Id = p_ref_line_id);
SELECT Address_id
FROM Ra_site_uses_all where site_use_id in
(select ship_to_site_use_id from RA_Customer_Trx_All
WHERE Customer_trx_Id in (select customer_trx_id from
ra_customer_trx_lines_all where customer_trx_line_id = p_ref_line_id));
SELECT NVL( COUNT( * ), 0 )
FROM JAI_CMN_MATCH_TAXES
WHERE Ref_Line_Id = p_ref_line_id
AND Subinventory = p_subinventory
AND Receipt_Id = p_receipt_id
AND Order_Invoice = 'O';
SELECT NVL( COUNT( * ), 0 )
FROM JAI_CMN_MATCH_TAXES
WHERE Ref_Line_Id = p_ref_line_id
AND Receipt_Id = p_receipt_id
AND Order_Invoice = 'I';
SELECT SUM( NVL( Tax_Amount, 0 ) )
FROM JAI_CMN_MATCH_TAXES
WHERE Subinventory = p_subinventory
AND Ref_Line_Id = p_ref_line_id
AND Tax_Line_No = line_no;
SELECT SUM( NVL( Tax_Amount, 0 ) )
FROM JAI_CMN_MATCH_TAXES
WHERE Ref_Line_Id = p_ref_line_id
AND Tax_Line_No = line_no;
SELECT SUM( NVL( Tax_Amount, 0 ) ) tax_amount, SUM( NVL( Base_Tax_Amount, 0 ) ) base_tax_amount,
SUM( NVL( Func_Tax_Amount, 0 ) ) func_tax_amount
FROM JAI_CMN_MATCH_TAXES
WHERE Ref_Line_Id = p_ref_line_id
AND Tax_Line_No = line_no
AND Receipt_ID IS NOT NULL;
SELECT rate_per_unit ,PRIMARY_UOM_CODE
FROM JAI_CMN_RG_23D_TRXS
WHERE register_id = p_receipt_id;
Select order_um1,exchange_rate,item_id
From op_ordr_dtl
WHERE bol_id = p_ref_line_id
and bolline_no = p_line_no;
INSERT INTO JAI_CMN_MATCH_TAXES(MATCH_TAX_ID, REF_LINE_ID,
SUBINVENTORY,
TAX_LINE_NO,
PRECEDENCE_1,
PRECEDENCE_2,
PRECEDENCE_3,
PRECEDENCE_4,
PRECEDENCE_5,
PRECEDENCE_6, -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
PRECEDENCE_7,
PRECEDENCE_8,
PRECEDENCE_9,
PRECEDENCE_10,
TAX_ID,
TAX_RATE,
QTY_RATE,
UOM,
TAX_AMOUNT,
BASE_TAX_AMOUNT,
FUNC_TAX_AMOUNT,
TOTAL_TAX_AMOUNT,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
RECEIPT_ID,
ORDER_INVOICE )
VALUES ( JAI_CMN_MATCH_TAXES_S.nextval, p_ref_line_id,
p_subinventory,
z.lno,
rec.p_1,
rec.p_2,
rec.p_3,
rec.p_4,
rec.p_5,
rec.p_6, -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
rec.p_7,
rec.p_8,
rec.p_9,
rec.p_10,
rec.tax_id,
rec.tax_rate,
rec.qty_rate,
rec.uom,
0,
0,
0,
0,
SYSDATE,
UID,
SYSDATE,
UID,
UID,
p_receipt_id,
p_order_invoice );
DELETE JAI_CMN_MATCH_TAXES
WHERE Ref_Line_Id = p_ref_line_id
AND nvl(Subinventory,'###') = nvl(p_subinventory,'###')
AND receipt_id = p_receipt_id
AND Tax_Line_No = i;
UPDATE JAI_CMN_MATCH_TAXES
SET Tax_Amount = tax_amt_tab(i),
Base_Tax_Amount = tax_target_tab(i),
Func_Tax_Amount = tax_amt_tab(i) ,
--* NVL( p_curr_conv_factor, 1 ),
Total_Tax_Amount = v_cum_amount
WHERE Ref_Line_Id = p_ref_line_id
AND nvl(Subinventory,'###') = nvl(p_subinventory,'###')
AND receipt_id = p_receipt_id
AND Tax_Line_No = i;
update JAI_OPM_SO_PICK_TAXES
Set tax_amount = tax_amt_tab(i)
Where bol_id = p_ref_line_id
and bolline_no = p_line_no
AND Tax_Line_No = i;
UPDATE JAI_AR_TRX_TAX_LINES
SET Tax_Amount = rec.tax_amount,
Base_Tax_Amount = rec.base_tax_amount,
Func_Tax_Amount = rec.func_tax_amount
WHERE link_to_cust_trx_line_id = p_ref_line_id
AND Tax_Line_No = i;
UPDATE JAI_AR_TRX_TAX_LINES
SET Tax_Amount = tax_amt_tab(i),
Base_Tax_Amount = tax_target_tab(i),
Func_Tax_Amount = tax_amt_tab(i) * NVL( p_curr_conv_factor, 1 )
WHERE link_to_cust_trx_line_id = p_ref_line_id
AND Tax_Line_No = i;
v_last_update_date Date; -- := Sysdate; --Ramananda for File.Sql.35
SELECT matched_quantity
FROM JAI_AR_TRX_LINES
WHERE Customer_Trx_Line_Id = p_ref_line_id;*/
SELECT nvl(quantity * nvl(assessable_value, line_amount),0) assessable_value, line_amount, unit_code, inventory_item_id, quantity,
tax_category_id, customer_trx_id, creation_date, created_by, last_updated_by, last_update_login
FROM JAI_AR_TRX_LINES
WHERE Customer_Trx_Line_Id = p_ref_line_id;
SELECT NVL( MAX( Tax_Line_No ), 0 )
FROM JAI_AR_TRX_TAX_LINES
WHERE Link_To_Cust_Trx_Line_Id = p_link_to_cust_trx_line_id;
SELECT NVL( MAX( Tax_Line_No ), 0 )
FROM JAI_CMN_MATCH_TAXES
WHERE Ref_Line_Id = p_ref_line_id;
SELECT *
FROM JAI_AR_TRX_TAX_LINES
WHERE Link_To_Cust_Trx_Line_Id = p_link_to_cust_trx_line_id
AND Tax_Line_No > nvl(v_tax_line_no,0)
ORDER BY Tax_Line_No;
CURSOR Get_Receipt_taxes IS SELECT a.tax_id tax_id_po, a.tax_line_no tax_line_no_po, c.tax_id , c.tax_line_no,
a.precedence_1 p_1, a.precedence_2 p_2, a.precedence_3 p_3, a.precedence_4 p_4, a.precedence_5 p_5,
a.precedence_6 p_6, a.precedence_7 p_7, a.precedence_8 p_8, a.precedence_9 p_9, a.precedence_10 p_10,
c.qty_rate, c.tax_rate, c.tax_amount, c.uom
FROM JAI_PO_TAXES a, JAI_CMN_TAXES_ALL b, JAI_RCV_LINE_TAXES c
WHERE c.tax_line_no > nvl(v_tax_line_no,0)
AND a.tax_id = b.tax_id
AND c.tax_id = b.tax_id
AND (c.shipment_line_id,a.line_location_id) = (SELECT shipment_line_id,po_line_location_id
FROM rcv_transactions
WHERE transaction_id = (select receipt_ref
from JAI_CMN_RG_23D_TRXS
where register_id = p_receipt_id))
ORDER BY c.tax_line_no;
Commented insert into JAI_CMN_MATCH_TAXES
since it is not required
base bug# 4146708 creates objects
--------------------------------------------------------------------------------------------*/
v_qty := p_qty; --Ramananda for File.Sql.35
v_last_update_date := Sysdate; --Ramananda for File.Sql.35
/* bug# 4111609 insertion not required. So code commented */
/*
INSERT INTO JAI_CMN_MATCH_TAXES(MATCH_TAX_ID, REF_LINE_ID,
SUBINVENTORY,
TAX_LINE_NO,
PRECEDENCE_1,
PRECEDENCE_2,
PRECEDENCE_3,
PRECEDENCE_4,
PRECEDENCE_5,
PRECEDENCE_6, -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
PRECEDENCE_7,
PRECEDENCE_8,
PRECEDENCE_9,
PRECEDENCE_10,
TAX_ID,
TAX_RATE,
QTY_RATE,
UOM,
TAX_AMOUNT,
BASE_TAX_AMOUNT,
FUNC_TAX_AMOUNT,
TOTAL_TAX_AMOUNT,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
RECEIPT_ID,
ORDER_INVOICE )
VALUES ( JAI_CMN_MATCH_TAXES_S.nextval, p_ref_line_id,
NULL,
rec1.tax_line_no,
rec1.p_1,
rec1.p_2,
rec1.p_3,
rec1.p_4,
rec1.p_5,
rec1.p_6, -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
rec1.p_7,
rec1.p_8,
rec1.p_9,
rec1.p_10,
rec1.tax_id,
rec1.tax_rate,
rec1.qty_rate,
rec1.uom,
0,
0,
0,
0,
SYSDATE,
UID,
SYSDATE,
UID,
UID,
p_receipt_id,
'I' );
DELETE JAI_CMN_MATCH_TAXES
WHERE Ref_Line_Id = p_ref_line_id
AND receipt_id = p_receipt_id;
jai_ar_utils_pkg.recalculate_tax('AR_LINES_UPDATE' , rec.tax_category_id , rec.customer_trx_id , p_ref_line_id,
rec.assessable_value , rec.line_amount , 1, rec.inventory_item_id ,rec.quantity,
rec.unit_code , NULL , NULL ,rec.creation_date , rec.created_by ,
v_last_update_date , rec.last_updated_by , rec.last_update_login );
SELECT
a.tax_id ,
a.tax_line_no lno ,
a.precedence_1 p_1 ,
a.precedence_2 p_2 ,
a.precedence_3 p_3 ,
a.precedence_4 p_4 ,
a.precedence_5 p_5 ,
a.precedence_6 p_6 , -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
a.precedence_7 p_7 ,
a.precedence_8 p_8 ,
a.precedence_9 p_9 ,
a.precedence_10 p_10 ,
a.qty_rate ,
-- Vijay Shankar for Bug# 3781299
decode(b.tax_type, lv_excise_cess_code, decode(p_excise_cess_cnt, 0, 0, a.tax_rate),
lv_sh_excise_cess_code, decode(p_sh_excise_cess_cnt, 0, 0, a.tax_rate), --Added by sacsethi for Bug no 5907436
a.tax_rate) tax_rate, -- a.tax_rate/*Bug 5989740 bduvarag*/
decode(b.tax_type, lv_excise_cess_code, decode(p_excise_cess_cnt, 0, 0, a.tax_amount),
lv_sh_excise_cess_code, decode(p_sh_excise_cess_cnt, 0, 0, a.tax_amount), --Added by sacsethi for Bug no 5907436
a.tax_amount) tax_amount, -- a.tax_amount ,/*Bug 5989740 bduvarag*/
a.uom ,
b.end_date valid_date ,
DECODE(aa.regime_code, jai_constants.cgst_regime, 7 -- Add by Xiao for GST refer to bug#10043656.
, jai_constants.sgst_regime, 7, -- Add by Xiao for GST refer to bug#10043656.
decode(upper(b.tax_type),'EXCISE', 1,
'ADDL. EXCISE', 1,
'OTHER EXCISE', 1,
'TDS', 2,
lv_excise_cess_code,3, -- bug#4111609 excise cess
lv_cvd_cess_code,3, -- bug#4111609 cvd cess
lv_sh_excise_cess_code,4, --added by sacsethi for budget07 enhancement
lv_sh_cvd_cess_code,4, --added by sacsethi for budget07 enhancement
/* Added for bug#8887871 */
(SELECT jrttv.tax_type
FROM jai_regime_tax_types_v jrttv
WHERE jrttv.tax_type = upper(b.tax_type)
AND jrttv.regime_code = jai_constants.vat_regime), 5,
'VAT REVERSAL', 6, 0)) tax_type_val,
b.mod_cr_Percentage ,
b.vendor_id ,
b.tax_type ,
NVL( b.rounding_factor, 0 ) rnd ,
b.adhoc_flag /* Added bug 5091874 */
, b.inclusive_tax_flag --Add by Kevin Cheng for inclusive tax Dec 12, 2007
FROM
JAI_OM_OE_SO_TAXES a ,
jai_regime_tax_types_v aa, -- Add by Xiao for GST refer to bug#10043656.
JAI_CMN_TAXES_ALL b
WHERE a.line_id = p_line_id
AND a.tax_id = b.tax_id
AND aa.tax_type(+) = b.tax_type -- Add by Xiao for GST refer to bug#10043656.
ORDER BY
a.tax_line_no;
SELECT
a.tax_id ,
a.tax_line_no lno ,
a.precedence_1 p_1 ,
a.precedence_2 p_2 ,
a.precedence_3 p_3 ,
a.precedence_4 p_4 ,
a.precedence_5 p_5 ,
a.precedence_6 p_6 ,
a.precedence_7 p_7 ,
a.precedence_8 p_8 ,
a.precedence_9 p_9 ,
a.precedence_10 p_10 ,
a.qty_rate ,
a.tax_rate tax_rate,
a.tax_amt tax_amount ,
a.tax_amt func_tax_amount,
c.transaction_uom uom,
b.end_date valid_date ,
DECODE(aa.regime_code, jai_constants.cgst_regime, 7 -- Add by Xiao for GST refer to bug#10043656.
, jai_constants.sgst_regime, 7, -- Add by Xiao for GST refer to bug#10043656.
decode(upper(b.tax_type),'EXCISE', 1,
'ADDL. EXCISE', 1,
'OTHER EXCISE', 1,
'TDS', 2,
lv_excise_cess_code,3,
lv_cvd_cess_code,3,
lv_sh_excise_cess_code,4, --added by sacsethi for budget07 enhancement
lv_sh_cvd_cess_code,4, --added by sacsethi for budget07 enhancement
(SELECT jrttv.tax_type
FROM jai_regime_tax_types_v jrttv
WHERE jrttv.tax_type = upper(b.tax_type)
AND jrttv.regime_code = jai_constants.vat_regime), 5,
'VAT REVERSAL', 6, 0)) tax_type_val,
b.mod_cr_Percentage ,
b.vendor_id ,
b.tax_type ,
NVL( b.rounding_factor, 0 ) rnd ,
b.adhoc_flag
, b.inclusive_tax_flag --Add by Kevin for inclusive tax Dec 12, 2007
FROM
jai_cmn_document_taxes a ,
jai_regime_tax_types_v aa , -- Add by Xiao for GST refer to bug#10043656.
jai_cmn_taxes_all b ,
jai_mtl_trxs c /* jai_mtl_trxs_temp is modified as jai_mtl_trxs by Vijay for ReArch. bug#2942973 */
WHERE
a.source_doc_line_id = p_line_id AND
a.tax_id = b.tax_id AND
aa.tax_type(+) = b.tax_type AND -- Add by Xiao for GST refer to bug#10043656.
a.source_doc_type = 'INTERORG_XFER' AND
a.source_doc_line_id = c.transaction_temp_id
ORDER BY
a.tax_line_no;
SELECT a.tax_id, a.tax_line_no lno,
a.precedence_1 p_1, a.precedence_2 p_2, a.precedence_3 p_3, a.precedence_4 p_4, a.precedence_5 p_5,
a.precedence_6 p_6, a.precedence_7 p_7, a.precedence_8 p_8, a.precedence_9 p_9, a.precedence_10 p_10,
a.qty_rate,
-- Vijay Shankar for Bug# 3781299
decode(b.tax_type, lv_excise_cess_code, decode(p_excise_cess_cnt, 0, 0, a.tax_rate),
lv_sh_excise_cess_code, decode(p_sh_excise_cess_cnt, 0, 0, a.tax_rate),
a.tax_rate) tax_rate, -- a.tax_rate
decode(b.tax_type, lv_excise_cess_code, decode(p_excise_cess_cnt, 0, 0, a.tax_amount), /*Bug 5989740 bduvarag*/
lv_sh_excise_cess_code, decode(p_sh_excise_cess_cnt, 0, 0, a.tax_amount), /*Bug 5989740 bduvarag*/
a.tax_amount) tax_amount, -- a.tax_amount ,
a.uom, b.end_date valid_date,
DECODE(aa.regime_code, jai_constants.cgst_regime, 7 --Added by Jia for GST Bug#10043656 on 2010/09/10
, jai_constants.sgst_regime, 7 --Added by Jia for GST Bug#10043656 on 2010/09/10
, decode(upper(b.tax_type),'EXCISE', 1,
'ADDL. EXCISE', 1,
'OTHER EXCISE', 1,
'TDS', 2,
lv_excise_cess_code,3, -- bug#4111609 excise cess
lv_cvd_cess_code,3, -- bug#4111609 cvd cess
lv_sh_excise_cess_code,4, /*Bug 5989740 bduvarag*/
lv_sh_cvd_cess_code,4,
/* Added for bug#8887871 */
(SELECT jrttv.tax_type
FROM jai_regime_tax_types_v jrttv
WHERE jrttv.tax_type = upper(b.tax_type)
AND jrttv.regime_code = jai_constants.vat_regime), 5,
'VAT REVERSAL', 6, 0)) tax_type_val,
b.mod_cr_Percentage, b.vendor_id, b.tax_type, NVL( b.rounding_factor, 0 ) rnd, b.adhoc_flag /* Added for bug 5091874*/
, b.inclusive_tax_flag --Add by Kevin Cheng for inclusive tax Dec 12, 2007
FROM JAI_AR_TRX_TAX_LINES a, JAI_CMN_TAXES_ALL b,
jai_regime_tax_types_v aa -- Add by Xiao for GST refer to bug#10043656.
WHERE a.link_to_cust_trx_line_id = p_line_id
AND a.tax_id = b.tax_id
AND aa.tax_type(+) = b.tax_type -- Add by Xiao for GST refer to bug#10043656.
ORDER BY a.tax_line_no;
SELECT A.uom_class
FROM mtl_units_of_measure A, mtl_units_of_measure B
WHERE A.uom_code = v_unit_code
AND B.uom_code = p_tax_line_uom_code
AND A.uom_class = B.uom_class;
SELECT
decode(nvl(quantity,0),0,1,quantity) ,
selling_Price ,
unit_code ,
inventory_item_id
, nvl(line_amount, 0) line_amount --Add by Kevin Cheng for inclusive tax Dec 12, 2007
, nvl(assessable_value,0)*nvl(quantity,0) assessable_value --Add by Kevin Cheng for inclusive tax Dec 12, 2007
, nvl(vat_assessable_value,0) vat_assessable_vale --Add by Kevin Cheng for inclusive tax Dec 12, 2007
, nvl(vat_reversal_price, 0) vat_reversal_price --added for bug#8887871
, nvl(gst_assessable_value,0) gst_assessable_value --Add by Xiao for GST refer to bug#10043656.
FROM
JAI_OM_OE_SO_LINES
WHERE line_id = p_line_id;
SELECT Quantity, Unit_Selling_Price, Unit_Code, Inventory_Item_Id
, nvl(line_amount, 0) line_amount --Add by Kevin Cheng for inclusive tax Dec 12, 2007
, nvl(assessable_value,0)*nvl(quantity,0) assessable_value --Add by Kevin Cheng for inclusive tax Dec 12, 2007
, nvl(vat_assessable_value,0) vat_assessable_vale --Add by Kevin Cheng for inclusive tax Dec 12, 2007
, nvl(vat_reversal_price, 0) vat_reversal_price --added for bug#8887871
, nvl(gst_assessable_value,0) gst_assessable_value --Add by Xiao for GST refer to bug#10043656.
FROM JAI_AR_TRX_LINES
WHERE Customer_Trx_Line_Id = p_line_id;
/* SELECT transaction_Quantity quantity, Selling_Price unit_Selling_price , uom_code Unit_Code, Inventory_Item_Id */
SELECT decode(quantity,0,1,quantity), selling_price unit_Selling_price , transaction_uom Unit_Code, Inventory_Item_Id, vat_assessable_value
, nvl(selling_price,0)*nvl(quantity,0) line_amount --Add by Kevin Cheng for inclusive tax Dec 12, 2007
, nvl(assessable_value,0) assessable_value --Add by Kevin Cheng for inclusive tax Dec 12, 2007
, nvl(gst_assessable_value,0) gst_assessable_value --Add by Xiao for GST refer to bug#10043656.
FROM jai_mtl_trxs /* jai_mtl_trxs_temp is modified as jai_mtl_trxs by Vijay for ReArch. bug#2942973 */
WHERE transaction_temp_id = p_line_id;
SELECT NVL( Exempt, 'N' )
FROM JAI_CMN_CUS_ADDRESSES
WHERE Customer_Id = p_customer_id
AND Address_Id = AddressId;
SELECT cust_acct_site_id address_id
from hz_cust_site_uses_all where site_use_id=
(select SHIP_TO_LOCATION_ID
FROM wsh_delivery_details
WHERE delivery_detail_id = p_ref_line_id);
SELECT cust_acct_site_id address_id
FROM hz_cust_site_uses_all where site_use_id in
(select ship_to_site_use_id from RA_Customer_Trx_All
WHERE Customer_trx_Id in (select customer_trx_id from
ra_customer_trx_lines_all where customer_trx_line_id = p_ref_line_id));
SELECT NVL( COUNT( * ), 0 )
FROM JAI_CMN_MATCH_TAXES
WHERE Ref_Line_Id = p_ref_line_id
AND Subinventory = p_subinventory
AND Receipt_Id = p_receipt_id
AND Order_Invoice = 'O';
SELECT NVL( COUNT( * ), 0 )
FROM JAI_CMN_MATCH_TAXES
WHERE Ref_Line_Id = p_ref_line_id
AND Receipt_Id = p_receipt_id
AND Order_Invoice = 'I';
SELECT SUM( NVL( Tax_Amount, 0 ) )
FROM JAI_CMN_MATCH_TAXES
WHERE Subinventory = p_subinventory
AND Ref_Line_Id = p_ref_line_id
AND Tax_Line_No = line_no;
SELECT SUM( NVL( Tax_Amount, 0 ) )
FROM JAI_CMN_MATCH_TAXES
WHERE Ref_Line_Id = p_ref_line_id
AND Tax_Line_No = line_no;
SELECT SUM( NVL( Tax_Amount, 0 ) ) tax_amount, SUM( NVL( Base_Tax_Amount, 0 ) ) base_tax_amount,
SUM( NVL( Func_Tax_Amount, 0 ) ) func_tax_amount
FROM JAI_CMN_MATCH_TAXES
WHERE Ref_Line_Id = p_ref_line_id
AND Tax_Line_No = line_no
AND Receipt_ID IS NOT NULL;
SELECT --rate_per_unit/*bduvarag for the bug#6038720*/
/* Added the following and commented the above for bug#6022072 */
--added nvl(additional_cvd,0) for bug#9288359
(nvl(duty_amount,0) + nvl(additional_cvd,0)) / decode(DECODE( TRANSACTION_TYPE,
'MCR', oth_receipt_quantity,quantity_received)
,0,1,DECODE( TRANSACTION_TYPE, 'MCR', oth_receipt_quantity,quantity_received)),
-- added the decode transaction_type for bug8366387
-- ssumaith - removed the + additional_cvd in the above line
receipt_num ,
decode(DECODE( TRANSACTION_TYPE, 'MCR', oth_receipt_quantity,quantity_received) ,0,1,DECODE( TRANSACTION_TYPE, 'MCR', oth_receipt_quantity,quantity_received)) quantity_received -- added the decode transaction_type for bug8366387 ,
--other_tax_credit / decode(quantity_received ,0,1,quantity_received) -- bug#4111609
FROM JAI_CMN_RG_23D_TRXS
WHERE register_id = p_receipt_id;
SELECT tax_type , credit
FROM JAI_CMN_RG_OTHERS
WHERE source_register_id = p_receipt_id and
source_type =3; --in (3,4) ; commented 4 for bug#9406919
SELECT source_line_id
FROM wsh_delivery_details
WHERE delivery_detail_id = cp_delivery_detail_id;
SELECT count(1)
FROM JAI_OM_OE_SO_TAXES a, JAI_CMN_TAXES_ALL b
WHERE a.line_id = cp_order_line_id
AND a.tax_id = b.tax_id
AND b.tax_type = p_Cess_type_code;/*Bug 5989740 bduvarag*/
SELECT count(1)
FROM JAI_AR_TRX_TAX_LINES a, JAI_CMN_TAXES_ALL b
WHERE a.link_to_cust_trx_line_id = cp_customer_trx_line_id
AND a.tax_id = b.tax_id
AND b.tax_type = p_Cess_type_code ;/*Bug 5989740 bduvarag*/
SELECT shipment_line_id
FROM rcv_transactions
WHERE transaction_id = cp_transaction_id;
SELECT 1
FROM JAI_CMN_RG_23D_TRXS
WHERE register_id = p_transaction_id
AND nvl(other_tax_credit,0) <> 0;
the excise duty per unit and the same is updated back to the
localization AR table.
3. 05/05/2004 Aiyer - Bug # 3611625, FileVersion 619.1
Issue:-
For a trading organization when a delivery is split into two deliveries,
such that the line contains adhoc type of non excise taxes then it is observed that
the tax amounts do not get apportioned based on the quantity applied and the orginal line
quantity.
Reason:-
This was happening because prior to this fix the tax amounts in case of Adhoc non excise
type of tax used to be defaulted from JAI_OM_OE_SO_TAXES .
Now when a delivery detail is split after pick release, then the delivery detail get splits
but not the line.
Hence the tax amount's, which were picked up from the JAI_OM_OE_SO_TAXES did not get apportioned
properly.
Fix:-
In case of Adhoc non excise type of taxes, the tax amounts get apportioned based on the p_line_quantity
and v_original_quantity.
Dependency Due To This Bug:-
None
4 21/07/2004 Vijay Shankar for Budget2004 Bug# 3781299, Version: 115.1
Education Cess has been introduced in Budget 2004, which will be calculated on all excise taxes.
Code is modified to make Education Cess Tax_rate as 0 in MAIN Cursor itself if
JAI_CMN_TAXES_ALL.stform_type='EXCISE - CESS' and 'EXCISE-CESS'/'CVD-CESS' exist in Receipt
Separate function is written to check(1) whether Cess tax exist in sales order/AR Manual Invoice line,
if exists then check(2) for ExciseCess tax is receipts, If exists then return 1 else 0. If any of the
check (1),(2) fails then function returns -1.
Function excise_cess_check is added with this code change. Also Cursors oe_tax_cur and ar_tax_cur are modified
NO DEPENDANCY
5 17/09/2004 Bug#3896539, Version: 115.2
Modified the values assigned to Variables lv_excise_cess_code and lv_cvd_cess_code, so that they dont contain
empty spaces
6 10/01/2005 rallamse bug#4111609, Version 115.3
When Match receipts happens, in addition to excise , education cess also needs to be matched to the sales order / invoice
This has been done by makign code changes at various places in the procedure.
This fix does not introduce dependency on this object , but this patch cannot be sent alone to the CT
because it relies on the alter done and the new tables created as part of the education cess enhancement
bug# 4146708 creates the objects
7 06/08/2005 Aiyer bug#4539813, Version 120.3 (Forward porting fix done in the bug 4284335)
Issue:-
Excise Education cess taxes are not being recalculated correctly in some cases.
Reason:-
This is a enhancement to the existing education cess code
Here if the other_tax_credit in jai_cmn_rg_23d_trxs is <> 0 then the tax_rate and precedences can be assigned as null
and the tax rate need not be considered.
However if the other_tax_credit in jai_cmn_rg_23d_trxs is = 0 then the tax rate needs to be considered for recalculation.
Fix:-
Modified the code to set the tax rate and precedences according to the other_tax_rate column in jai_cmn_rg_23d_trxs based on a IF condition.
Dependency Introduced due to this bug:-
None
8 08/05/2007 Made changes for InterOrg bug 6030615
9 09/10/2007 CSahoo for bug#6487182, File Version 120.12
R12RUP04-ST1: MATCHING RESULTS IN INCORRECT NEGATIVE TAX FOR ZERO RATE TAX CODE
Modified the IF condition to "tax_rate_tab( i ) NOT IN (0,-1)"
10 12/19/2007 Kevin Cheng Update the logic for inclusive tax calculation
11 18-feb-2008 ssumaith - bug#6817615
duty amount field in the jai_cmn_rg23d_trxs table qas already
including additional_cvd. Adding additional_cvd again is causing the problem.
12 03/26/2008 Kevin Cheng bug#6881225
Initialize pl/sql table Tax_Amt_Tab() to 0 in the begining of initialization loop,
in case there is no value for this table. Otherwise, a "no data found" exception will
be thrown out in later calculation loops.
13 03/26/2008 Kevin Cheng bug#6915049
Add statement ln_tax_amt_nr:=lt_tax_amt_non_rate_tab(i) in the ELSE branch for
adhoc and UOM related taxes, so lt_tax_amt_non_rate_tab(i) won't lose its value
in later assignment statement lt_tax_amt_non_rate_tab(i):=ln_tax_amt_nr.
14 04/04/2008 Kevin Cheng bug#6936009
Remove round function for those temporary variables, so the calculation result will
be more pricise, solving the decimal fraction issue.
11 25-feb-2010 vkaranam for bug#9406919
Issue:
Cess/SHE Cess amount is updated wrongly in the ship confirm localized
for trading organization.
Fix:
source_type condition in cur_cess_Duty_Rate_Cur has been changed from
source_type in (3,4) to source_type =3;
INSERT INTO JAI_CMN_MATCH_TAXES(MATCH_TAX_ID, REF_LINE_ID,
SUBINVENTORY,
TAX_LINE_NO,
PRECEDENCE_1,
PRECEDENCE_2,
PRECEDENCE_3,
PRECEDENCE_4,
PRECEDENCE_5,
PRECEDENCE_6, -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
PRECEDENCE_7,
PRECEDENCE_8,
PRECEDENCE_9,
PRECEDENCE_10,
TAX_ID,
TAX_RATE,
QTY_RATE,
UOM,
TAX_AMOUNT,
BASE_TAX_AMOUNT,
FUNC_TAX_AMOUNT,
TOTAL_TAX_AMOUNT,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
RECEIPT_ID,
ORDER_INVOICE )
VALUES ( JAI_CMN_MATCH_TAXES_S.nextval, p_ref_line_id,
p_subinventory,
rec.lno,
rec.p_1,
rec.p_2,
rec.p_3,
rec.p_4,
rec.p_5,
rec.p_6,
rec.p_7,
rec.p_8,
rec.p_9,
rec.p_10,
rec.tax_id,
rec.tax_rate,
rec.qty_rate,
rec.uom,
0,
0,
0,
0,
SYSDATE,
UID,
SYSDATE,
UID,
UID,
p_receipt_id,
p_order_invoice );
INSERT INTO JAI_CMN_MATCH_TAXES(MATCH_TAX_ID, REF_LINE_ID,
SUBINVENTORY,
TAX_LINE_NO,
PRECEDENCE_1,
PRECEDENCE_2,
PRECEDENCE_3,
PRECEDENCE_4,
PRECEDENCE_5,
PRECEDENCE_6, -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
PRECEDENCE_7,
PRECEDENCE_8,
PRECEDENCE_9,
PRECEDENCE_10,
TAX_ID,
TAX_RATE,
QTY_RATE,
UOM,
TAX_AMOUNT,
BASE_TAX_AMOUNT,
FUNC_TAX_AMOUNT,
TOTAL_TAX_AMOUNT,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
RECEIPT_ID,
ORDER_INVOICE )
VALUES ( JAI_CMN_MATCH_TAXES_S.nextval, p_ref_line_id,
p_subinventory,
rec.lno,
rec.p_1,
rec.p_2,
rec.p_3,
rec.p_4,
rec.p_5,
rec.p_6, -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
rec.p_7,
rec.p_8,
rec.p_9,
rec.p_10,
rec.tax_id,
rec.tax_rate,
rec.qty_rate,
rec.uom,
0,
0,
0,
0,
SYSDATE,
UID,
SYSDATE,
UID,
UID,
p_receipt_id,
p_order_invoice );
INSERT INTO Jai_cmn_match_Taxes( match_tax_id, --6447097
REF_LINE_ID,
SUBINVENTORY,
TAX_LINE_NO,
PRECEDENCE_1,
PRECEDENCE_2,
PRECEDENCE_3,
PRECEDENCE_4,
PRECEDENCE_5,
TAX_ID,
TAX_RATE,
QTY_RATE,
UOM,
TAX_AMOUNT,
BASE_TAX_AMOUNT,
FUNC_TAX_AMOUNT,
TOTAL_TAX_AMOUNT,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
RECEIPT_ID,
ORDER_INVOICE ,
PRECEDENCE_6,
PRECEDENCE_7,
PRECEDENCE_8,
PRECEDENCE_9,
PRECEDENCE_10
)
VALUES ( jai_cmn_match_taxes_s.nextval,--6447097
p_ref_line_id,
p_subinventory,
rec.lno,
rec.p_1,
rec.p_2,
rec.p_3,
rec.p_4,
rec.p_5,
rec.tax_id,
rec.tax_rate,
rec.qty_rate,
rec.uom,
0,
0,
0,
0,
SYSDATE,
FND_GLOBAl.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
FND_GLOBAL.LOGIN_ID,
p_receipt_id,
p_order_invoice,
rec.p_6,
rec.p_7,
rec.p_8,
rec.p_9,
rec.p_10
);
DELETE JAI_CMN_MATCH_TAXES
WHERE Ref_Line_Id = p_ref_line_id
AND nvl(Subinventory,'###') = nvl(p_subinventory,'###')
AND receipt_id = p_receipt_id
AND Tax_Line_No = i;
UPDATE JAI_CMN_MATCH_TAXES
SET Tax_Amount = tax_amt_tab(i),
-- Base_Tax_Amount = tax_target_tab(i),
Base_Tax_Amount = lt_base_tax_amt_tab(i) --bug#9794853
,Func_Tax_Amount = tax_amt_tab(i) * NVL( p_curr_conv_factor, 1 ),
Total_Tax_Amount = v_cum_amount
WHERE Ref_Line_Id = p_ref_line_id
AND nvl(Subinventory,'###') = nvl(p_subinventory,'###')
AND receipt_id = p_receipt_id
AND Tax_Line_No = i;
DELETE JAI_CMN_MATCH_TAXES
WHERE Ref_Line_Id = p_ref_line_id
AND nvl(Subinventory,'###') = nvl(p_subinventory,'###')
AND receipt_id = p_receipt_id
AND Tax_Line_No = i;
UPDATE JAI_CMN_MATCH_TAXES
SET Tax_Amount = tax_amt_tab(i),
-- Base_Tax_Amount = tax_target_tab(i),
Base_Tax_Amount = lt_base_tax_amt_tab(i) --bug#9794853
,Func_Tax_Amount = tax_amt_tab(i) * NVL( p_curr_conv_factor, 1 ),
Total_Tax_Amount = v_cum_amount
WHERE Ref_Line_Id = p_ref_line_id
AND nvl(Subinventory,'###') = nvl(p_subinventory,'###')
AND receipt_id = p_receipt_id
AND Tax_Line_No = i;
UPDATE JAI_AR_TRX_TAX_LINES
SET Tax_Amount = rec.tax_amount,
Base_Tax_Amount = rec.base_tax_amount,
Func_Tax_Amount = rec.func_tax_amount
WHERE link_to_cust_trx_line_id = p_ref_line_id
AND Tax_Line_No = i;
UPDATE JAI_AR_TRX_TAX_LINES
SET Tax_Amount = tax_amt_tab(i),
-- Base_Tax_Amount = tax_target_tab(i),
Base_Tax_Amount = lt_base_tax_amt_tab(i) --bug#9794853
,Func_Tax_Amount = tax_amt_tab(i) * NVL( p_curr_conv_factor, 1 )
WHERE link_to_cust_trx_line_id = p_ref_line_id
AND Tax_Line_No = i;
UPDATE Jai_cmn_document_Taxes
SET Tax_Amt = nvl(tax_amt,0)+tax_amt_tab(i), --added nvl(tax_amt,0) for bug#8445390
Func_Tax_Amt = nvl(Func_Tax_Amt,0)+tax_amt_tab(i) * NVL( p_curr_conv_factor, 1 ) --added nvl(Func_Tax_Amt,0) for bug#8445390
WHERE source_doc_line_id = p_ref_line_id
AND source_doc_type = 'INTERORG_XFER'
AND Tax_Line_No = i;
UPDATE Jai_cmn_match_Taxes
SET Tax_Amount = tax_amt_tab(i),
-- Base_Tax_Amount = tax_target_tab(i),
Base_Tax_Amount = lt_base_tax_amt_tab(i) --bug#9794853
,Func_Tax_Amount = tax_amt_tab(i) * NVL( p_curr_conv_factor, 1 )
WHERE Ref_Line_Id = p_ref_line_id
AND receipt_id = p_receipt_id
AND Tax_Line_No = i;