JA India - VAT Returns Report(1) - draft

➜ Back to Library
Description
Categories: BI Publisher
Columns: Unreg Non Cg Organization Id, Unreg Non Cg Location Id, Unreg Non Cg Tax Type, Unreg Non Cg Tax Rate, Unreg Non Cg Tax Amount, Unreg Non Cg Base Amount ...
Application: Asia/Pacific Localizations
Source: India - VAT Returns Report(1) (XML) - Not Supported: Reserved For Future Use
Short Name: JAINVATR_XML
DB package: JA_JAINVATR_XMLP_PKG 
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_d