--DROP VIEW adempiere.JP_Invoice_NotMatchReceipt;
CREATE OR REPLACE VIEW adempiere.JP_Invoice_NotMatchReceipt AS
SELECT
	i.DOCUMENTNO			AS DOCUMENTNO,
	i.C_DOCTYPE_ID			AS C_DOCTYPE_ID	,
	i.C_DOCTYPETARGET_ID		AS C_DOCTYPETARGET_ID,
	i.DATEINVOICED			AS DATEINVOICED,
	i.DATEACCT			AS DATEACCT,
	i.C_BPARTNER_ID			AS C_BPARTNER_ID,
	i.M_PRICELIST_ID		AS M_PRICELIST_ID,
	i.PAYMENTRULE			AS PAYMENTRULE,
	i.C_PAYMENTTERM_ID		AS C_PAYMENTTERM_ID,
	i.C_CURRENCY_ID			AS C_CURRENCY_ID,
	i.C_CONVERSIONTYPE_ID		AS C_CONVERSIONTYPE_ID,
	i.DOCSTATUS			AS DOCSTATUS,
	i.POSTED			AS POSTED,
	il.AD_CLIENT_ID			AS AD_CLIENT_ID,
	il.AD_ORG_ID			AS AD_ORG_ID,
	il.AD_ORGTRX_ID			AS AD_ORGTRX_ID,
	il.C_INVOICELINE_ID		AS C_INVOICELINE_ID,
	il.C_INVOICE_ID			AS C_INVOICE_ID,
	il.LINE				AS LINE,
	il.M_PRODUCT_ID			AS M_PRODUCT_ID,
	il.QTYENTERED			AS QTYENTERED,
	il.QTYINVOICED			AS QTYINVOICED,
	il.C_UOM_ID			AS C_UOM_ID,
	il.C_TAX_ID			AS C_TAX_ID,
	il.PRICELIMIT			AS PRICELIMIT,
	il.PRICELIST			AS PRICELIST,
	il.PRICEACTUAL			AS PRICEACTUAL,
	il.PRICEENTERED			AS PRICEENTERED,
	il.LINENETAMT			AS LINENETAMT,
	il.LINETOTALAMT			AS LINETOTALAMT,
	il.TAXAMT			AS TAXAMT,
	il.RRAMT			AS RRAMT,
	il.CREATED			AS CREATED,
	il.CREATEDBY			AS CREATEDBY,
	il.UPDATED			AS UPDATED,
	il.UPDATEDBY			AS UPDATEDBY,
	p.VALUE				AS ProductValue,
	p.NAME				AS ProductName,
	p.M_PRODUCT_CATEGORY_ID		AS M_PRODUCT_CATEGORY_ID,
	p.PRODUCTTYPE			AS PRODUCTTYPE,
	coalesce(mi.QTY,0)		AS JP_MatchedQty,
	il.QTYINVOICED - coalesce(mi.QTY,0) AS JP_NotMatchQty

FROM adempiere.C_INVOICELINE il
	INNER JOIN adempiere.C_INVOICE i ON(il.C_INVOICE_ID = i.C_INVOICE_ID)
	INNER JOIN adempiere.M_PRODUCT p ON(il.M_PRODUCT_ID = P.M_PRODUCT_ID)
	LEFT OUTER JOIN (SELECT C_InvoiceLIne_ID, SUM(COALESCE(Qty,0)) AS Qty From adempiere.M_MatchInv GROUP BY C_InvoiceLine_ID) mi ON (mi.C_InvoiceLine_ID=il.C_InvoiceLine_ID)
WHERE
		i.ISSOTRX = 'N'
	--AND p.PRODUCTTYPE = 'I' /*製品タイプはレポートの抽出条件に初期値でIとして表示しておくようにする*/
	AND il.QTYINVOICED <> coalesce(mi.QTY,0)
	AND i.DOCSTATUS in ('CO','CL') 