Examples with MAcctSchema org.compiere.model.MAcctSchema used on opensource projects

Example 31 with MAcctSchema

use of org.compiere.model.MAcctSchema in project adempiere by adempiere.

the class WAcctViewer method actionQuery.

// sortAddItem
/**
	 *  Query
	 */
private void actionQuery() {
    //  Parameter Info
    StringBuffer para = new StringBuffer();
    //  Reset Selection Data
    m_data.C_AcctSchema_ID = 0;
    m_data.AD_Org_ID = 0;
    //  Save Selection Choices
    Listitem listitem = selAcctSchema.getSelectedItem();
    KeyNamePair kp = null;
    if (listitem != null)
        kp = (KeyNamePair) listitem.getValue();
    if (kp != null)
        m_data.C_AcctSchema_ID = kp.getKey();
    para.append("C_AcctSchema_ID=").append(m_data.C_AcctSchema_ID);
    listitem = selPostingType.getSelectedItem();
    ValueNamePair vp = null;
    if (listitem != null)
        vp = (ValueNamePair) listitem.getValue();
    else
        return;
    m_data.PostingType = vp.getValue();
    para.append(", PostingType=").append(m_data.PostingType);
    //  Document
    m_data.documentQuery = selDocument.isChecked();
    para.append(", DocumentQuery=").append(m_data.documentQuery);
    if (selDocument.isChecked()) {
        if (m_data.AD_Table_ID == 0 || m_data.Record_ID == 0)
            return;
        para.append(", AD_Table_ID=").append(m_data.AD_Table_ID).append(", Record_ID=").append(m_data.Record_ID);
    } else {
        m_data.DateFrom = selDateFrom.getValue() != null ? new Timestamp(selDateFrom.getValue().getTime()) : null;
        para.append(", DateFrom=").append(m_data.DateFrom);
        m_data.DateTo = selDateTo.getValue() != null ? new Timestamp(selDateTo.getValue().getTime()) : null;
        para.append(", DateTo=").append(m_data.DateTo);
        listitem = selOrg.getSelectedItem();
        if (listitem != null)
            kp = (KeyNamePair) listitem.getValue();
        else
            kp = null;
        if (kp != null)
            m_data.AD_Org_ID = kp.getKey();
        para.append(", AD_Org_ID=").append(m_data.AD_Org_ID);
        //
        Iterator<String> it = m_data.whereInfo.values().iterator();
        while (it.hasNext()) para.append(", ").append(it.next());
    }
    //  Save Display Choices
    m_data.displayQty = displayQty.isChecked();
    para.append(" - Display Qty=").append(m_data.displayQty);
    m_data.displaySourceAmt = displaySourceAmt.isChecked();
    para.append(", Source=").append(m_data.displaySourceAmt);
    m_data.displayDocumentInfo = displayDocumentInfo.isChecked();
    para.append(", Doc=").append(m_data.displayDocumentInfo);
    listitem = sortBy1.getSelectedItem();
    vp = null;
    if (listitem != null) {
        vp = (ValueNamePair) listitem.getValue();
        if (vp.getName() != null && vp.getName().trim().length() > 0) {
            //vp.getName();
            m_data.sortBy1 = vp.getValue();
            m_data.group1 = group1.isChecked();
            para.append(" - Sorting: ").append(m_data.sortBy1).append("/").append(m_data.group1);
        }
    }
    listitem = sortBy2.getSelectedItem();
    vp = null;
    if (listitem != null) {
        vp = (ValueNamePair) listitem.getValue();
        if (vp.getName() != null && vp.getName().trim().length() > 0) {
            //vp.getName();
            m_data.sortBy2 = vp.getValue();
            m_data.group2 = group2.isChecked();
            para.append(", ").append(m_data.sortBy2).append("/").append(m_data.group2);
        }
    }
    listitem = sortBy3.getSelectedItem();
    vp = null;
    if (listitem != null) {
        vp = (ValueNamePair) listitem.getValue();
        if (vp.getName() != null && vp.getName().trim().length() > 0) {
            //vp.getName();
            m_data.sortBy3 = vp.getValue();
            m_data.group3 = group3.isChecked();
            para.append(", ").append(m_data.sortBy3).append("/").append(m_data.group3);
        }
    }
    listitem = sortBy4.getSelectedItem();
    vp = null;
    if (listitem != null) {
        vp = (ValueNamePair) listitem.getValue();
        if (vp.getName() != null && vp.getName().trim().length() > 0) {
            //vp.getName();
            m_data.sortBy4 = vp.getValue();
            m_data.group4 = group4.isChecked();
            para.append(", ").append(m_data.sortBy4).append("/").append(m_data.group4);
        }
    }
    bQuery.setEnabled(false);
    statusLine.setValue(" " + Msg.getMsg(Env.getCtx(), "Processing"));
    log.config(para.toString());
    //  Switch to Result pane
    tabbedPane.setSelectedIndex(1);
    //  Set TableModel with Query
    RModel rmodel = m_data.query();
    m_queryData = rmodel.getRows();
    List<ArrayList<Object>> list = null;
    paging.setPageSize(PAGE_SIZE);
    if (m_queryData.size() > PAGE_SIZE) {
        list = m_queryData.subList(0, PAGE_SIZE);
        paging.setTotalSize(m_queryData.size());
        pagingPanel.setVisible(true);
    } else {
        list = m_queryData;
        paging.setTotalSize(m_queryData.size());
        pagingPanel.setVisible(false);
    }
    paging.setActivePage(0);
    ListModelTable listmodeltable = new ListModelTable(list);
    if (table.getListhead() == null) {
        Listhead listhead = new Listhead();
        listhead.setSizable(true);
        for (int i = 0; i < rmodel.getColumnCount(); i++) {
            // Replace user columns with the user selected names
            String displayColumnName = rmodel.getColumnName(i);
            ;
            String columnName;
            RColumn col = rmodel.getColumn(i);
            columnName = col.getColumnName();
            MAcctSchema as = MAcctSchema.get(Env.getCtx(), m_data.C_AcctSchema_ID);
            if (columnName.equals("User1_ID")) {
                MAcctSchemaElement ase = as.getAcctSchemaElement(MAcctSchemaElement.ELEMENTTYPE_UserList1);
                if (ase != null)
                    displayColumnName = Msg.translate(Env.getCtx(), ase.getName());
            } else if (columnName.equals("User2_ID")) {
                MAcctSchemaElement ase = as.getAcctSchemaElement(MAcctSchemaElement.ELEMENTTYPE_UserList2);
                if (ase != null)
                    displayColumnName = Msg.translate(Env.getCtx(), ase.getName());
            } else if (columnName.equals("User3_ID")) {
                MAcctSchemaElement ase = as.getAcctSchemaElement(MAcctSchemaElement.ELEMENTTYPE_UserList3);
                if (ase != null)
                    displayColumnName = Msg.translate(Env.getCtx(), ase.getName());
            } else if (columnName.equals("User4_ID")) {
                MAcctSchemaElement ase = as.getAcctSchemaElement(MAcctSchemaElement.ELEMENTTYPE_UserList4);
                if (ase != null)
                    displayColumnName = Msg.translate(Env.getCtx(), ase.getName());
            }
            Listheader listheader = new Listheader(displayColumnName);
            listheader.setTooltiptext(rmodel.getColumnName(i));
            listhead.appendChild(listheader);
        }
        table.appendChild(listhead);
    } else // Elaine 2008/07/28
    {
        Listhead listhead = table.getListhead();
        // remove existing column header
        listhead.getChildren().clear();
        // add in new column header
        for (int i = 0; i < rmodel.getColumnCount(); i++) {
            Listheader listheader = new Listheader(rmodel.getColumnName(i));
            listhead.appendChild(listheader);
        }
    }
    //
    table.getItems().clear();
    table.setItemRenderer(new WListItemRenderer());
    table.setModel(listmodeltable);
    resultPanel.invalidate();
    bQuery.setEnabled(true);
    statusLine.setValue(" " + Msg.getMsg(Env.getCtx(), "ViewerOptions"));
}

Example 32 with MAcctSchema

use of org.compiere.model.MAcctSchema in project adempiere by adempiere.

the class AcctSchemaDefaultCopy method doIt.

//	prepare
/**
	 * 	Process
	 *	@return message
	 *	@throws Exception
	 */
protected String doIt() throws Exception {
    log.info("C_AcctSchema_ID=" + p_C_AcctSchema_ID + ", CopyOverwriteAcct=" + p_CopyOverwriteAcct);
    if (p_C_AcctSchema_ID == 0)
        throw new AdempiereSystemError("C_AcctSchema_ID=0");
    MAcctSchema as = MAcctSchema.get(getCtx(), p_C_AcctSchema_ID);
    if (as.get_ID() == 0)
        throw new AdempiereSystemError("Not Found - C_AcctSchema_ID=" + p_C_AcctSchema_ID);
    MAcctSchemaDefault acct = MAcctSchemaDefault.get(getCtx(), p_C_AcctSchema_ID);
    if (acct == null || acct.get_ID() == 0)
        throw new AdempiereSystemError("Default Not Found - C_AcctSchema_ID=" + p_C_AcctSchema_ID);
    String sql = null;
    int updated = 0;
    int created = 0;
    int updatedTotal = 0;
    int createdTotal = 0;
    //	Update existing Product Category
    if (p_CopyOverwriteAcct) {
        sql = "UPDATE M_Product_Category_Acct pa " + "SET P_Revenue_Acct=" + acct.getP_Revenue_Acct() + ", P_Expense_Acct=" + acct.getP_Expense_Acct() + ", P_CostAdjustment_Acct=" + acct.getP_CostAdjustment_Acct() + ", P_InventoryClearing_Acct=" + acct.getP_InventoryClearing_Acct() + ", P_Asset_Acct=" + acct.getP_Asset_Acct() + ", P_COGS_Acct=" + acct.getP_COGS_Acct() + ", P_PurchasePriceVariance_Acct=" + acct.getP_PurchasePriceVariance_Acct() + ", P_InvoicePriceVariance_Acct=" + acct.getP_InvoicePriceVariance_Acct() + ", P_AverageCostVariance_Acct=" + acct.getP_AverageCostVariance_Acct() + ", P_TradeDiscountRec_Acct=" + acct.getP_TradeDiscountRec_Acct() + ", P_TradeDiscountGrant_Acct=" + acct.getP_TradeDiscountGrant_Acct() + ", P_WIP_Acct=" + acct.getP_WIP_Acct() + ", P_FloorStock_Acct=" + acct.getP_FloorStock_Acct() + ", P_MethodChangeVariance_Acct=" + acct.getP_MethodChangeVariance_Acct() + ", P_UsageVariance_Acct=" + acct.getP_UsageVariance_Acct() + ", P_RateVariance_Acct=" + acct.getP_RateVariance_Acct() + ", P_MixVariance_Acct=" + acct.getP_MixVariance_Acct() + ", P_Labor_Acct=" + acct.getP_Labor_Acct() + ", P_Burden_Acct=" + acct.getP_Burden_Acct() + ", P_CostOfProduction_Acct=" + acct.getP_CostOfProduction_Acct() + ", P_OutsideProcessing_Acct=" + acct.getP_OutsideProcessing_Acct() + ", P_Overhead_Acct=" + acct.getP_Overhead_Acct() + ", P_Scrap_Acct=" + acct.getP_Scrap_Acct() + ", Updated=SysDate, UpdatedBy=0 " + "WHERE pa.C_AcctSchema_ID=" + p_C_AcctSchema_ID + " AND EXISTS (SELECT * FROM M_Product_Category p " + "WHERE p.M_Product_Category_ID=pa.M_Product_Category_ID)";
        updated = DB.executeUpdate(sql, get_TrxName());
        addLog(0, null, new BigDecimal(updated), "@Updated@ @M_Product_Category_ID@");
        updatedTotal += updated;
    }
    //	Insert new Product Category
    sql = "INSERT INTO M_Product_Category_Acct " + "(M_Product_Category_ID, C_AcctSchema_ID," + " AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy," + " P_Revenue_Acct, P_Expense_Acct, P_CostAdjustment_Acct, P_InventoryClearing_Acct, P_Asset_Acct, P_CoGs_Acct," + " P_PurchasePriceVariance_Acct, P_InvoicePriceVariance_Acct, P_AverageCostVariance_Acct," + " P_TradeDiscountRec_Acct, P_TradeDiscountGrant_Acct," + " P_WIP_Acct,P_FloorStock_Acct,P_MethodChangeVariance_Acct,P_UsageVariance_Acct,P_RateVariance_Acct," + " P_MixVariance_Acct,P_Labor_Acct,P_Burden_Acct,P_CostOfProduction_Acct,P_OutsideProcessing_Acct,P_Overhead_Acct,P_Scrap_Acct) " + " SELECT p.M_Product_Category_ID, acct.C_AcctSchema_ID," + " p.AD_Client_ID, p.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0," + " acct.P_Revenue_Acct, acct.P_Expense_Acct, acct.P_CostAdjustment_Acct, acct.P_InventoryClearing_Acct, acct.P_Asset_Acct, acct.P_CoGs_Acct," + " acct.P_PurchasePriceVariance_Acct, acct.P_InvoicePriceVariance_Acct, acct.P_AverageCostVariance_Acct," + " acct.P_TradeDiscountRec_Acct, acct.P_TradeDiscountGrant_Acct," + " acct.P_WIP_Acct,acct.P_FloorStock_Acct,acct.P_MethodChangeVariance_Acct,acct.P_UsageVariance_Acct,acct.P_RateVariance_Acct," + " acct.P_MixVariance_Acct,acct.P_Labor_Acct,acct.P_Burden_Acct,acct.P_CostOfProduction_Acct,acct.P_OutsideProcessing_Acct,P_Overhead_Acct,P_Scrap_Acct " + "FROM M_Product_Category p" + " INNER JOIN C_AcctSchema_Default acct ON (p.AD_Client_ID=acct.AD_Client_ID) " + "WHERE acct.C_AcctSchema_ID=" + p_C_AcctSchema_ID + " AND NOT EXISTS (SELECT * FROM M_Product_Category_Acct pa " + "WHERE pa.M_Product_Category_ID=p.M_Product_Category_ID" + " AND pa.C_AcctSchema_ID=acct.C_AcctSchema_ID)";
    created = DB.executeUpdate(sql, get_TrxName());
    addLog(0, null, new BigDecimal(created), "@Created@ @M_Product_Category_ID@");
    createdTotal += created;
    if (//	Insert new Products
    !p_CopyOverwriteAcct) {
        sql = "INSERT INTO M_Product_Acct " + "(M_Product_ID, C_AcctSchema_ID," + " AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy," + " P_Revenue_Acct, P_Expense_Acct, P_CostAdjustment_Acct, P_InventoryClearing_Acct, P_Asset_Acct, P_CoGs_Acct," + " P_PurchasePriceVariance_Acct, P_InvoicePriceVariance_Acct, P_AverageCostVariance_Acct," + " P_TradeDiscountRec_Acct, P_TradeDiscountGrant_Acct, " + " P_WIP_Acct,P_FloorStock_Acct,P_MethodChangeVariance_Acct,P_UsageVariance_Acct,P_RateVariance_Acct," + " P_MixVariance_Acct,P_Labor_Acct,P_Burden_Acct,P_CostOfProduction_Acct,P_OutsideProcessing_Acct,P_Overhead_Acct,P_Scrap_Acct) " + "SELECT p.M_Product_ID, acct.C_AcctSchema_ID," + " p.AD_Client_ID, p.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0," + " acct.P_Revenue_Acct, acct.P_Expense_Acct, acct.P_CostAdjustment_Acct, acct.P_InventoryClearing_Acct, acct.P_Asset_Acct, acct.P_CoGs_Acct," + " acct.P_PurchasePriceVariance_Acct, acct.P_InvoicePriceVariance_Acct, acct.P_AverageCostVariance_Acct," + " acct.P_TradeDiscountRec_Acct, acct.P_TradeDiscountGrant_Acct," + " acct.P_WIP_Acct,acct.P_FloorStock_Acct,acct.P_MethodChangeVariance_Acct,acct.P_UsageVariance_Acct,acct.P_RateVariance_Acct," + " acct.P_MixVariance_Acct,acct.P_Labor_Acct,acct.P_Burden_Acct,acct.P_CostOfProduction_Acct,acct.P_OutsideProcessing_Acct,acct.P_Overhead_Acct,acct.P_Scrap_Acct " + "FROM M_Product p" + " INNER JOIN M_Product_Category_Acct acct ON (acct.M_Product_Category_ID=p.M_Product_Category_ID)" + "WHERE acct.C_AcctSchema_ID=" + p_C_AcctSchema_ID + " AND p.M_Product_Category_ID=acct.M_Product_Category_ID" + " AND NOT EXISTS (SELECT * FROM M_Product_Acct pa " + "WHERE pa.M_Product_ID=p.M_Product_ID" + " AND pa.C_AcctSchema_ID=acct.C_AcctSchema_ID)";
        created = DB.executeUpdate(sql, get_TrxName());
        addLog(0, null, new BigDecimal(created), "@Created@ @M_Product_ID@");
        createdTotal += created;
    }
    //	Update Business Partner Group
    if (p_CopyOverwriteAcct) {
        sql = "UPDATE C_BP_Group_Acct a " + "SET C_Receivable_Acct=" + acct.getC_Receivable_Acct() + ", C_Receivable_Services_Acct=" + acct.getC_Receivable_Services_Acct() + ", C_Prepayment_Acct=" + acct.getC_Prepayment_Acct() + ", V_Liability_Acct=" + acct.getV_Liability_Acct() + ", V_Liability_Services_Acct=" + acct.getV_Liability_Services_Acct() + ", V_Prepayment_Acct=" + acct.getV_Prepayment_Acct() + ", PayDiscount_Exp_Acct=" + acct.getPayDiscount_Exp_Acct() + ", PayDiscount_Rev_Acct=" + acct.getPayDiscount_Rev_Acct() + ", WriteOff_Acct=" + acct.getWriteOff_Acct() + ", NotInvoicedReceipts_Acct=" + acct.getNotInvoicedReceipts_Acct() + ", UnEarnedRevenue_Acct=" + acct.getUnEarnedRevenue_Acct() + ", NotInvoicedRevenue_Acct=" + acct.getNotInvoicedRevenue_Acct() + ", NotInvoicedReceivables_Acct=" + acct.getNotInvoicedReceivables_Acct() + ", Updated=SysDate, UpdatedBy=0 " + "WHERE a.C_AcctSchema_ID=" + p_C_AcctSchema_ID + " AND EXISTS (SELECT * FROM C_BP_Group_Acct x " + "WHERE x.C_BP_Group_ID=a.C_BP_Group_ID)";
        updated = DB.executeUpdate(sql, get_TrxName());
        addLog(0, null, new BigDecimal(updated), "@Updated@ @C_BP_Group_ID@");
        updatedTotal += updated;
    }
    // Insert Business Partner Group
    sql = "INSERT INTO C_BP_Group_Acct " + "(C_BP_Group_ID, C_AcctSchema_ID," + " AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy," + " C_Receivable_Acct, C_Receivable_Services_Acct, C_PrePayment_Acct," + " V_Liability_Acct, V_Liability_Services_Acct, V_PrePayment_Acct," + " PayDiscount_Exp_Acct, PayDiscount_Rev_Acct, WriteOff_Acct," + " NotInvoicedReceipts_Acct, UnEarnedRevenue_Acct," + " NotInvoicedRevenue_Acct, NotInvoicedReceivables_Acct) " + "SELECT x.C_BP_Group_ID, acct.C_AcctSchema_ID," + " x.AD_Client_ID, x.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0," + " acct.C_Receivable_Acct, acct.C_Receivable_Services_Acct, acct.C_PrePayment_Acct," + " acct.V_Liability_Acct, acct.V_Liability_Services_Acct, acct.V_PrePayment_Acct," + " acct.PayDiscount_Exp_Acct, acct.PayDiscount_Rev_Acct, acct.WriteOff_Acct," + " acct.NotInvoicedReceipts_Acct, acct.UnEarnedRevenue_Acct," + " acct.NotInvoicedRevenue_Acct, acct.NotInvoicedReceivables_Acct " + "FROM C_BP_Group x" + " INNER JOIN C_AcctSchema_Default acct ON (x.AD_Client_ID=acct.AD_Client_ID) " + "WHERE acct.C_AcctSchema_ID=" + p_C_AcctSchema_ID + " AND NOT EXISTS (SELECT * FROM C_BP_Group_Acct a " + "WHERE a.C_BP_Group_ID=x.C_BP_Group_ID" + " AND a.C_AcctSchema_ID=acct.C_AcctSchema_ID)";
    created = DB.executeUpdate(sql, get_TrxName());
    addLog(0, null, new BigDecimal(created), "@Created@ @C_BP_Group_ID@");
    createdTotal += created;
    //	Update Business Partner - Employee
    if (p_CopyOverwriteAcct) {
        sql = "UPDATE C_BP_Employee_Acct a " + "SET E_Expense_Acct=" + acct.getE_Expense_Acct() + ", E_Prepayment_Acct=" + acct.getE_Prepayment_Acct() + ", Updated=SysDate, UpdatedBy=0 " + "WHERE a.C_AcctSchema_ID=" + p_C_AcctSchema_ID + " AND EXISTS (SELECT * FROM C_BP_Employee_Acct x " + "WHERE x.C_BPartner_ID=a.C_BPartner_ID)";
        updated = DB.executeUpdate(sql, get_TrxName());
        addLog(0, null, new BigDecimal(updated), "@Updated@ @C_BPartner_ID@ @IsEmployee@");
        updatedTotal += updated;
    }
    //	Insert new Business Partner - Employee
    sql = "INSERT INTO C_BP_Employee_Acct " + "(C_BPartner_ID, C_AcctSchema_ID," + " AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy," + " E_Expense_Acct, E_Prepayment_Acct) " + "SELECT x.C_BPartner_ID, acct.C_AcctSchema_ID," + " x.AD_Client_ID, x.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0," + " acct.E_Expense_Acct, acct.E_Prepayment_Acct " + "FROM C_BPartner x" + " INNER JOIN C_AcctSchema_Default acct ON (x.AD_Client_ID=acct.AD_Client_ID) " + "WHERE acct.C_AcctSchema_ID=" + p_C_AcctSchema_ID + " AND NOT EXISTS (SELECT * FROM C_BP_Employee_Acct a " + "WHERE a.C_BPartner_ID=x.C_BPartner_ID" + " AND a.C_AcctSchema_ID=acct.C_AcctSchema_ID)";
    created = DB.executeUpdate(sql, get_TrxName());
    addLog(0, null, new BigDecimal(created), "@Created@ @C_BPartner_ID@ @IsEmployee@");
    createdTotal += created;
    //
    if (!p_CopyOverwriteAcct) {
        sql = "INSERT INTO C_BP_Customer_Acct " + "(C_BPartner_ID, C_AcctSchema_ID," + " AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy," + " C_Receivable_Acct, C_Receivable_Services_Acct, C_PrePayment_Acct) " + "SELECT p.C_BPartner_ID, acct.C_AcctSchema_ID," + " p.AD_Client_ID, p.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0," + " acct.C_Receivable_Acct, acct.C_Receivable_Services_Acct, acct.C_PrePayment_Acct " + "FROM C_BPartner p" + " INNER JOIN C_BP_Group_Acct acct ON (acct.C_BP_Group_ID=p.C_BP_Group_ID)" + "WHERE acct.C_AcctSchema_ID=" + //	#
        p_C_AcctSchema_ID + " AND p.C_BP_Group_ID=acct.C_BP_Group_ID" + " AND NOT EXISTS (SELECT * FROM C_BP_Customer_Acct ca " + "WHERE ca.C_BPartner_ID=p.C_BPartner_ID" + " AND ca.C_AcctSchema_ID=acct.C_AcctSchema_ID)";
        created = DB.executeUpdate(sql, get_TrxName());
        addLog(0, null, new BigDecimal(created), "@Created@ @C_BPartner_ID@ @IsCustomer@");
        createdTotal += created;
        //
        sql = "INSERT INTO C_BP_Vendor_Acct " + "(C_BPartner_ID, C_AcctSchema_ID," + " AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy," + " V_Liability_Acct, V_Liability_Services_Acct, V_PrePayment_Acct) " + "SELECT p.C_BPartner_ID, acct.C_AcctSchema_ID," + " p.AD_Client_ID, p.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0," + " acct.V_Liability_Acct, acct.V_Liability_Services_Acct, acct.V_PrePayment_Acct " + "FROM C_BPartner p" + " INNER JOIN C_BP_Group_Acct acct ON (acct.C_BP_Group_ID=p.C_BP_Group_ID)" + "WHERE acct.C_AcctSchema_ID=" + //	#
        p_C_AcctSchema_ID + " AND p.C_BP_Group_ID=acct.C_BP_Group_ID" + " AND NOT EXISTS (SELECT * FROM C_BP_Vendor_Acct va " + "WHERE va.C_BPartner_ID=p.C_BPartner_ID AND va.C_AcctSchema_ID=acct.C_AcctSchema_ID)";
        created = DB.executeUpdate(sql, get_TrxName());
        addLog(0, null, new BigDecimal(created), "@Created@ @C_BPartner_ID@ @IsVendor@");
        createdTotal += created;
    }
    //	Update Warehouse
    if (p_CopyOverwriteAcct) {
        sql = "UPDATE M_Warehouse_Acct a " + "SET W_Inventory_Acct=" + acct.getW_Inventory_Acct() + ", W_Differences_Acct=" + acct.getW_Differences_Acct() + ", W_Revaluation_Acct=" + acct.getW_Revaluation_Acct() + ", W_InvActualAdjust_Acct=" + acct.getW_InvActualAdjust_Acct() + ", Updated=SysDate, UpdatedBy=0 " + "WHERE a.C_AcctSchema_ID=" + p_C_AcctSchema_ID + " AND EXISTS (SELECT * FROM M_Warehouse_Acct x " + "WHERE x.M_Warehouse_ID=a.M_Warehouse_ID)";
        updated = DB.executeUpdate(sql, get_TrxName());
        addLog(0, null, new BigDecimal(updated), "@Updated@ @M_Warehouse_ID@");
        updatedTotal += updated;
    }
    //	Insert new Warehouse
    sql = "INSERT INTO M_Warehouse_Acct " + "(M_Warehouse_ID, C_AcctSchema_ID," + " AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy," + " W_Inventory_Acct, W_Differences_Acct, W_Revaluation_Acct, W_InvActualAdjust_Acct) " + "SELECT x.M_Warehouse_ID, acct.C_AcctSchema_ID," + " x.AD_Client_ID, x.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0," + " acct.W_Inventory_Acct, acct.W_Differences_Acct, acct.W_Revaluation_Acct, acct.W_InvActualAdjust_Acct " + "FROM M_Warehouse x" + " INNER JOIN C_AcctSchema_Default acct ON (x.AD_Client_ID=acct.AD_Client_ID) " + "WHERE acct.C_AcctSchema_ID=" + p_C_AcctSchema_ID + " AND NOT EXISTS (SELECT * FROM M_Warehouse_Acct a " + "WHERE a.M_Warehouse_ID=x.M_Warehouse_ID" + " AND a.C_AcctSchema_ID=acct.C_AcctSchema_ID)";
    created = DB.executeUpdate(sql, get_TrxName());
    addLog(0, null, new BigDecimal(created), "@Created@ @M_Warehouse_ID@");
    createdTotal += created;
    //	Update Project
    if (p_CopyOverwriteAcct) {
        sql = "UPDATE C_Project_Acct a " + "SET PJ_Asset_Acct=" + acct.getPJ_Asset_Acct() + ", PJ_WIP_Acct=" + acct.getPJ_Asset_Acct() + ", Updated=SysDate, UpdatedBy=0 " + "WHERE a.C_AcctSchema_ID=" + p_C_AcctSchema_ID + " AND EXISTS (SELECT * FROM C_Project_Acct x " + "WHERE x.C_Project_ID=a.C_Project_ID)";
        updated = DB.executeUpdate(sql, get_TrxName());
        addLog(0, null, new BigDecimal(updated), "@Updated@ @C_Project_ID@");
        updatedTotal += updated;
    }
    //	Insert new Projects
    sql = "INSERT INTO C_Project_Acct " + "(C_Project_ID, C_AcctSchema_ID," + " AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy," + " PJ_Asset_Acct, PJ_WIP_Acct) " + "SELECT x.C_Project_ID, acct.C_AcctSchema_ID," + " x.AD_Client_ID, x.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0," + " acct.PJ_Asset_Acct, acct.PJ_WIP_Acct " + "FROM C_Project x" + " INNER JOIN C_AcctSchema_Default acct ON (x.AD_Client_ID=acct.AD_Client_ID) " + "WHERE acct.C_AcctSchema_ID=" + p_C_AcctSchema_ID + " AND NOT EXISTS (SELECT * FROM C_Project_Acct a " + "WHERE a.C_Project_ID=x.C_Project_ID" + " AND a.C_AcctSchema_ID=acct.C_AcctSchema_ID)";
    created = DB.executeUpdate(sql, get_TrxName());
    addLog(0, null, new BigDecimal(created), "@Created@ @C_Project_ID@");
    createdTotal += created;
    //	Update Tax
    if (p_CopyOverwriteAcct) {
        sql = "UPDATE C_Tax_Acct a " + "SET T_Due_Acct=" + acct.getT_Due_Acct() + ", T_Liability_Acct=" + acct.getT_Liability_Acct() + ", T_Credit_Acct=" + acct.getT_Credit_Acct() + ", T_Receivables_Acct=" + acct.getT_Receivables_Acct() + ", T_Expense_Acct=" + acct.getT_Expense_Acct() + ", Updated=SysDate, UpdatedBy=0 " + "WHERE a.C_AcctSchema_ID=" + p_C_AcctSchema_ID + " AND EXISTS (SELECT * FROM C_Tax_Acct x " + "WHERE x.C_Tax_ID=a.C_Tax_ID)";
        updated = DB.executeUpdate(sql, get_TrxName());
        addLog(0, null, new BigDecimal(updated), "@Updated@ @C_Tax_ID@");
        updatedTotal += updated;
    }
    //	Insert new Tax
    sql = "INSERT INTO C_Tax_Acct " + "(C_Tax_ID, C_AcctSchema_ID," + " AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy," + " T_Due_Acct, T_Liability_Acct, T_Credit_Acct, T_Receivables_Acct, T_Expense_Acct) " + "SELECT x.C_Tax_ID, acct.C_AcctSchema_ID," + " x.AD_Client_ID, x.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0," + " acct.T_Due_Acct, acct.T_Liability_Acct, acct.T_Credit_Acct, acct.T_Receivables_Acct, acct.T_Expense_Acct " + "FROM C_Tax x" + " INNER JOIN C_AcctSchema_Default acct ON (x.AD_Client_ID=acct.AD_Client_ID) " + "WHERE acct.C_AcctSchema_ID=" + p_C_AcctSchema_ID + " AND NOT EXISTS (SELECT * FROM C_Tax_Acct a " + "WHERE a.C_Tax_ID=x.C_Tax_ID" + " AND a.C_AcctSchema_ID=acct.C_AcctSchema_ID)";
    created = DB.executeUpdate(sql, get_TrxName());
    addLog(0, null, new BigDecimal(created), "@Created@ @C_Tax_ID@");
    createdTotal += created;
    //	Update BankAccount
    if (p_CopyOverwriteAcct) {
        sql = "UPDATE C_BankAccount_Acct a " + "SET B_InTransit_Acct=" + acct.getB_InTransit_Acct() + ", B_Asset_Acct=" + acct.getB_Asset_Acct() + ", B_Expense_Acct=" + acct.getB_Expense_Acct() + ", B_InterestRev_Acct=" + acct.getB_InterestRev_Acct() + ", B_InterestExp_Acct=" + acct.getB_InterestExp_Acct() + ", B_Unidentified_Acct=" + acct.getB_Unidentified_Acct() + ", B_UnallocatedCash_Acct=" + acct.getB_UnallocatedCash_Acct() + ", B_PaymentSelect_Acct=" + acct.getB_PaymentSelect_Acct() + ", B_SettlementGain_Acct=" + acct.getB_SettlementGain_Acct() + ", B_SettlementLoss_Acct=" + acct.getB_SettlementLoss_Acct() + ", B_RevaluationGain_Acct=" + acct.getB_RevaluationGain_Acct() + ", B_RevaluationLoss_Acct=" + acct.getB_RevaluationLoss_Acct() + ", Updated=SysDate, UpdatedBy=0 " + "WHERE a.C_AcctSchema_ID=" + p_C_AcctSchema_ID + " AND EXISTS (SELECT * FROM C_BankAccount_Acct x " + "WHERE x.C_BankAccount_ID=a.C_BankAccount_ID)";
        updated = DB.executeUpdate(sql, get_TrxName());
        addLog(0, null, new BigDecimal(updated), "@Updated@ @C_BankAccount_ID@");
        updatedTotal += updated;
    }
    //	Insert new BankAccount
    sql = "INSERT INTO C_BankAccount_Acct " + "(C_BankAccount_ID, C_AcctSchema_ID," + " AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy," + " B_InTransit_Acct, B_Asset_Acct, B_Expense_Acct, B_InterestRev_Acct, B_InterestExp_Acct," + " B_Unidentified_Acct, B_UnallocatedCash_Acct, B_PaymentSelect_Acct," + " B_SettlementGain_Acct, B_SettlementLoss_Acct," + " B_RevaluationGain_Acct, B_RevaluationLoss_Acct) " + "SELECT x.C_BankAccount_ID, acct.C_AcctSchema_ID," + " x.AD_Client_ID, x.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0," + " acct.B_InTransit_Acct, acct.B_Asset_Acct, acct.B_Expense_Acct, acct.B_InterestRev_Acct, acct.B_InterestExp_Acct," + " acct.B_Unidentified_Acct, acct.B_UnallocatedCash_Acct, acct.B_PaymentSelect_Acct," + " acct.B_SettlementGain_Acct, acct.B_SettlementLoss_Acct," + " acct.B_RevaluationGain_Acct, acct.B_RevaluationLoss_Acct " + "FROM C_BankAccount x" + " INNER JOIN C_AcctSchema_Default acct ON (x.AD_Client_ID=acct.AD_Client_ID) " + "WHERE acct.C_AcctSchema_ID=" + p_C_AcctSchema_ID + " AND NOT EXISTS (SELECT * FROM C_BankAccount_Acct a " + "WHERE a.C_BankAccount_ID=x.C_BankAccount_ID" + " AND a.C_AcctSchema_ID=acct.C_AcctSchema_ID)";
    created = DB.executeUpdate(sql, get_TrxName());
    addLog(0, null, new BigDecimal(created), "@Created@ @C_BankAccount_ID@");
    createdTotal += created;
    //	Update Withholding
    if (p_CopyOverwriteAcct) {
        sql = "UPDATE C_Withholding_Acct a " + "SET Withholding_Acct=" + acct.getWithholding_Acct() + ", Updated=SysDate, UpdatedBy=0 " + "WHERE a.C_AcctSchema_ID=" + p_C_AcctSchema_ID + " AND EXISTS (SELECT * FROM C_Withholding_Acct x " + "WHERE x.C_Withholding_ID=a.C_Withholding_ID)";
        updated = DB.executeUpdate(sql, get_TrxName());
        addLog(0, null, new BigDecimal(updated), "@Updated@ @C_Withholding_ID@");
        updatedTotal += updated;
    }
    //	Insert new Withholding
    sql = "INSERT INTO C_Withholding_Acct " + "(C_Withholding_ID, C_AcctSchema_ID," + " AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy," + "	Withholding_Acct) " + "SELECT x.C_Withholding_ID, acct.C_AcctSchema_ID," + " x.AD_Client_ID, x.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0," + " acct.Withholding_Acct " + "FROM C_Withholding x" + " INNER JOIN C_AcctSchema_Default acct ON (x.AD_Client_ID=acct.AD_Client_ID) " + "WHERE acct.C_AcctSchema_ID=" + p_C_AcctSchema_ID + " AND NOT EXISTS (SELECT * FROM C_Withholding_Acct a " + "WHERE a.C_Withholding_ID=x.C_Withholding_ID" + " AND a.C_AcctSchema_ID=acct.C_AcctSchema_ID)";
    created = DB.executeUpdate(sql, get_TrxName());
    addLog(0, null, new BigDecimal(created), "@Created@ @C_Withholding_ID@");
    createdTotal += created;
    //	Update Charge
    if (p_CopyOverwriteAcct) {
        sql = "UPDATE C_Charge_Acct a " + "SET Ch_Expense_Acct=" + acct.getCh_Expense_Acct() + ", Ch_Revenue_Acct=" + acct.getCh_Revenue_Acct() + ", Updated=SysDate, UpdatedBy=0 " + "WHERE a.C_AcctSchema_ID=" + p_C_AcctSchema_ID + " AND EXISTS (SELECT * FROM C_Charge_Acct x " + "WHERE x.C_Charge_ID=a.C_Charge_ID)";
        updated = DB.executeUpdate(sql, get_TrxName());
        addLog(0, null, new BigDecimal(updated), "@Updated@ @C_Charge_ID@");
        updatedTotal += updated;
    }
    //	Insert new Charge
    sql = "INSERT INTO C_Charge_Acct " + "(C_Charge_ID, C_AcctSchema_ID," + " AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy," + " Ch_Expense_Acct, Ch_Revenue_Acct) " + "SELECT x.C_Charge_ID, acct.C_AcctSchema_ID," + " x.AD_Client_ID, x.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0," + " acct.Ch_Expense_Acct, acct.Ch_Revenue_Acct " + "FROM C_Charge x" + " INNER JOIN C_AcctSchema_Default acct ON (x.AD_Client_ID=acct.AD_Client_ID) " + "WHERE acct.C_AcctSchema_ID=" + p_C_AcctSchema_ID + " AND NOT EXISTS (SELECT * FROM C_Charge_Acct a " + "WHERE a.C_Charge_ID=x.C_Charge_ID" + " AND a.C_AcctSchema_ID=acct.C_AcctSchema_ID)";
    created = DB.executeUpdate(sql, get_TrxName());
    addLog(0, null, new BigDecimal(created), "@Created@ @C_Charge_ID@");
    createdTotal += created;
    //	Update Cashbook
    if (p_CopyOverwriteAcct) {
        sql = "UPDATE C_Cashbook_Acct a " + "SET CB_Asset_Acct=" + acct.getCB_Asset_Acct() + ", CB_Differences_Acct=" + acct.getCB_Differences_Acct() + ", CB_CashTransfer_Acct=" + acct.getCB_CashTransfer_Acct() + ", CB_Expense_Acct=" + acct.getCB_Expense_Acct() + ", CB_Receipt_Acct=" + acct.getCB_Receipt_Acct() + ", Updated=SysDate, UpdatedBy=0 " + "WHERE a.C_AcctSchema_ID=" + p_C_AcctSchema_ID + " AND EXISTS (SELECT * FROM C_Cashbook_Acct x " + "WHERE x.C_Cashbook_ID=a.C_Cashbook_ID)";
        updated = DB.executeUpdate(sql, get_TrxName());
        addLog(0, null, new BigDecimal(updated), "@Updated@ @C_Cashbook_ID@");
        updatedTotal += updated;
    }
    //	Insert new Cashbook
    sql = "INSERT INTO C_Cashbook_Acct " + "(C_Cashbook_ID, C_AcctSchema_ID," + " AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy," + " CB_Asset_Acct, CB_Differences_Acct, CB_CashTransfer_Acct," + " CB_Expense_Acct, CB_Receipt_Acct) " + "SELECT x.C_Cashbook_ID, acct.C_AcctSchema_ID," + " x.AD_Client_ID, x.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0," + " acct.CB_Asset_Acct, acct.CB_Differences_Acct, acct.CB_CashTransfer_Acct," + " acct.CB_Expense_Acct, acct.CB_Receipt_Acct " + "FROM C_Cashbook x" + " INNER JOIN C_AcctSchema_Default acct ON (x.AD_Client_ID=acct.AD_Client_ID) " + "WHERE acct.C_AcctSchema_ID=" + p_C_AcctSchema_ID + " AND NOT EXISTS (SELECT * FROM C_Cashbook_Acct a " + "WHERE a.C_Cashbook_ID=x.C_Cashbook_ID" + " AND a.C_AcctSchema_ID=acct.C_AcctSchema_ID)";
    created = DB.executeUpdate(sql, get_TrxName());
    addLog(0, null, new BigDecimal(created), "@Created@ @C_Cashbook_ID@");
    createdTotal += created;
    return "@Created@=" + createdTotal + ", @Updated@=" + updatedTotal;
}

Example 33 with MAcctSchema

use of org.compiere.model.MAcctSchema in project adempiere by adempiere.

the class AcctSchemaCopyAcct method doIt.

//	prepare
/**
	 * 	Process
	 *	@return info
	 *	@throws Exception
	 */
protected String doIt() throws Exception {
    log.info("SourceAcctSchema_ID=" + p_SourceAcctSchema_ID + ", TargetAcctSchema_ID=" + p_TargetAcctSchema_ID);
    if (p_SourceAcctSchema_ID == 0 || p_TargetAcctSchema_ID == 0)
        throw new AdempiereSystemError("ID=0");
    if (p_SourceAcctSchema_ID == p_TargetAcctSchema_ID)
        throw new AdempiereUserError("Must be different");
    MAcctSchema source = MAcctSchema.get(getCtx(), p_SourceAcctSchema_ID, null);
    if (source.get_ID() == 0)
        throw new AdempiereSystemError("NotFound Source C_AcctSchema_ID=" + p_SourceAcctSchema_ID);
    MAcctSchema target = new MAcctSchema(getCtx(), p_TargetAcctSchema_ID, get_TrxName());
    if (target.get_ID() == 0)
        throw new AdempiereSystemError("NotFound Target C_AcctSchema_ID=" + p_TargetAcctSchema_ID);
    //
    MAcctSchemaElement[] targetElements = target.getAcctSchemaElements();
    if (targetElements.length == 0)
        throw new AdempiereUserError("NotFound Target C_AcctSchema_Element");
    //	Accounting Element must be the same
    MAcctSchemaElement sourceAcctElement = source.getAcctSchemaElement(MAcctSchemaElement.ELEMENTTYPE_Account);
    if (sourceAcctElement == null)
        throw new AdempiereUserError("NotFound Source AC C_AcctSchema_Element");
    MAcctSchemaElement targetAcctElement = target.getAcctSchemaElement(MAcctSchemaElement.ELEMENTTYPE_Account);
    if (targetAcctElement == null)
        throw new AdempiereUserError("NotFound Target AC C_AcctSchema_Element");
    if (sourceAcctElement.getC_Element_ID() != targetAcctElement.getC_Element_ID())
        throw new AdempiereUserError("@C_Element_ID@ different");
    if (MAcctSchemaGL.get(getCtx(), p_TargetAcctSchema_ID) == null)
        copyGL(target);
    if (MAcctSchemaDefault.get(getCtx(), p_TargetAcctSchema_ID) == null)
        copyDefault(target);
    return "@OK@";
}

Example 34 with MAcctSchema

use of org.compiere.model.MAcctSchema in project adempiere by adempiere.

the class FrozenUnFrozenCost method doIt.

@Override
protected String doIt() throws Exception {
    //Get account schema
    MAcctSchema accountSchema = MAcctSchema.get(getCtx(), getAccountingSchemaId());
    //Get cost type
    MCostType costType = MCostType.get(getCtx(), getCostTypeId());
    //Get cost element to process
    final List<MCostElement> costElements = getCostElementId() > 0 ? Arrays.asList(MCostElement.get(getCtx(), getCostElementId())) : MCostElement.getCostElement(getCtx(), get_TrxName());
    //Iterate cost element
    costElements.stream().filter(costElement -> costElement != null).forEach(costElement -> {
        AtomicInteger records = new AtomicInteger(0);
        Arrays.stream(getProductIds()).filter(productId -> productId > 0).forEach(productId -> {
            MProduct product = MProduct.get(getCtx(), productId);
            final CostDimension costDimension = new CostDimension(product, accountSchema, costType.getM_CostType_ID(), getOrganizationId(), getWarehouseId(), 0, costElement.getM_CostElement_ID());
            Trx.run(trxName -> {
                final List<MCost> costs = costDimension.toQuery(MCost.class, trxName).list();
                costs.stream().filter(cost -> cost != null).forEach(cost -> {
                    cost.setIsCostFrozen(isCostFrozen());
                    cost.saveEx();
                    records.updateAndGet(record -> record + 1);
                });
            });
        });
        String message = "@M_CostElement_ID@ " + costElement.getName() + " @Records@ " + records.get() + " @IsCostFrozen@ = " + isCostFrozen();
        addLog(Msg.parseTranslation(getCtx(), message));
    });
    return "@OK@";
}

Example 35 with MAcctSchema

use of org.compiere.model.MAcctSchema in project adempiere by adempiere.

the class CopyCostTypeToCostType method doIt.

@Override
protected String doIt() throws Exception {
    if (getCostTypeId() == getCostTypeIdTo())
        throw new AdempiereException("@M_CostType_ID@ @NotValid@");
    MAcctSchema accountSchema = MAcctSchema.get(getCtx(), getAccountingSchemaId());
    MCostType costTypeFrom = MCostType.get(getCtx(), getCostTypeId());
    MCostType costTypeTo = MCostType.get(getCtx(), getCostTypeIdTo());
    MCostElement costElementFrom = MCostElement.get(getCtx(), getCostElementId());
    MCostElement costElementTo = MCostElement.get(getCtx(), getCostElementIdTo());
    Arrays.stream(getProductIds()).filter(productId -> productId > 0).forEach(productId -> {
        Trx.run(trxName -> {
            copyCostTypeToCostType(productId, accountSchema, costTypeFrom, costTypeTo, costElementFrom, costElementTo, trxName);
        });
    });
    return "@Ok@";
}