Sales Invoice Report Format and Development:
(1) Development of New report already explained in the previous blog. In this blog, we will develop a new “Sales Invoice Report”. Follow the below screenshot and develop a report for the client. The report includes- No. of Copies and Document No. filter.
(2) RDLC Format for designing.
(3) After publish and attached. See the result in the below screenshot.
Source Code:
| report50000 “Sales Invoice Report” { UsageCategory = Administration; ApplicationArea = All; DefaultLayout = RDLC; RDLCLayout = ‘./SalesInvoiceReport.rdl’; dataset { dataitem(Integer; Integer) { DataItemTableView = SORTING(“Number”); column(Number_Copies; Number) { } column(Copy_Text; CopyText) { } dataitem(“Sales Invoice Header”; “Sales Invoice Header”) { DataItemTableView = SORTING(“No.”); DataItemLinkReference = Integer; column(CompanyInformation_Name; CompanyInformation.Name) { } column(CompanyInformation_Picture; CompanyInformation.Picture) { } column(CompanyInformation_Address; CompanyInformation.Address) { } column(CompanyInformation_Address_2; CompanyInformation.”Address 2″) { } column(CompanyInformation_Post_Code; CompanyInformation.”Post Code”) { } column(CompanyInformation_City_Code; CompanyInformation.”City”) { } column(CompanyInformation_GST_No; CompanyInformation.”GST Registration No.”) { } column(CompanyInformation_State_Code; CompanyInformation.”State Code”) { } column(CompanyInformation_Email_Id; CompanyInformation.”E-Mail”) { } column(CompanyInformation_Phone_No; CompanyInformation.”Phone No.”) { } column(CompanyInformation_PAN_No; CompanyInformation.”P.A.N. No.”) { } column(CompanyInformation_Industrial_Classification; CompanyInformation.”Industrial Classification”) { } column(CompanyInformation_Bank_Name; CompanyInformation.”Bank Name”) { } column(CompanyInformation_Bank_Account_No; CompanyInformation.”Bank Account No.”) { } column(CompanyInformation_Bank_Branch_No; CompanyInformation.”Bank Branch No.”) { } column(Document_No_; “No.”) { } column(Posting_Date; “Posting Date”) { } column(Bill_to_Customer_No_; “Bill-to Customer No.”) { } column(Bill_to_Name; “Bill-to Name”) { } column(Bill_to_Address; “Bill-to Address”) { } column(Bill_to_Address_2; “Bill-to Address”) { } column(Bill_to_City; “Bill-to City”) { } column(Bill_to_State_Code; “GST Bill-to State Code”) { } column(Bill_to_GST_No; “Customer GST Reg. No.”) { } column(Bill_to_Post_Code; “Bill-to Post Code”) { } column(Bill_to_State_No; BillToStateNo) { } column(ShipToAddressInfo_Name; ShipToAddressInfo[1]) { } column(ShipToAddressInfo_Address; ShipToAddressInfo[2]) { } column(ShipToAddressInfo_Address_2; ShipToAddressInfo[3]) { } column(ShipToAddressInfo_City; ShipToAddressInfo[4]) { } column(ShipToAddressInfo_State; ShipToAddressInfo[5]) { } column(ShipToAddressInfo_GST; ShipToAddressInfo[6]) { } column(ShipToAddressInfo_Post_Code; ShipToAddressInfo[7]) { } column(ShipToAddressInfo_State_No; ShipToAddressInfo[8]) { } column(Total_GST_Amount; TotalGSTAmount) { } column(CGST_Total_Amount; CGSTTotalAmount) { } column(SGST_Total_Amount; SGSTTotalAmount) { } column(IGST_Total_Amount; IGSTTotalAmount) { } column(Total_Invoice_Value; TotalInvoiceValue) { } column(Round_Off; RoundOff) { } column(AmountInWords_1; AmountInWords[1]) { } column(AmountInWords_2; AmountInWords[2]) { } column(Header_Comment; HeaderComment) { } column(External_Document_No_; “External Document No.”) { } column(Document_Date; “Document Date”) { } column(Payment_Terms_Code; “Payment Terms Code”) { } column(Salesperson_Code; Salesperson.Name) { } dataitem(“Sales Invoice Line”; “Sales Invoice Line”) { DataItemTableView = SORTING(“Document No.”, “Line No.”) Where(Quantity = Filter(<>0)); DataItemLinkReference = “Sales Invoice Header”; DataItemLink = “Document No.” = FIELD(“No.”); column(Sr_No; SrNo) { } column(Line_No_; SalesLineNo) { } column(Line_Description; “Description”) { } column(Line_Description_2; “Description 2”) { } column(Line_Description_3; Description3) { } column(Line_Quantity; “Quantity”) { } column(Unit_of_Measure_Code; “Unit Of Measure Code”) { } column(Unit_Price; “Unit Price”) { } column(Line_Amount; “Line Amount”) { } column(HSN_SAC_Code; “HSN/SAC Code”) { } column(Total_GST_Line_Percemntage; TotalGSTLinePercemntage) { } column(Line_Comment; LineComment) { } triggeronafterGetRecord() var myInt: Integer; begin SalesLineNo := ”; ifType = Type::”G/L Account” thenbegin if “No.” = ‘40006400’then currreport.skip; end; ifType = Type::Item thenbegin GetItem.get(“NO.”); ifGetItem.Type = GetItem.Type::Service then SalesLineNo := ‘TNS’ else SalesLineNo := “No.”; endelse SalesLineNo := “No.”; SrNo += 1; TotalGSTLinePercemntage := 0; //TaxTransactionValue.reset; //TaxTransactionValue.Setrange(“Tax Record ID”, SalesInvLine.RecordId); //TaxTransactionValue.Setfilter(Percent, ‘<>%1’, 0); //if TaxTransactionValue.FindSet() then begin //repeat //TotalGSTLinePercemntage += TaxTransactionValue.Percent; //until TaxTransactionValue.next = 0; //end; DetailedGSTLedgerEntry.RESET; DetailedGSTLedgerEntry.SETCURRENTKEY(“GST Component Code”); DetailedGSTLedgerEntry.SETRANGE(“Document No.”, “Document No.”); DetailedGSTLedgerEntry.SETRANGE(“Document Line No.”, “Line No.”); DetailedGSTLedgerEntry.SETRANGE(“Entry Type”, DetailedGSTLedgerEntry.”Entry Type”::”Initial Entry”); IFDetailedGSTLedgerEntry.FINDSETTHENBEGIN REPEAT IFDetailedGSTLedgerEntry.”GST Component Code” = ‘CGST’THENBEGIN //CGSTAmount := DetailedGSTLedgerEntry.”GST Amount”; TotalGSTLinePercemntage += DetailedGSTLedgerEntry.”GST %”; END; IFDetailedGSTLedgerEntry.”GST Component Code” = ‘IGST’THENBEGIN //IGSTAmount := DetailedGSTLedgerEntry.”GST Amount”; TotalGSTLinePercemntage += DetailedGSTLedgerEntry.”GST %”; END; IFDetailedGSTLedgerEntry.”GST Component Code” = ‘SGST’THENBEGIN //SGSTAmount := DetailedGSTLedgerEntry.”GST Amount”; TotalGSTLinePercemntage += DetailedGSTLedgerEntry.”GST %”; END; UNTILDetailedGSTLedgerEntry.NEXT = 0; END; Clear(LineComment); Clear(LineCommentCounter); SalesLineCommentLine.reset; SalesLineCommentLine.setfilter(“Document Type”, ‘%1’, SalesLineCommentLine.”Document Type”::”Posted Invoice”); SalesLineCommentLine.setrange(“No.”, “Document No.”); SalesLineCommentLine.Setrange(“Document Line No.”, “Line No.”); ifSalesLineCommentLine.FindSet() thenbegin repeat LineCommentCounter += 1; ifLineCommentCounter>1then LineComment := LineComment + ‘ ‘ + SalesLineCommentLine.Comment else LineComment := SalesLineCommentLine.Comment; UntilSalesLineCommentLine.next = 0; end; end; } triggerOnPreDataItem() var myInt: Integer; begin CompanyInformation.get; CompanyInformation.CalcFields(Picture); end; triggerOnAfterGetRecord() var myInt: Integer; begin SrNo := 0; clear(ShipToAddressInfo); Clear(BillToStateNo); ifGetStateBillTo.get(“GST Bill-to State Code”) then BillToStateNo := GetStateBillTo.”State Code (GST Reg. No.)”; if “Ship-to Code” <>”thenbegin ShipToAddressInfo[1] := ShipToAddress.Name; ShipToAddressInfo[2] := ShipToAddress.Address; ShipToAddressInfo[3] := ShipToAddress.”Address 2″; ShipToAddressInfo[4] := ShipToAddress.City; ShipToAddressInfo[5] := ShipToAddress.State; ShipToAddressInfo[6] := “Ship-to GST Reg. No.”; ShipToAddressInfo[7] := ShipToAddress.”Post Code”; ifGetStateShipTo.get(ShipToAddress.State) then ShipToAddressInfo[8] := GetStateShipTo.”State Code (GST Reg. No.)”; endelsebegin ShipToAddressInfo[1] := “Bill-to Name”; ShipToAddressInfo[2] := “Bill-to Address”; ShipToAddressInfo[3] := “Bill-to Address 2”; ShipToAddressInfo[4] := “Bill-to City”; ShipToAddressInfo[5] := “GST Bill-to State Code”; ShipToAddressInfo[6] := “Customer GST Reg. No.”; ShipToAddressInfo[7] := “Bill-to Post Code”; ifGetStateShipTo.get(“GST Bill-to State Code”) then ShipToAddressInfo[8] := GetStateShipTo.”State Code (GST Reg. No.)”; end; Clear(CGSTTotalAmount); Clear(SGSTTotalAmount); Clear(IGSTTotalAmount); Clear(TotalInvoiceValue); DetailedGSTLedgerEntry.RESET; DetailedGSTLedgerEntry.SETCURRENTKEY(“GST Component Code”); DetailedGSTLedgerEntry.SETRANGE(“Document No.”, “No.”); //DetailedGSTLedgerEntry.SETRANGE(“Document Line No.”, “Line No.”); DetailedGSTLedgerEntry.SETRANGE(“Entry Type”, DetailedGSTLedgerEntry.”Entry Type”::”Initial Entry”); IFDetailedGSTLedgerEntry.FINDSETTHENBEGIN REPEAT IFDetailedGSTLedgerEntry.”GST Component Code” = ‘CGST’THENBEGIN CGSTTotalAmount += -DetailedGSTLedgerEntry.”GST Amount”; //TotalGSTLinePercemntage += DetailedGSTLedgerEntry.”GST %”; END; IFDetailedGSTLedgerEntry.”GST Component Code” = ‘IGST’THENBEGIN IGSTTotalAmount += -DetailedGSTLedgerEntry.”GST Amount”; //TotalGSTLinePercemntage += DetailedGSTLedgerEntry.”GST %”; END; IFDetailedGSTLedgerEntry.”GST Component Code” = ‘SGST’THENBEGIN SGSTTotalAmount += -DetailedGSTLedgerEntry.”GST Amount”; //TotalGSTLinePercemntage += DetailedGSTLedgerEntry.”GST %”; END; UNTILDetailedGSTLedgerEntry.NEXT = 0; END; SalesInvLine.reset; SalesInvLine.Setrange(“Document No.”, “No.”); ifSalesInvLine.FindSet() thenbegin Repeat TotalInvoiceValue += SalesInvLine.”Line Amount”; UntilSalesInvLine.next = 0; end; TotalInvoiceValue += SGSTTotalAmount + CGSTTotalAmount + IGSTTotalAmount; Clear(HeaderComment); Clear(HeaderCommentCounter); SalesHeaderCommentLine.reset; SalesHeaderCommentLine.setrange(“No.”, “No.”); SalesHeaderCommentLine.Setfilter(“Document Line No.”, ‘%1’, 0); ifSalesHeaderCommentLine.FindSet() thenbegin repeat HeaderCommentCounter += 1; ifHeaderCommentCounter>1then HeaderComment := HeaderComment + ‘ ‘ + SalesHeaderCommentLine.Comment else HeaderComment := SalesHeaderCommentLine.Comment; UntilSalesHeaderCommentLine.next = 0; end; //if “Currency Code” = ” then //”Currency Code” := ‘INR’; ifSalesPerson.get(“Salesperson Code”) then; IndianReportCheck.InitTextVariable(); IndianReportCheck.FormatNoText(AmountinWords, TotalInvoiceValue, “Currency Code”); SalesInvLine.reset; SalesInvLine.setfilter(Type, ‘%1’, SalesInvLine.Type::”G/L Account”); SalesInvLine.setfilter(“No.”, ‘%1’, ‘40006400’); ifSalesInvLine.FindFirst() then RoundOff := SalesInvLine.Amount; end; } triggerOnPreDataItem() var myInt: Integer; begin ifNoOfPrint>1then Setrange(Number, 1, NoOfPrint) else Setrange(Number, 1, 1); end; triggerOnAfterGetRecord() var myInt: Integer; begin IF Number = 1THEN CopyText := ‘ORIGINAL FOR BUYER’; IF Number = 2THEN CopyText := ‘DUPLICATE FOR TRANSPORTER’; IF Number = 3THEN CopyText := ‘TRIPLICATE FOR SUPPLIER’; end; } } requestpage { layout { area(Content) { group(Options) { Caption = ‘Options’; field(NoOfPrint; NoOfPrint) { ApplicationArea = Basic, Suite; Caption = ‘No. of Prints’; ToolTip = ‘Specifies how many copies of the document to print.’; } } } } actions { area(processing) { action(ActionName) { ApplicationArea = All; } } } } triggerOnPreReport() var myInt: Integer; begin end; var CompanyInformation: Record “Company Information”; ShipToAddress: Record “Ship-to Address”; ShipToAddressInfo: Array[10] ofText; GetStateBillTo: Record State; GetStateShipTo: Record State; BillToStateNo: Code[10]; ReportCheck: Report Check; AmountinWords: array[2] ofText[80]; TaxTransactionValue: Record “Tax Transaction Value”; CGSTTotalAmount: Decimal; SGSTTotalAmount: Decimal; IGSTTotalAmount: Decimal; TotalGSTLinePercemntage: Decimal; Counter: Integer; TotalGSTAmount: Decimal; SalesInvLine: record “Sales Invoice Line”; TotalInvoiceValue: decimal; RoundOff: Decimal; SrNo: Integer; SalesLineCommentLine: Record “Sales Comment Line”; SalesHeaderCommentLine: Record “Sales Comment Line”; HeaderComment: Text; LineComment: Text; HeaderCommentCounter: Integer; LineCommentCounter: Integer; DetailedGSTLedgerEntry: Record “Detailed GST Ledger Entry”; SalesLineNo: Text[50]; GetItem: Record Item; NoOfPrint: Integer; CopyText: Text; SalesPerson: Record “Salesperson/Purchaser”; IndianReportCheck: Report18041; } |
