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:
- C_TAX_ID IS NULL When C_TAXCATEGORY_ID IS NOT NULL
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:
- ACCOUNT_ID IS NOT NULL When FINANCIAL_INVOICE_LINE = 'Y'
- ACCOUNT_ID IS NULL When FINANCIAL_INVOICE_LINE = 'N'
- ACCOUNT_ID IS NOT NULL when M_PRODUCT_ID IS NULL
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 + "\")");
}