JA India - VAT Returns Report(1) - draft

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_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
Parameter Name SQL text Validation
Registration Number
 
LOV Oracle
Organization Name
 
LOV Oracle
Location Name
 
LOV Oracle
Date From
 
Date
Date To
 
Date
JAI_REGIMES_VAT
 
LOV Oracle