JA India - Purchases - draft

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