How to Fetch total invoice value,taxable value,cgst amt,sgstamt,igstamt

Discussion in 'Free Source Codes' started by Neeru, Mar 8, 2020.

    
  1. Neeru

    Neeru Active Member


    [​IMG] how to fetch total invoice value,taxable value,cgst amt,sgstamt,igstamt Capture.PNG
     


  2. ABDUL AZIZ

    ABDUL AZIZ Member


    Your query do not match with the format,
    Please elaborate ....
     


  3. Neeru

    Neeru Active Member


    Capture.PNG

    Hi @ABDUL AZIZ brother, I want fatch this value...
     


  4. ABDUL AZIZ

    ABDUL AZIZ Member


    Try this....

    [Collection:MyVch]

    Type : Vouchers : Voucher Type
    Child Of : $$VchTypeSales
    Belongs To : Yes
    Fetch : *.*, Date, PartyLedgerName, VoucherTypeName, VoucherNumber, Amount, LedgerName


    [Collection:MyColl]

    Source Collection:MyVch
    By : VoucherNumber : $VoucherNumber
    Compute : IGSTAmount : $LedgerEntries[1,@@IGSTAmt].Amount
    Compute : SGSTAmount : $LedgerEntries[1,@@SGSTAmt].Amount
    Compute : CGSTAmount : $LedgerEntries[1,@@CGSTAmt].Amount
    Compute: SALEAmount : $$CollAmtTotal:InventoryEntries:$Amount
    Fetch : *.*


    [System:Formulae]

    IGSTAmt : $LedgerName Contains "IGST"
    SGSTAmt : $LedgerName Contains "SGST"
    CGSTAmt : $LedgerName Contains "CGST"
     


  5. Bipin Damania

    Bipin Damania Active Member


    If Inventory is not maintain but Ledger based GST is use in company, then how Taxable value to be compute ?

    Please reply.
     
    panam likes this.


  6. Devendra_Rawat

    Devendra_Rawat Well-Known Member


    Its a bit difficult task,

    solution by Aziz works when ledger name contains those gst names, otherwise
    replace formula in collection.

    IsCGSTDutyLed : @@IsGSTDutyLed AND $$IsSysNameEqual:CGST:($GSTDutyHead:Ledger:$LedgerName)
    IsSGSTDutyLed : @@IsGSTDutyLed AND @@IsSGSTDutyLedHead
    IsIGSTDutyLed : @@IsGSTDutyLed AND $$IsSysNameEqual:IGST:($GSTDutyHead:Ledger:$LedgerName)
    IsCessDutyLed : @@IsGSTDutyLed AND $$IsSysNameEqual:Cess:($GSTDutyHead:Ledger:$LedgerName)

    IsGSTDutyLed : $IsVATDutyLedger:Ledger:$LedgerName AND $$IsSysNameEqual:GST:($TaxType:Ledger:$LedgerName)



    Biggest problem will come when you will need rate wise bifurcation of Tax Amount
     
    ABDUL AZIZ likes this.


  7. ABDUL AZIZ

    ABDUL AZIZ Member


    If inventory is not maintained then below collection may be used.. but may some other universal way also ...

    [Collection:MyVch]

    Type : Vouchers : Voucher Type
    Child Of : $$VchTypeSales
    Belongs To : Yes
    Fetch : *.*, Date, PartyLedgerName, VoucherTypeName, VoucherNumber, Amount, LedgerName


    [Collection:MyColl]

    Source Collection:MyVch
    By : VoucherNumber : $VoucherNumber
    Compute : IGSTAmount : $LedgerEntries[1,@@IsIGSTDutyLed].Amount
    Compute : SGSTAmount : $LedgerEntries[1,@@IsSGSTDutyLed].Amount
    Compute : CGSTAmount : $LedgerEntries[1,@@IsCGSTDutyLed].Amount
    Compute : SALEAmount : $LedgerEntries[1,@@SalesAccount].Amount;$$CollAmtTotal:InventoryEntries:$Amount
    Fetch : *.*

    ; Select $PartyLedgerName, $SALEAmount, $IGSTAmount, $SGSTAmount, $CGSTAmount, $Amount from MyColl

    [System:Formulae]

    IGSTAmt : $LedgerName Contains "IGST"
    SGSTAmt : $LedgerName Contains "SGST"
    CGSTAmt : $LedgerName Contains "CGST"
    SalesAccount : $$IsLedOfGrp:$LedgerName:$$GroupSales; Contains "Sale"
     


  8. chavan manohar

    chavan manohar New Member


    SIR
    I HAVE TRYED THIS CODE. I UNABLE TO SEE THIS REPORT. WHER THIS REPORT WILL GET
     


  9. ponsiva73

    ponsiva73 New Member


    Fentastic
     


  10. rony

    rony Member


    Dear Experts,
    Is their any way that if voucher has class type and we don't want to show that class entries in report which formula can omit those class type entries.
    Thanks.
     


  11. rony

    rony Member


    And for fetching fields directly use this function on concern field level. Set As : $$FilterAmtTotal:LedgerEntries:IGST1:$Amount and collection of Mr. Aziz can be used and in formula section
    IGST :$$FilterAmtTotal:LedgerEntries:IGST1:$Amount
    IGST1 :$Name:Ledger:$LedgerName Contains $$LocaleString:"IGST" also be used for fetching detail.
    But in this my and Mr. Aziz has one problem that it elaborate single entry of total IGST and not % wise. Then garimaji said it will be big issue . On that case for every rate one field has to be create as well as function and formula. In columnar it auto omits the column which has no figure and only shows the figured column. that type also can be developed.
     


  12. Bipin Damania

    Bipin Damania Active Member


    In which Report and where in that Report Class Type is Display (Show) ?
    Please explain with Screenshot
     


  13. rony

    rony Member


    Dear Damaniaji,
    This day book report is of Sattam sir and you also posted for "3 months help person" post. In that all entries of all voucher will come. attached img shows, specially journal voucher entry of dated 30-6 entry shows journal voucher class of GST adjustment. I am asking for filter these type of class entry from report for all vouchers.
     

    Attached Files:

    Last edited: Aug 30, 2020


  14. sattam

    sattam Active Member


    Use Filter in Collection
    Code:
    [Collection:My Collection]
    Filter:myfilter
    [System: Formulae]
    myfilter:(##SVClass=$$SysName:NotApplicable) OR $$IsEmpty:##SVClass
     


  15. rony

    rony Member


    Sattam Sir,
    Is this common formula for all voucher class or I have to mention voucher name.
    Thanks.
     


  16. Amit Kamdar

    Amit Kamdar Administrator Staff Member


    It is Common. No need to specify any Voucher type, unless you want to include e.g. Sales with Class and exclude purchase with class, then you need to mention the Voucher type too with AND.
     


  17. rony

    rony Member


    Sirji Tried but not filtering. Same done as sattamji suggested.Please help.
    regards
     

    Attached Files:



  18. Amit Kamdar

    Amit Kamdar Administrator Staff Member


    myfilter: $$IsEmpty:##SVClass
     


  19. Amit Kamdar

    Amit Kamdar Administrator Staff Member


    From the photo it is apparent that only Purchase Voucher summary you need...........

    Then best to filter as per Purchase VoucherType
     


  20. Bipin Damania

    Bipin Damania Active Member


    As per Amitji's guidance filter only Purchase type Vouchers
     


  21. rony

    rony Member


    Dear Experts,
    I am using for 3 vouchers type and my collection is as under. I tried Amit Sir suggestion also but could not succeed.
    [Collection : My Collection]
    Type : Vouchers
    Filter : Purchase
    Fetch : Date,Amount,PartyLedgerName,Reference,VoucherNumber,TAmount,IGST,CGST,SGST,CESS
    Fetch : Stock Item,Inventory Entries
    Filter : myfilter

    [System: Formula]
    Purchase : $$IsPurchase:$VoucherTypeName OR $$IsDebitNote:$VoucherTypeName OR $$IsJournal:$VoucherTypeName
    myfilter : $$IsEmpty:##SVClass
     


  22. Amit Kamdar

    Amit Kamdar Administrator Staff Member


    remove ------------ Filter : myfilter
     


  23. rony

    rony Member


    Dear Amitji,
    Removed as per UR instruction even though Class voucher entry comes. Earlier no filter used but sattam sir suggested for class filter
    myfilter:(##SVClass=$$SysName:NotApplicable) OR $$IsEmpty:##SVClass
    , did not work then U suggested
    myfilter: $$IsEmpty:##SVClass
    also does not omit class voucher (GST Adjustment) entry . Even I remove Filter then also same status as img I posted earlier. Actually I want to filter class type voucher entry .like gst adjustment or interest class of voucher specially for journal and debit note.
    Please Help.
    Thanks.
     


  24. PANKAJ BHONDLE

    PANKAJ BHONDLE Active Member


    Filter:$VoucherTypeName="Journal"
     


  25. rony

    rony Member


    Pankaji,
    That Filter already in my collection. I want to filter journal and debit note voucher's class entry. Please see my image. unfortunately both sattam sir and amitji suggested function did not work.
     


Share This Page