Here's a snapshot of the Excel sheet that we shall be using in this exercise. You can create a similar sheet or Download it from here.
Now, let's begin with writing XML tags.
Tips:-
To get the Tally XML tags, enter few Stock Item records and then Export the same using option "Gateway of Tally >> Display >> List of Accounts >> Alt+E".
Next, select the following:-
Format: XML data
Type of Master: Stock Items Master
For more information on how to get the XML tags from Tally software itself, refer UDIMagic Tutorials
To begin with, we shall write XML tags to create Masters that are required before the Stock Item Masters are created.
STEP 1
Write XML tags to create UNIT masters
<MASTER TYPE="UNIT"/>
<NAME COLUMNREFERENCE="C"/>
<ISSIMPLEUNIT>Yes</ISSIMPLEUNIT>
</MASTER>
Remarks:-
a) The above XML tags instruct UDIMagic to create UNIT Masters based on Column C data in the Excel sheet.
STEP 2
Write XML tags to create STOCKGROUP masters
<MASTER TYPE="STOCKGROUP"/>
<NAME.LIST>
<NAME COLUMNREFERENCE="B"/>
<NAME.LIST>
</MASTER>
STEP 3
Write XML tags to create a single Ledger Master
<MASTER TYPE="LEDGER" SINGLERECORD="YES">
<NAME.LIST>
<NAME>Sale of Spares</NAME>
</NAME.LIST>
<PARENT>Sales Accounts</PARENT>
<ISCOSTCENTREON>No</ISCOSTCENTREON>
<AFFECTSSTOCK>Yes</AFFECTSSTOCK>
<USEFORVAT>Yes</USEFORVAT>
<TAXCLASSIFICATIONNAME>Output VAT @ 12.5%</TAXCLASSIFICATIONNAME>
</MASTER>
Remarks:-
a) The above tags instruct UDIMagic to create a single Ledger Master "Sale of Spares" irrespective of the number of data/rows in the Excel sheet.
b) This is done using the attribute SINGLERECORD="Yes".
c) VAT Rate for "Sale of Spares" has been specified as 12.5%.
STEP 4
Write XML tags to create a single Ledger Master
<MASTER TYPE="LEDGER" SINGLERECORD="YES">
<NAME.LIST>
<NAME>Sale of Oil</NAME>
</NAME.LIST>
<PARENT>Sales Accounts</PARENT>
<ISCOSTCENTREON>No</ISCOSTCENTREON>
<AFFECTSSTOCK>Yes</AFFECTSSTOCK>
<USEFORVAT>Yes</USEFORVAT>
<TAXCLASSIFICATIONNAME>Output VAT @ 15%</TAXCLASSIFICATIONNAME>
</MASTER>
Remarks:-
a) The above tags instruct UDIMagic to create a single Ledger Master "Sale of Oil" irrespective of the number of data/rows in the Excel sheet.
b) This is done using the attribute SINGLERECORD="Yes"
c) VAT Rate for "Sale of Oil" has been specified as 15%.
STEP 5
Write XML tags to create Stock Item Masters
<!-- Create StockItem Masters -->
< MASTER TYPE="STOCKITEM">
<!-- Fetch the value for NAME tag from Column A of Excel Sheet -->
<NAME.LIST>
<NAME COLUMNREFERENCE="A"/>
</NAME.LIST>
<!-- StockGroup Name is to be taken from Column B-->
<PARENT COLUMNREFERENCE="B"/>
<!-- BaseUnits is to be taken from Column C-->
<BASEUNITS COLUMNREFERENCE="C"/>
<!-- Part Number to be taken from Column D -->
<ADDITIONALNAME.LIST>
<ADDITIONALNAME COLUMNREFERENCE="D"/>
</ADDITIONALNAME.LIST>
<!-- VAT Rate to be taken from Column E -->
<RATEOFVAT COLUMNREFERENCE="E"/>
<!-- These tags relate to option SET MODIFY DEFAULT LEDGER FOR INVOICING -->
<SALESLIST.LIST>
<!-- LedgerName -->
<NAME FORMULA="=IF(+E#=12.5,"Sale of Spares","Sale of Oil")"/>
<!-- VAT Class -->
<TAXCLASSIFICATIONNAME FORMULA="=IF(+E#=12.5,"Output VAT @ 12.5%","Output VAT @ 15%")"/>
<!-- Posting Specifies the posting percentage -->
<CLASSRATE FORMULA="=+F# / 100"/>
<REMOVEZEROENTRIES>Yes</REMOVEZEROENTRIES>
</SALESLIST.LIST>
</MASTER>
Remarks:-
a) The <SALESLIST.LIST> tag is used to specify the default Ledger Name and VAT class.
b) The Excel sheet used in this exercise contains two VAT Classes viz "VAT12.5%" and "VAT 15%" which is specified in Column E.
c) The default Ledger for each Stock Item is assigned based upon the VAT rates specified in Column E.
d) We have used the FORMULA attribute to specify conditionally specify which ledger i.e. "Sale of Spares" or "Sale of Oil" is to be assigned to each Stock Item.
e) As mentioned in previous posts, there are 5 special characters which are to be written differently in XML tags. For more information, refer Exercise 8
Understanding the FORMULA attribute
To begin with, we shall first take up "How this formula can be written in MS-Excel?"
Excel Formula
=IF(E2=12.5,"Sale of Spares","Sale of Oil")
Formula used in XML Tags
<NAME FORMULA=IF(E#=12.5,"Sale of Spares","Sale of Oil"/>
No comments:
Post a Comment