View source | View content page | Page history | Printable version   
Toolbox
Main Page
Upload file
What links here
Recent changes
Help

PDF Books
Show collection (0 pages)
Collections help

Search

Projects:Financial Invoices/Technical Documentation

Contents

Technical Documentation

AD Changes

New field in general ledger screen

Financial Management || Accounting || Setup || G/L Item || G/L Item

we will have a new field called tax category. Tax can not be chosen for asset. If we choose tax category, tax can be chosen in appropriate transaction screen. So tax will be null, if tax category choosen.

Column Req. Datatype Description Notes
C_TAXCATEGORY_ID N VARCHAR(32) Category of the tax FK: C_TAXCATEGORY


Constrain:

Financial Management || Accounting || Setup || G/L Item || G/L Item >> Accounting

we don't have any changes in this section.

New fields in invoice line screen

We will have two fields

Column Req. Datatype Description Notes
FINANCIAL_INVOICE_LINE Y CHAR (1) This flag will decide whether we are going to book the invoice line on directly general ledger or not. Default value is “N”

Display Logic: if it is “N”, product field will be shown. If it is “Y”, Account Id field will be shown.

ACCOUNT_ID N VARCHAR(32) Name of the general ledger FK: C_GLITEM

Reference: Table C_GLItem

Constrain:

Java and xsql(data) file changes:

Impact in DocLine because of new field:

We have added two new attribute in line level is called account id(it is nothing but general ledger id) and financial invoice line in the screen. However financial invoice line is a field which is nothing but a flag to make sure which attributes (product or gl) to take effect on line creation. It won't have any impact in line creation on fact table. So we don't need to add financial invoice line in DocLine.java. So we have to add only one attribute in DocLine.java as gl item id.

New attribute:

 public String m_C_Glitem_ID = "";

Existing code:

 public void loadAttributes(FieldProvider data, AcctServer vo) {
   ...
   m_M_Product_ID = data.getField("mProductId");
   m_AD_OrgTrx_ID = data.getField("adOrgtrxId");
   ...

New Code:

  public void loadAttributes(FieldProvider data, AcctServer vo) {
   ...
   m_M_Product_ID = data.getField("mProductId");
   m_C_Glitem_ID = data.getField("mCGlitemId");
   m_AD_OrgTrx_ID = data.getField("adOrgtrxId");
   ...

Usage of this flag:

We will check whether end user choose product or general ledger to get accout in DocLine_Invoice.java where this flag will be useful.

Reference in DocLine_Invoice.java

   //GL Item directly from Invoice Line
   else if (m_M_Product_ID.equals("") && !m_C_Glitem_ID.equals("")) {


Impact in Invoice Line creation on Fact:

product account is fetched in four places in DocInvoice from DocLine_Invoice. We have now introduced direct posting on chosen general ledger account if finacial invoice line is 'Y'. So we have to get different account id based on chosen general ledger item or product.

fact.createLine(p_lines[i], ((DocLine_Invoice)p_lines[i]).getAccount(ProductInfo.ACCTTYPE_P_Revenue, as, conn), this.C_Currency_ID, "", p_lines[i].getAmount(), Fact_Acct_Group_ID, nextSeqNo(SeqNo), DocumentType, conn);

fact.createLine(p_lines[i], ((DocLine_Invoice) p_lines[i]).getAccount(ProductInfo.ACCTTYPE_P_Revenue, as, conn), this.C_Currency_ID, p_lines[i].getAmount(), "", Fact_Acct_Group_ID, nextSeqNo(SeqNo), DocumentType, conn);

fact.createLine(p_lines[i], ((DocLine_Invoice)p_lines[i]).getAccount(ProductInfo.ACCTTYPE_P_Expense, as, conn), this.C_Currency_ID, p_lines[i].getAmount(), "", Fact_Acct_Group_ID, nextSeqNo(SeqNo), DocumentType, conn);

fact.createLine(p_lines[i], ((DocLine_Invoice)p_lines[i]).getAccount(ProductInfo.ACCTTYPE_P_Expense, as, conn), this.C_Currency_ID, ”", p_lines[i].getAmount(), Fact_Acct_Group_ID, nextSeqNo(SeqNo), DocumentType, conn);

We don't have any changes in calling places(DocInvoice.java). We will have impact only on DocLine_Invoice.java where we are geeting account for each line.

Impact in getAccount method in DocLine_Invoice class:

Currently getAccount method is support only product and charge. However we are going to apply new concept called general ledger posting. So we have to modify here to accomodate our new type.

 /**
  * Line Account from Product (or Charge).
  * 
  * @param AcctType
  *          see ProoductInfo.ACCTTYPE_* (0..3)
  * @param as
  *          Accounting schema
  * @return Requested Product Account
  */
 public Account getAccount(String AcctType, AcctSchema as, ConnectionProvider conn) {
   // Charge Account
   if (m_M_Product_ID.equals("") && !m_C_Charge_ID.equals("")) {
     BigDecimal amt = new BigDecimal(-1); // Revenue (-)
     if (p_DocumentType.indexOf("AP") != -1)
       amt = new BigDecimal(+1); // Expense (+)
     Account acct = getChargeAccount(as, amt, conn);
     if (acct != null)
       return acct;
   }
   // Product Account
   return p_productInfo.getAccount(AcctType, as, conn);
 } // getAccount

Required modification: We have to add one more condition to check business user whether choose gl item or not. If they choose gl item, we have to return those accounts instead of product account. Credit account of gl item is equivalent to product revenue. Debit account of gl item is equivalent to product expence accout.

Modification in DocLine_Invoice.java:

   // GL Item directly from Invoice Line
   else if (m_M_Product_ID.equals("") && !m_C_Glitem_ID.equals("")) {
     try {
       DocLineInvoiceData[] data = null;
       data = DocLineInvoiceData.selectGlitem(conn, m_C_Glitem_ID, as.getC_AcctSchema_ID());
       String Account_ID = "";
       if (data == null || data.length == 0)
         return null;
       if (data.length > 0) {
         switch (Integer.parseInt(AcctType)) {
         case 1:
           // It is similar to ProductInfo.ACCTTYPE_P_Revenue
           Account_ID = data[0].glitemCreditAcct;
           break;
         case 2:
           // It is similar to ProductInfo.ACCTTYPE_P_Expense
           Account_ID = data[0].glitemDebitAcct;
           break;
         }
       }
       // No account
       if (Account_ID.equals("")) {
       log4jDocLine_Invoice.warn("getAccount - NO account for m_C_Glitem_ID=" + m_C_Glitem_ID);
         return null;
       }
       // Return Account
       return Account.getAccount(conn, Account_ID);
     } catch (ServletException e) {
       log4jDocLine_Invoice.warn(e);
     }
   }

New SQLMethod in DocLineInvoice_data.xsql:

We have to fetch debit account and credit account for choosen general ledger id from given account schema. The below query does that.

  <SqlMethod name="selectGlitem" type="preparedStatement" return="multiple">
   <SqlMethodComment></SqlMethodComment>
   <Sql>
     <![CDATA[
   SELECT GLITEM_DEBIT_ACCT, GLITEM_CREDIT_ACCT
   FROM C_GLITEM_ACCT 
   WHERE C_GLITEM_id = ?
   AND C_ACCTSCHEMA_id = ?
    ]]>
    </Sql>
    <Parameter name="C_Glitem_ID"/>
    <Parameter name="C_AcctSchema_ID"/>
  </SqlMethod>

Impact in tax line creation by newly added fields:

Tax posting as an expenses instead of VAT for undeductible organization. We have to post tax amount into product expenses account earlier. If general ledger is chosen by business user, then we have to post the tax amount according to genral ledger's account. Sometimes we will have mixed of lines (some lines from product and some lines from general ledger) for same tax rate. We also have to consider of rounding the tax line amount in the case of mixed lines.

New code in DocInvoice_data.xsql:

To get credit and debit account of gl item for given tax id, invoice and account schema:

SELECT a.GLITEM_CREDIT_ACCT AS P_REVENUE_ACCT, a.GLITEM_DEBIT_ACCT AS 	P_EXPENSE_ACCT, t.TaxAmt
  FROM C_GLITEM_ACCT a, C_InvoiceLineTax t, C_InvoiceLine l
  WHERE t.C_InvoiceLine_Id = l.C_InvoiceLine_Id 
  AND l.Account_ID = a.C_GLITEM_id
  AND a.C_ACCTSCHEMA_id = ?
  AND t.C_Tax_Id= ? 
  AND t.C_Invoice_Id= ? 
  

To get total number of tax line for given tax id and invoice id:

 SELECT count(*) as TOTALLINES FROM C_InvoiceLineTax WHERE C_Tax_Id= ? AND C_Invoice_Id= ?

Old Code in DocInvoice.java (for API and APC document type):

	if (m_taxes[i].m_isTaxUndeductable) {
         DocInvoiceData[] data = null;
         data = DocInvoiceData.selectProductAcct(conn, as.getC_AcctSchema_ID(),
             m_taxes[i].m_C_Tax_ID, Record_ID);
         for (int j = 0; j < data.length; j++) {
           fact.createLine(docLine, Account.getAccount(conn, data[j].pExpenseAcct),
               this.C_Currency_ID, data[j].taxamt, "", Fact_Acct_Group_ID, nextSeqNo(SeqNo),
               DocumentType, conn);
         }
        }

New Code in DocInvoice.java(for API and APC document type):

	if (m_taxes[i].m_isTaxUndeductable) {
         computeTaxUndeductableLine(conn, as, fact, docLine, Fact_Acct_Group_ID,
             m_taxes[i].m_C_Tax_ID, m_taxes[i].getAmount());
       }
 private void computeTaxUndeductableLine(ConnectionProvider conn, AcctSchema as, Fact fact,
     DocLine docLine, String Fact_Acct_Group_ID, String taxId, String strTaxAmount) {
   int invoiceLineTaxCount = 0;
   int totalInvoiceLineTax = getTaxLineCount(conn, taxId);
   BigDecimal cumulativeTaxLineAmount = new BigDecimal(0);
   BigDecimal taxAmount = new BigDecimal(strTaxAmount.equals("") ? "0.00" : strTaxAmount);
   DocInvoiceData[] data = null;
   try {
     // We can have some lines from product or some lines from general ledger
     data = DocInvoiceData.selectProductAcct(conn, as.getC_AcctSchema_ID(), taxId, Record_ID);
     cumulativeTaxLineAmount = createLineForTaxUndeductable(invoiceLineTaxCount,
         totalInvoiceLineTax, cumulativeTaxLineAmount, taxAmount, data, conn, fact, docLine,
         Fact_Acct_Group_ID);
     invoiceLineTaxCount = data.length;
     // check whether gl item is selected instead of product in invoice line
     data = DocInvoiceData.selectGLItemAcctForTaxLine(conn, as.getC_AcctSchema_ID(), taxId,
         Record_ID);
     createLineForTaxUndeductable(invoiceLineTaxCount, totalInvoiceLineTax,
         cumulativeTaxLineAmount, taxAmount, data, conn, fact, docLine, Fact_Acct_Group_ID);
   } catch (ServletException e) {
     log4jDocInvoice.warn(e);
   }
 }
 private int getTaxLineCount(ConnectionProvider conn, String taxId) {
   DocInvoiceData[] data = null;
   try {
     data = DocInvoiceData.getTaxLineCount(conn, taxId, Record_ID);
   } catch (ServletException e) {
     log4jDocInvoice.warn(e);
   }
   if (data.length > 0) {
     return Integer.parseInt(data[0].totallines);
   }
   return 0;
 }
 private BigDecimal createLineForTaxUndeductable(int invoiceLineTaxCount, int totalInvoiceLineTax,
     BigDecimal cumulativeTaxLineAmount, BigDecimal taxAmount, DocInvoiceData[] data,
     ConnectionProvider conn, Fact fact, DocLine docLine, String Fact_Acct_Group_ID) {
   for (int j = 0; j < data.length; j++) {
     invoiceLineTaxCount++;
     // We have to adjust the amount in last line of tax
     if (invoiceLineTaxCount == totalInvoiceLineTax) {
       data[j].taxamt = taxAmount.subtract(cumulativeTaxLineAmount).toPlainString();
     }
     try {
       // currently applicable for API and APC
       if (this.DocumentType.equals(AcctServer.DOCTYPE_APInvoice)) {
         fact.createLine(docLine, Account.getAccount(conn, data[j].pExpenseAcct),
             this.C_Currency_ID, data[j].taxamt, "", Fact_Acct_Group_ID, nextSeqNo(SeqNo),
             DocumentType, conn);
       } else if (this.DocumentType.equals(AcctServer.DOCTYPE_APCredit)) {
         fact.createLine(docLine, Account.getAccount(conn, data[j].pExpenseAcct),
             this.C_Currency_ID, "", data[j].taxamt, Fact_Acct_Group_ID, nextSeqNo(SeqNo),
             DocumentType, conn);
       }
       cumulativeTaxLineAmount = cumulativeTaxLineAmount.add(new BigDecimal(data[j].taxamt));
     } catch (ServletException e) {
       log4jDocInvoice.warn(e);
     }
   }
   return cumulativeTaxLineAmount;
 }

Callout for tax population

We can get collection of tax from stored procedure of C_GETTAX. We have to add one more parameter to do that. We can go for overloading to avoid impact on existing places.

Product id is used in two places.

Instance 1)

IF(p_IsSOTrx = 'Y' AND p_Project_ID IS NOT NULL) THEN
     SELECT MAX(C_TAX_ID)
     INTO v_TaxID
     FROM C_PROJECTLINE
     WHERE C_Project_ID = p_Project_ID
       AND M_Product_ID = p_Product_ID;
     IF(v_TaxID IS NOT NULL) THEN
       RETURN v_TaxID;
     END IF;
   END IF;

We don't have any impact here since product id will be passed as null.

Instance 2)

  SELECT C_TaxCategory_ID
     INTO v_TaxCategoryID
     FROM M_Product
     WHERE M_Product_ID = p_Product_ID;

We have to replace this query with gl category id where it has to get tax category instead of product table.

    IF(p_glitem_id IS NULL) THEN
       SELECT C_TaxCategory_ID
       INTO v_TaxCategoryID
       FROM M_Product
       WHERE M_Product_ID = p_Product_ID;
     ELSE
       SELECT C_TaxCategory_ID
       INTO v_TaxCategoryID
       FROM C_GLITEM
       WHERE C_GLITEM_ID = p_glitem_id;
     END IF;

This stored proc will be called from Tax class. So we also have to add one more overload method there to accomodate gl item id.

 public static String get(ConnectionProvider conn, String M_Product_ID, String shipDate,
     String AD_Org_ID, String M_Warehouse_ID, String billC_BPartner_Location_ID,
     String shipC_BPartner_Location_ID, String C_Project_ID, String IsSOTrx, String glItemId)
     throws IOException, ServletException {
   log4jTax.debug("Tax.get");
   return TaxData.taxGet(conn, M_Product_ID, shipDate, AD_Org_ID, M_Warehouse_ID,
       billC_BPartner_Location_ID, shipC_BPartner_Location_ID, C_Project_ID, IsSOTrx, glItemId);
 }


SL_Invoice_Glitem.java is a new callout has been created and mapped for column account_id(its nothing but gl item id).

Core logic in this class:

  SLInvoiceTaxData[] data = SLInvoiceTaxData.select(this, strCInvoiceID);
     if (data != null && data.length > 0) {
       String strCTaxID = Tax.get(this, null, data[0].dateinvoiced, strADOrgID, strWharehouse,
           data[0].cBpartnerLocationId, data[0].cBpartnerLocationId, data[0].cProjectId,
           strIsSOTrx, strAccountID);
       result.append("new Array(\"inpcTaxId\", \"" + strCTaxID + "\")");
     }

Retrieved from "http://wiki.openbravo.com/wiki/Projects:Financial_Invoices/Technical_Documentation"

This page has been accessed 900 times. This page was last modified on 8 June 2012, at 05:27. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.