sp_helptext sp_GetAccountsByCompanyId

CREATE PROCEDURE [dbo].[sp_GetAccountsByCompanyId]   
    @companyId INT          
AS          
BEGIN          
    SET NOCOUNT ON;          
  
    SELECT           
        a._id,          
        a.companyId,          
        a.groupId,        
        ISNULL(g.groupName, 'No Group') AS groupName,       
        a.stateUser,          
        a.transportId,          
        t.name AS transportName,          
        t.phone AS transportPhone,          
        t.gstNo AS transportGSTNo,          
        a.accountName,          
        a.address,          
        a.zipCode,          
        a.email,          
        a.phone,          
        a.gst,          
        a.pan,          
        a.adharNo,          
        a.contectName,          
        a.contectNo,          
        a.bankName,          
        a.ifscCode,          
        a.creditDay,          
        a.regdType,          
        a.isTcsCompulsary,          
        a.tdsApplicabe,          
        a.isItTransport,          
        a.transportMode,          
        a.transportRate,          
        a.tcsLimit,          
        a.cityId,          
        a.tanNo,          
        a.opBalance,          
        a.openingBalanceType,          
        a.clsBalance,          
        a.closingBalanceType,          
        a.agentAndAreaName,          
        a.openingBalance,          
        a.closingBalance,          
  
        -- newly added fields        
        a.state,          
        sm.State AS stateName,          
        sm.StateCode,          
        a.gstVatReturn,          
        a.maintBillWise,          
        a.eComNo,          
        a.expHsnCode,          
        a.cinNo,          
        a.ieCodeNo,          
        a.creditLimit,          
        a.inttRate,          
        a.basicLimit,          
        a.taxFormName,          
        a.tradeType,          
  
        -- Agent details  
        a.agentId,    
        ag.AgentName,    
        ag.Address AS agentAddress,    
        ag.City AS agentCity,    
        ag.PinCode AS agentPinCode,    
        ag.PhoneNumber AS agentPhone,    
        ag.PanNo AS agentPan,    
        ag.Description AS agentDescription,    
        ag.EnterBy AS agentEnterBy,    
        ag.CreatedDate AS agentCreatedDate,  
  
        -- GST/VAT Account Info  
     ISNULL(gVat.id, 0) AS gstVatId,  
    ISNULL(gVat.className, '') AS gstVatClassName,  
    ISNULL(gVat.rate, 0) AS gstVatRate,  
    ISNULL(gVat.Type, '') AS gstVatType,  
    ISNULL(gVat.headingName, 0) AS gstVatHeadingId,  
    ISNULL(h.headingName, '') AS gstVatHeadingName  
  
    FROM dbo.tbAccountMaster a          
    LEFT JOIN dbo.tbTransportMaster t         
        ON a.transportId = t.transportId       
       AND t.companyId = a.companyId          
    LEFT JOIN dbo.tbGroupMaster g        
        ON a.groupId = g.groupId        
       AND (g.companyId = a.companyId OR g.companyId = 0 OR g.companyId IS NULL)        
    LEFT JOIN dbo.tbStateMaster sm         
        ON a.state = sm.Id          
    LEFT JOIN dharmesh.tbAgentMaster ag    
        ON a.agentId = ag.Id    
       AND ag.CompanyId = a.companyId    
    LEFT JOIN dbo.gstVatAccout gVat  
       ON a.gstVat = gVat.id  
       AND gVat.companyId = a.companyId  
    LEFT JOIN dbo.tbHeadingNameMaster h  
        ON gVat.headingName = h.id  
    WHERE a.companyId = @companyId          
    ORDER BY a._id;          
END;