ZX Use Tax Liability - draft

Description
Categories: BI Publisher
Columns: C Tax Name, C Sort Tax Name, C Invoice Tax Id, C Tax Rate, C Vendor Name, C Vendor Id, C Sort Vendor Name, C Site Code, C Site Code2, C Sort Site Code ...
Application: E-Business Tax
Source: Use Tax Liability Report (XML) - Not Supported: Reserved For Future Use
Short Name: ZXXINUTR_XML
DB package: ZX_ZXXINUTR_XMLP_PKG
SELECT  r.tax_rate_code C_TAX_NAME,
         upper(r.tax_rate_code) C_SORT_TAX_NAME,
        d.tax_code_id C_INVOICE_TAX_ID,
         r.percentage_rate C_TAX_RATE,
         v.vendor_name C_VENDOR_NAME,
         v.vendor_id C_VENDOR_ID,
         decode(:P_ORDERBY,
		'VENDOR',upper(v.vendor_name)) C_SORT_VENDOR_NAME,
         vs.vendor_site_code C_SITE_CODE,
         vs.vendor_site_code C_SITE_CODE2,
         decode(:P_ORDERBY,
		'VENDOR',decode(:P_SUMMARY_FLAG_D,
			   'Y',upper(vs.vendor_site_code))) C_SORT_SITE_CODE,
         i.invoice_id C_INVOICE_ID,
	 i.invoice_num C_INVOICE_NUMBER,
	 upper(i.invoice_num) C_SORT_INVOICE_NUMBER,
	 b.batch_name C_BATCH_NAME,
         	i.invoice_date C_INVOICE_DATE,
	 nvl(i.invoice_amount,0) C_INVOICE_AMOUNT,
         d.line_type_lookup_code C_LINE_TYPE,
	ZX_ZXXINUTR_XMLP_PKG.C_OLD_VENDOR_ID_p C_OLD_VENDOR_ID,
	ZX_ZXXINUTR_XMLP_PKG.item_amount(i.invoice_id, r.tax_rate_code, d.tax_code_id) C_TAXABLE_AMOUNT,
	ZX_ZXXINUTR_XMLP_PKG.tax_amount(:C_TAXABLE_AMOUNT, r.percentage_rate) C_TAX_AMOUNT
FROM     po_vendors v,
	 po_vendor_sites_all vs,
	 ap_invoices_all i,
                 ap_invoice_distributions_all d,
	 ap_batches_all b,
	 zx_rates_b r,
                 zx_taxes_b  t,
                 zx_regimes_b reg
WHERE    b.batch_id(+) = i.batch_id
AND      d.tax_code_id = nvl(r.source_id(+),r.tax_rate_id(+))
AND      t.tax_regime_code(+) = r.tax_regime_code
AND    t.tax(+) = r.tax
AND NVL(t.effective_from, decode(:P_REPORT_BASIS, 'GL',d.accounting_date,'INVOICE', i.invoice_date,'ENTERED',i.creation_date,sysdate)) <= decode(:P_REPORT_BASIS, 'GL',d.accounting_date,'INVOICE', i.invoice_date,'ENTERED',i.creation_date,sysdate)
AND (NVL(t.effective_to, decode(:P_REPORT_BASIS, 'GL',d.accounting_date,'INVOICE', i.invoice_date,'ENTERED',i.creation_date,sysdate)) >= decode(:P_REPORT_BASIS, 'GL',d.accounting_date,'INVOICE', i.invoice_date,'ENTERED',i.creation_date,sysdate)
        OR t.effective_to IS NULL)
AND      t.tax_type_code = 'USE'
AND      i.invoice_id = d.invoice_id + 0
AND      (i.tax_amount is null OR i.tax_amount=0)
AND      i.vendor_id = v.vendor_id
AND      i.vendor_site_id = vs.vendor_site_id
AND      i.invoice_currency_code = :P_CURR_CODE
AND      (:P_REPORT_BASIS='GL' and d.accounting_date=
                (select d2.accounting_date
                 from   ap_invoice_distributions_all d2
                 where  d2.invoice_id = d.invoice_id
                 and    d2.tax_code_id = d.tax_code_id
                 and    d2.accounting_date between
                        :P_START_DATE and :P_END_DATE
                 and    rownum < 2
               &p_org_where_d2
               )
        OR
          (:P_REPORT_BASIS != 'GL' and
                decode(:P_REPORT_BASIS,
                        'ENTERED',trunc(i.creation_date),
                        'INVOICE',trunc(i.invoice_date))
                    BETWEEN :P_START_DATE and :P_END_DATE))
AND      decode(:P_TAX_PARAM_DISP,
		'All',r.tax_rate_code,
               :P_TAX_PARAM_DISP)=r.tax_rate_code
AND      decode(:P_INV_STATUS_D,
		'PAID',i.payment_status_flag,
                'APPROVED',d.match_status_flag,
                'POSTED',d.posted_flag,
		'All','Y')=
             decode(:P_INV_STATUS_D,
			'PAID','Y',
                        'APPROVED','A',
                        'POSTED','Y',
			'All','Y')
AND      ((:P_INV_STATUS_D = 'APPROVED'
 	AND NOT EXISTS
                 		(SELECT 'Holds exists'
  		FROM ap_holds_all h
                  	WHERE h.invoice_id = i.invoice_id
                 		AND h.release_lookup_code is NULL
                                      &p_org_where_h ))
	OR
	(:P_INV_STATUS_D != 'APPROVED'))
&p_org_where_vs
&p_org_where_i
&p_org_where_d
&p_org_where_b
ORDER BY r.tax_rate_code,
	 decode(:P_ORDERBY,
		'INVOICE',null,
		'VENDOR',UPPER(v.vendor_name)),
         decode(:P_ORDERBY,
		'INVOICE',null,
		'VENDOR',UPPER(vs.vendor_site_code)),
	i.invoice_date,
	 upper(i.invoice_num),
	 i.invoice_id
Parameter Name SQL text Validation
Reporting_level
 
LOV Oracle
Reporting Context
 
LOV Oracle
Currency Code
 
LOV Oracle
Tax Use Date Basis
 
LOV Oracle
Invoice Status
 
LOV Oracle
Report Ordered By
 
LOV Oracle
From Date
 
Date
To Date
 
Date
Tax Name
 
LOV Oracle
Summarize
 
LOV Oracle
SOB