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

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