JA India - Purchases - draft

Description
Categories: BI Publisher
Application: Asia/Pacific Localizations
Source: India - Purchases Report (XML) - Not Supported: Reserved For Future Use
Short Name: JAINPROS_XML
DB package: JA_JAINPROS_XMLP_PKG
SELECT
	pll.ship_to_organization_id 	organization_id,
	pll.ship_to_location_id	location_id1,
	INV1.invoice_id		invoice_id,
	INV1.invoice_num		invoice_num,
	INV1.invoice_date		invoice_date,
	INV1.invoice_amount		invoice_amt,
	jipov.vendor_id		vendor_id,
	jipov.vendor_site_id		vendor_site_id,
	jitc.tax_id			tax_id,
	jitc.tax_name		tax_name,
	jitc.tax_rate		tax_rate,
	jitc.tax_type		tax_type,
	sum(jiatd.tax_amount)	tax_amount,
	JA_JAINPROS_XMLP_PKG.cf_vend_nameformula(jipov.vendor_id) CF_VEND_NAME,
	JA_JAINPROS_XMLP_PKG.cf_vend_cstformula(jipov.vendor_id, jipov.vendor_site_id) CF_VEND_CST,
	JA_JAINPROS_XMLP_PKG.cf_vend_site_codeformula(jipov.vendor_site_id) CF_VEND_SITE_CODE,
	JA_JAINPROS_XMLP_PKG.cf_reg_noformula(jipov.vendor_id, jipov.vendor_site_id) CF_REG_NO,
	JA_JAINPROS_XMLP_PKG.CP_VEND_LST_p CP_VEND_LST,
	JA_JAINPROS_XMLP_PKG.CP_VEND_VAT_p CP_VEND_VAT,
	JA_JAINPROS_XMLP_PKG.CP_VEND_STATE_p CP_VEND_STATE
FROM
	ap_invoices_all INV1,
	JAI_AP_MATCH_INV_TAXES jiatd,
	po_line_locations_all pll,
	JAI_CMN_TAXES_ALL jitc,
	JAI_CMN_VENDOR_SITES jipov
WHERE jiatd.invoice_id = INV1.invoice_id
AND jiatd.line_location_id = pll.line_location_id
AND jiatd.tax_id = jitc.tax_id
AND jipov.vendor_id = INV1.vendor_id
AND jipov.vendor_site_id = INV1.vendor_site_id
AND INV1.cancelled_date IS NULL
&P_TAX_TYPE_LEX
&P_VENDOR_SITE_LEX
&P_INVOICE_DATE_LEX
 and pll.ship_to_organization_id=:orgn_id
 and pll.ship_to_location_id=:location_id
GROUP BY
	pll.ship_to_organization_id,
	pll.ship_to_location_id,
	INV1.invoice_id,
	INV1.invoice_num,
	INV1.invoice_date,
	INV1.invoice_amount,
	jipov.vendor_id,
	jipov.vendor_site_id,
	jitc.tax_id,
	jitc.tax_name,
	jitc.tax_rate,
	jitc.tax_type
/*ORDER BY
	jipov.vendor_id, jipov.vendor_site_id, INV1.invoice_date*/
ORDER BY 1 ASC,2 ASC,7 ASC,8 ASC,3 ASC,5 ASC,4 ASC,6 ASC , jipov.vendor_id , jipov.vendor_site_id , INV1.invoice_date
Parameter Name SQL text Validation
Operating Unit
 
LOV Oracle
End Date
 
Date
Start Date
 
Date
Supplier Site
 
LOV Oracle
Supplier
 
LOV Oracle
Location
 
LOV Oracle
Organization
 
LOV Oracle
Report For
 
LOV Oracle