#=======================================================================
# Writer
: Lokanadham Thandlam
#=======================================================================
/* Prepared by Lokanadham to
Cross check the Taxes*/
SELECT jrtl.tax_line_no,
jrtl.tax_name,
jrtl.tax_type,
jrtl.tax_rate,
jrtl.currency,
--
jrtl.qty_rate,
-- jrtl.uom,
jrtl.tax_amount,
jrl.qty_received,
ROUND (jrtl.tax_amount / jrl.qty_received, 4) * (:primary_qty) taxes --8.0 taxes,
--:primary_quantity,
-- NVL (mod_cr_percentage, 0)
mod_cr_percentage
FROM jai_rcv_headers jrh,
jai_rcv_lines jrl,
jai_rcv_line_taxes jrtl,
jai_cmn_taxes_all jtc
WHERE jrh.shipment_header_id IN
( SELECT rsh.shipment_header_id
FROM rcv_transactions rcv,
jai_cmn_rg_23ac_ii_trxs rg,
jai_cmn_inventory_orgs hou,
rcv_shipment_headers rsh,
mtl_system_items msi,
po_vendors po,
jai_rcv_transactions jrcv
WHERE rsh.shipment_header_id(+) = rcv.shipment_header_id
AND rsh.receipt_num = NVL (:po_receipt, rsh.receipt_num)
AND NVL (jrcv.vat_invoice_no, jrcv.excise_invoice_no) =
NVL (
:po_invoice_no,
NVL (jrcv.vat_invoice_no, jrcv.excise_invoice_no))
AND rcv.transaction_type = 'RETURN TO VENDOR'
AND rg.organization_id(+) = rcv.organization_id
AND jrcv.organization_id = msi.organization_id
AND rg.organization_id(+) = rcv.organization_id
AND hou.organization_id = rcv.organization_id
AND jrcv.transaction_id = rcv.transaction_id
AND rg.receipt_ref(+) = rcv.transaction_id
AND po.vendor_id = NVL (rg.vendor_id, rcv.vendor_id)
AND msi.inventory_item_id = jrcv.inventory_item_id
AND hou.location_id = jrcv.location_id
AND NOT EXISTS
(SELECT 1
FROM jai_cmn_rg_pla_trxs
WHERE ref_document_id = rcv.transaction_id
AND organization_id =
rcv.organization_id
AND transaction_source_num = 19)
GROUP BY rsh.shipment_header_id)
AND jrl.shipment_line_id IN
( SELECT rcv.shipment_line_id
FROM
rcv_transactions rcv,
jai_cmn_rg_23ac_ii_trxs
rg,
jai_cmn_inventory_orgs
hou,
rcv_shipment_headers
rsh,
mtl_system_items msi,
po_vendors po,
jai_rcv_transactions
jrcv
WHERE rsh.shipment_header_id(+) = rcv.shipment_header_id
AND rsh.receipt_num =
NVL (:po_receipt, rsh.receipt_num)
AND NVL (jrcv.vat_invoice_no, jrcv.excise_invoice_no) =
NVL (
:po_invoice_no,
NVL (jrcv.vat_invoice_no,
jrcv.excise_invoice_no))
AND rcv.transaction_type = 'RETURN TO VENDOR'
AND rg.organization_id(+) = rcv.organization_id
AND jrcv.organization_id = msi.organization_id
AND rg.organization_id(+) = rcv.organization_id
AND hou.organization_id = rcv.organization_id
AND jrcv.transaction_id = rcv.transaction_id
AND rg.receipt_ref(+) = rcv.transaction_id
AND po.vendor_id = NVL (rg.vendor_id, rcv.vendor_id)
AND msi.inventory_item_id = jrcv.inventory_item_id
AND hou.location_id = jrcv.location_id
AND NOT EXISTS
(SELECT 1
FROM jai_cmn_rg_pla_trxs
WHERE ref_document_id = rcv.transaction_id
AND organization_id =
rcv.organization_id
AND transaction_source_num = 19)
GROUP BY rcv.shipment_line_id)
AND jrh.shipment_header_id = jrtl.shipment_header_id
AND jrl.shipment_line_id = jrtl.shipment_line_id
AND NVL (modvat_flag, 'N') = 'Y'
AND jrtl.tax_id = jtc.tax_id
--parameters
--Primary_Qty 2
--Excise InvoiceNo R/47782
--Receipt/GRN No 30198