ER1 - Understanding business logic for custom report

[p604431]

This blog will give an overview on ER1 report (Details of Manufacture, Clearance and Duty Payable) for custom report building. Since the business user faced some mismatched number in the quantity and amount, so decided to go for custom development.

After long time back, took some time and now posted the blog. This is my understand on the ER1 report

Introduction:

ER1 (Excise register 1), it falls under CIN (Country India Version). In manufacture company's the report used to know details on total products manufactured, how much is sold and total tax payable for those goods to the tax department. It is used monthly basis.

1. 
   

   Purpose: The basic need of this report is to find out total products manufactured, cleared (finished good sent out of the plant or sold) and the different duty or tax amount need to be paid.
   

   
   

2. 
   

   List of tables used: J_1IEXCHDR, J_1IEXCDTL, VBRK, KONV, J_1IRGSUM, J_1IRG1, J_1IMTCHID AND J_1ICHIDTX

   
   

3. 
   

   Input or Selection criteria: Excise group, Plant and Excise document date.

   
   

4. 
   

   Quantity Cleared, Duty Payable Details: With the selection condition fetch the records from the item table J_1IEXCDTL with excise transaction type ‘TRNTYP’ (DLFC & OTHR). The table will give details like chapter id, material no, Quantity (MENGE), Unit of measurement (MEINS), excise duty details. The quantity that we have here is considered as Quantity Cleared.

   
   Export & Captive items: From the data that are fetched, find out the EXPORT and CAPTIVE used items.    For captive use items, its excise rate (BEDRATE) always will be zero. Whereas for export need to be found out.
   

   Get the excise header table data with respect from item details which fetched earlier and look for field EXPIND in header table. If the excise document number has value 'U' for the field EXPIND, then it is said to be export items. In the output field duty rate and duty value for EXPORT and CAPTIVE item are exempted, therefore it can made as zero duty.

   
   

5. 
   

   Quantity Manufactured: Quantity manufactured - For this detail go for J_1iRG1 table with condition like movement type (321 & 322). 322 MvT is for reversal quantity.

   
   

6. 
   

   Assessable Value: It is total value each items excluding the duty assigned on them. For this go to VBRK table with reference document number (RDOC2) from item details (J_1IEXCDTL), with document condition number (KNUMV) and fetch data from KONV table with condition Type 'JASS'.

   
   

7. 
   

   Duty & Notification availed: In this column maintain the duty text as 'CENVAT' and in respective column of Notification availed it is texted as 'MODVATABLE'. This combination changes for Export and Captive items are as 'EXPORT' and 'CAPTIVE USE' respectively replacing 'MODVATABLE'.

   
   

8. 
   

   Closing Balance: The data can be fetched from the excise table J_1iRG1BAL table. But the problem is with the periodic items. (I.e. Some item can be manufactured periodically which can be based orders, requirement etc). So because of this it is hard to get opening & closing balance for the non transactional periods. To the fact that last utilized periods closing balance should be the next utilized periods opening balance.

   
   

   To avoid this, just go for table J_1iRGSUM. This table contains cumulative receipt and issue value of items from the day when its transaction is started. Here consider all the items or materials with respect to the excise register type and excise group.

   
   

   Then do this calculation for all items to get closing balance.

   
   

   Closing balance = Cumulative receipt - Cumulative issue.

   
   

   Now you got closing balance quantity for all items. Collect these data with respect to its chapter id. We can have the items chapter id details from the tables J_1IMTCHID and J_1ICHIDTX (Contains chapter id description).

   
   

9. 
   

   Opening Balance: Before going for opening balance or final part to out put, collect all the details with respect to its chapter ids. Now we will be having Quantity Manufactured, Quantity Cleared and Closing Balance for the chapter id. Then apply this formula for each chapter id.

   
   

           Opening Balance = (Closing balance + Quantity Cleared) - Quantity Manufactured.

Now this will give details idea for ER1 report. For each chapter id you will find three line item or records in the list excluding Export and Captive use line item. Check the example screen short.

Note:

• 
  

  Correct Chapter id, Register type, material and Excise group combination should be maintained (T.Code: J1ID)

  
  


• 
  

  If new material is included or introduced, then update the table J_1iRG1BAL, for further info. Check Note 951955

  
  

Fields in the list-

Fields Name	Text
J_1IEXCDTL-CHAPID	CETSH No.
J_1IVCHID-J_1ICHT1	Description of Goods
J_1IEXCDTL-MEINS	Unit of Quantity
J_2IRG1BAL-OP_FINISH	Opening Balance
J_1IEXCDTL-MENGE	Quantity Manufactured, Quantity Cleared
J_2IRG1BAL-CB_FINISH	Closing Balance
J_1IEXCDTL-EXBAS	Assessable Value
CHAR75	Duty
CHAR25	Notification availed
J_1IEXCDTL-EXBED	Duty Payable
CHAR15	Prov. Assess. no
CHAR25	Rate of Duty (adv), Rate of Duty (Specific), S.No in Notification, Notification availed