JA India - VAT Returns Report(1) - draft

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