CREATE OR REPLACE VIEW adempiere.JP_PP_Doc_Fact_Acct
 AS
 SELECT
    f.AD_Client_ID
    ,f.AD_Org_ID
    ,'Y' AS IsActive
    ,max(f.Created) AS Created
    , 100 AS CreatedBy
    ,max(f.Updated) AS Updated 
    , 100 AS UpdatedBy
    ,f.C_AcctSchema_ID
    ,f.Account_ID
    ,f.AD_Table_ID
    ,f.GL_Category_ID
    ,f.GL_Budget_ID
    ,f.C_Tax_ID
    ,f.M_Locator_ID
    ,f.PostingType
    ,f.C_Currency_ID
    ,sum(f.AmtSourceDR) AS AmtSourceDR
    ,sum(f.AmtSourceCR) AS AmtSourceCR
    ,sum(f.AmtAcctDR) AS AmtAcctDR
    ,sum(f.AmtAcctCR) AS AmtAcctCR
    ,f.C_UOM_ID
    ,sum(f.Qty) AS Qty
    ,f.M_Product_ID
    ,f.C_BPartner_ID
    ,f.AD_OrgTrx_ID
    ,f.C_LocFrom_ID
    ,f.C_LocTo_ID
    ,f.C_SalesRegion_ID
    ,f.C_Project_ID
    ,f.C_Campaign_ID
    ,f.C_Activity_ID
    ,f.User1_ID
    ,f.User2_ID
    ,f.A_Asset_ID
    ,f.C_SubAcct_ID
    ,f.UserElement1_ID
    ,f.UserElement2_ID
    ,f.C_ProjectPhase_ID
    ,f.C_ProjectTask_ID
    ,f.JP_Order_ID
    ,f.JP_ContractContent_ID
    ,ppp.JP_PP_Doc_ID
    ,ev.Value AS JP_Account_Value
    ,ev.Name AS JP_Account_Name
    ,pd.Value AS JP_Product_Value
    ,pd.Name AS JP_Product_Name
FROM adempiere.Fact_Acct f
	INNER JOIN adempiere.M_Production p ON (f.Record_ID = p.M_Production_ID AND f.AD_Table_ID=325)
	INNER JOIN adempiere.JP_PP_Fact ppf ON (p.JP_PP_Fact_ID = ppf.JP_PP_Fact_ID)
	INNER JOIN adempiere.JP_PP_Plan ppp ON (ppf.JP_PP_Plan_ID = ppp.JP_PP_Plan_ID)
	INNER JOIN adempiere.C_ElementValue ev ON (f.Account_ID = ev.C_ElementValue_ID)
		LEFT OUTER JOIN adempiere.M_Product pd ON (f.M_Product_ID = pd.M_Product_ID)
WHERE f.AD_Table_ID=325
GROUP BY
    f.AD_Client_ID
    ,f.AD_Org_ID
    ,f.C_AcctSchema_ID
    ,f.Account_ID
    ,f.AD_Table_ID
    ,f.GL_Category_ID
    ,f.GL_Budget_ID
    ,f.C_Tax_ID
    ,f.M_Locator_ID
    ,f.PostingType
    ,f.C_Currency_ID
    ,f.C_UOM_ID
    ,f.M_Product_ID
    ,f.C_BPartner_ID
    ,f.AD_OrgTrx_ID
    ,f.C_LocFrom_ID
    ,f.C_LocTo_ID
    ,f.C_SalesRegion_ID
    ,f.C_Project_ID
    ,f.C_Campaign_ID
    ,f.C_Activity_ID
    ,f.User1_ID
    ,f.User2_ID
    ,f.A_Asset_ID
    ,f.C_SubAcct_ID
    ,f.UserElement1_ID
    ,f.UserElement2_ID
    ,f.C_ProjectPhase_ID
    ,f.C_ProjectTask_ID
    ,f.JP_Order_ID
    ,f.JP_ContractContent_ID
    ,ppp.JP_PP_Doc_ID
    ,ev.Value
    ,ev.Name
    ,pd.Value
    ,pd.Name
 ;

ALTER TABLE adempiere.JP_PP_Doc_Fact_Acct
    OWNER TO adempiere;