SELECT pll.ship_to_organization_id unreg_non_cg_organization_id,
pll.ship_to_location_id unreg_non_cg_location_id,
jitx.tax_type unreg_non_cg_tax_type,
jitx.tax_rate unreg_non_cg_tax_rate,
sum(jiatd.tax_amount*nvl(jiatd.exchange_rate,1)) unreg_non_cg_tax_amount,
sum( decode( jitx.tax_rate ,0 , jiatd.base_amount*nvl(jiatd.exchange_rate,1)
,(jiatd.tax_amount*nvl(jiatd.exchange_rate,1) *100 )/jitx.tax_rate)
) unreg_non_cg_base_amount
FROM ap_invoices_all INV,
JAI_AP_MATCH_INV_TAXES JIATD,
po_line_locations_all PLL,
po_lines_all PL,
JAI_CMN_VENDOR_SITES JIPOV,
JAI_CMN_TAXES_ALL JITX
WHERE jiatd.invoice_id = inv.invoice_id
AND jiatd.line_location_id = pll.line_location_id
AND pll.po_line_id = pl.po_line_id
AND inv.vendor_id = jipov.vendor_id
AND inv.vendor_site_id = jipov.vendor_site_id
AND jiatd.tax_id = jitx.tax_id
AND NOT EXISTS (
SELECT 1
FROM jai_rgm_item_attrib_v JRIAV
WHERE jriav.inventory_item_id = pl.item_id
AND jriav.organization_id = pll.ship_to_organization_id
AND jriav.regime_code = 'VAT'
AND jriav.attribute_code = 'ITEM CLASS'
AND jriav.attribute_value = 'CG'
)
AND inv.cancelled_date IS NULL
AND inv.invoice_date BETWEEN :p_from_date AND :p_to_date
AND jipov.vat_reg_no IS NULL
&p_regime_taxes
and pll.ship_to_organization_id=:organization_id
and pll.ship_to_location_id=:location_id
GROUP BY pll.ship_to_organization_id,
pll.ship_to_location_id,
jitx.tax_type,
jitx.tax_rate |