Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.6K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 295 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 993 SQL General
- 384 SQL Performance
- 34 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 259 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
customer ledger entry table report
asembereng
Member Posts: 220
I have attached a working report but the client wants me to display all the end of month within a period even if there are no transactions within that month. it should just indication zero. can someone help me. I am using the posting date to display financial transaction within that date. can someone help me out with this?
OBJECT Report 50005 MortgageStatementfor Housing
{
OBJECT-PROPERTIES
{
Date=30/07/08;
Time=14:27:57;
Modified=Yes;
Version List=NCM/MLS;
}
PROPERTIES
{
OnPreReport=BEGIN
DateFilt:=Customer.GETFILTER(Customer."Date Filter");
CompanyInformation.GET;
CompanyInformation.CALCFIELDS(Picture);
END;
}
DATAITEMS
{
{ PROPERTIES
{
DataItemTable=Table18;
DataItemTableView=SORTING(No.)
ORDER(Ascending)
WHERE(Fin. Charge Terms Code=FILTER(<>''));
NewPagePerRecord=Yes;
PrintOnlyIfDetail=Yes;
OnPreDataItem=BEGIN
IF "Cust. Ledger Entry".GETFILTERS <> '' THEN
FilterText := 'Filter: ' + "Cust. Ledger Entry".GETFILTERS;
//Customer.SETFILTER("No.",'BAK/A11');
END;
OnAfterGetRecord=BEGIN
FinChrgTerm.GET("Fin. Charge Terms Code");
IF NOT FinChrgTerm.GET("Fin. Charge Terms Code") THEN
FinChrgTerm."Interest Rate" :=0;
END;
ReqFilterFields=No.,Date Filter;
}
SECTIONS
{
{ PROPERTIES
{
SectionType=Header;
PrintOnEveryPage=Yes;
SectionWidth=19650;
SectionHeight=6768;
}
CONTROLS
{
{ 1000000002;TextBox;15000;0 ;3150 ;423 ;HorzAlign=Right;
SourceExpr=FORMAT(TODAY,0,4) }
{ 1000000003;TextBox;0 ;423 ;2850 ;423 ;Visible=No;
Enabled=No;
SourceExpr=COMPANYNAME }
{ 1000000004;TextBox;17700;423 ;450 ;423 ;CaptionML=ENU=Page;
SourceExpr=CurrReport.PAGENO }
{ 1000000005;Label ;16950;423 ;750 ;423 ;ParentControl=1000000004 }
{ 1000000042;Label ;4650 ;5499 ;9900 ;423 ;HorzAlign=Center;
FontSize=10;
FontBold=Yes;
CaptionML=ENU=MORTGAGE STATEMENT }
{ 1000000050;Label ;6600 ;5922 ;2250 ;423 ;Visible=No;
HorzAlign=Center;
FontSize=10;
FontBold=Yes;
CaptionML=ENU=AS AT }
{ 1000000027;TextBox;8400 ;5922 ;5400 ;423 ;Visible=No;
FontSize=9;
FontBold=Yes;
SourceExpr=GETFILTER("Date Filter") }
{ 1000000001;PictureBox;4500;0 ;9150 ;5076 ;SourceExpr=CompanyInformation.Picture }
}
}
{ PROPERTIES
{
SectionType=Body;
SectionWidth=19650;
SectionHeight=3384;
}
CONTROLS
{
{ 1000000008;TextBox;3150 ;0 ;1500 ;423 ;HorzAlign=Left;
FontSize=8;
SourceExpr="No." }
{ 1000000009;Label ;0 ;0 ;3000 ;423 ;ParentControl=1000000008;
FontSize=8;
FontBold=Yes }
{ 1000000011;TextBox;3150 ;423 ;6150 ;423 ;HorzAlign=Left;
FontSize=8;
SourceExpr=Name }
{ 1000000012;Label ;0 ;423 ;3000 ;423 ;ParentControl=1000000011;
FontSize=8;
FontBold=Yes }
{ 1000000014;TextBox;12300;423 ;3000 ;423 ;HorzAlign=Left;
FontSize=8;
SourceExpr="Monthly Install" }
{ 1000000015;Label ;9600 ;423 ;2250 ;423 ;ParentControl=1000000014;
FontSize=8;
FontBold=Yes }
{ 1000000017;TextBox;3150 ;1269 ;1800 ;423 ;HorzAlign=Left;
FontSize=8;
SourceExpr="Loan Amount" }
{ 1000000018;Label ;0 ;1269 ;3000 ;423 ;ParentControl=1000000017;
FontSize=8;
FontBold=Yes;
CaptionML=ENU=Plot Cost }
{ 1000000020;TextBox;3150 ;1692 ;1800 ;423 ;HorzAlign=Left;
FontSize=8;
SourceExpr="Down Payment" }
{ 1000000021;Label ;0 ;1692 ;3000 ;423 ;ParentControl=1000000020;
FontSize=8;
FontBold=Yes }
{ 1000000023;TextBox;3150 ;2115 ;1500 ;423 ;HorzAlign=Left;
FontSize=8;
SourceExpr="Plot No." }
{ 1000000024;Label ;0 ;2115 ;3000 ;423 ;ParentControl=1000000023;
FontSize=8;
FontBold=Yes }
{ 1000000036;TextBox;12300;1269 ;1800 ;423 ;HorzAlign=Left;
SourceExpr=FinChrgTerm."Interest Rate" }
{ 1000000026;Label ;9600 ;1269 ;2100 ;423 ;FontSize=8;
FontBold=Yes;
CaptionML=ENU=Interest Rate }
}
}
}
}
{ PROPERTIES
{
DataItemIndent=1;
DataItemTable=Table21;
DataItemTableView=SORTING(Customer No.,Posting Date)
WHERE(Reversed=CONST(No));
OnPreDataItem=BEGIN
//"Cust. Ledger Entry".SETFILTER("Posting Date",DateFilt);
FinchargeTotal:=0;
PrincipalTotal:=0;
ArreasVarTotal:=0;
CustBal1 := 0;
TotCustBal1:=0;
NumPayments:=0;
END;
OnAfterGetRecord=BEGIN
Fincharge:=0;
Principal:=0;
ArreasVar:=0;
//NumPayments:=0;
CustBal :=CustBal+"Cust. Ledger Entry".Amount;
TotalCustBal := "Detailed Cust. Ledg. Entry"."Amount (LCY)";
CustBal1 := "Cust. Ledger Entry". Amount;
//CustBal :=CustBal + "Cust. Ledger Entry". Amount;
TotCustBal1 += CustBal1;
FromDate := 010107D;
ToDate := TODAY;
//Amount := 0;
CustLedEntryMonth := '';
MonthsArrears := 0;
//Code to Count Months
IF (FromDate <> 0D) AND (ToDate > FromDate) THEN BEGIN
Calendar.RESET;
Calendar.SETRANGE("Period Type",Calendar."Period Type"::Month);
Calendar.SETRANGE("Period Start",FromDate,ToDate);
Months := Calendar.COUNT - 1;
END ELSE
Months := 0;
//Code to count two or more Payments as one in a particular month.
IF ("Document Type" = "Document Type"::Payment) AND
(InitMonth <> DATE2DMY("Posting Date",2) ) THEN BEGIN
NumPayments := NumPayments +1;
InitMonth:= DATE2DMY("Posting Date",2);
END;
MonOwed:= Months - NumPayments;
OtherArrears := MonOwed * -Customer."Monthly Install";
TotArrs := OtherArrears + ArreasVarTotal;
IF "Document Type"=0 THEN BEGIN
Amount := CustBal1-CustBal1;
END;
IF "Document Type"="Document Type":: Invoice THEN BEGIN
Amount := CustBal1-CustBal1;
END;
IF "Document Type"="Document Type":: Refund THEN BEGIN
Amount := CustBal1-CustBal1;
END;
IF "Document Type"="Document Type":: Refund THEN BEGIN
CustBal1 := Amount;
END;
//TotCustBal1+=CustBal1;
IF "Document Type"="Document Type":: "Finance Charge Memo" THEN BEGIN
// Fincharge :=-ABS(Amount);
END;
IF "Document Type" = "Document Type"::Payment THEN BEGIN
Pay := Amount;
FinCharg := 0;
END;
IF "Document Type" = "Document Type"::"Finance Charge Memo" THEN BEGIN
FinCharg := Amount;
Pay := 0;
END;
IF "Document Type"="Document Type":: "Finance Charge Memo" THEN BEGIN
Amount :=ABS(Amount)-ABS(Amount);
END;
IF "Document Type"="Document Type"::Payment THEN BEGIN
Principal:= "Closed by Amount";
IF Principal=0 THEN
Fincharge :=0
ELSE
Fincharge :=ABS(Amount)-ABS(Principal);
// FinchargeTotal:=ABS(FinchargeTotal+ ABS(Fincharge));
PrincipalTotal :=PrincipalTotal+Principal;
IF Customer."Monthly Install" = 0 THEN BEGIN
ArreasVar:=0;
END ELSE
ArreasVar:= -Customer."Monthly Install" - "Cust. Ledger Entry".Amount;
ArreasVarTotal:=ArreasVarTotal + ArreasVar;
END;
//ArreasVarTot:= ArreasVarTot + ArreasVar;
"var" := ArreasVarTot;
// ...........................MLS.............................................
//Code that does the monthly total of arrears
IF InitializeArreasVarTot THEN
ArreasVarTot := 0;
InitializeArreasVarTot := FALSE;
CLE.RESET;
CLE.COPY("Cust. Ledger Entry"); //copying current rec with filters
IF CLE.NEXT = 0 THEN //checking next rec's date.......starts
MonthEnds := TRUE
ELSE BEGIN
IF ((DATE2DMY("Posting Date",3) * 1000 + DATE2DMY("Posting Date",2)) =
(DATE2DMY(CLE."Posting Date",3) * 1000 + DATE2DMY(CLE."Posting Date",2))) THEN
MonthEnds := FALSE
ELSE
MonthEnds := TRUE; //checking next rec's date.........end
END;
ArreasVarTot:= ArreasVarTot + ArreasVar;
P1:= FORMAT(Pay);
P2:=FORMAT(FinCharg);
P3:=FORMAT(TotCustBal1);
P4:=FORMAT(ArreasVar);
FinchargeTotal+=-Fincharge;
absFinchargeTotal:=ABS(FinchargeTotal);
END;
CalcFields=Amount;
DataItemLink=Customer No.=FIELD(No.),
Posting Date=FIELD(Date Filter);
}
SECTIONS
{
{ PROPERTIES
{
SectionType=Header;
SectionWidth=19650;
SectionHeight=1269;
}
CONTROLS
{
{ 1000000007;Label ;0 ;0 ;1200 ;846 ;ParentControl=1000000000;
HorzAlign=Right;
VertAlign=Bottom;
FontSize=8;
FontBold=Yes;
MultiLine=Yes }
{ 1000000013;Label ;3000 ;0 ;1500 ;846 ;ParentControl=1000000010;
VertAlign=Bottom;
FontSize=8;
FontBold=Yes;
MultiLine=Yes }
{ 1000000019;Label ;4350 ;0 ;900 ;846 ;Visible=No;
ParentControl=1000000016;
HorzAlign=General;
VertAlign=Bottom;
FontSize=8;
FontBold=Yes;
MultiLine=Yes }
{ 1000000025;Label ;5100 ;0 ;1500 ;846 ;Visible=No;
ParentControl=1000000022;
HorzAlign=Right;
VertAlign=Bottom;
FontSize=8;
FontBold=Yes;
MultiLine=Yes;
CaptionML=ENU=Repayment }
{ 1000000029;Label ;1500 ;0 ;1350 ;846 ;Visible=No;
ParentControl=1000000028;
VertAlign=Bottom;
FontSize=8;
FontBold=Yes;
MultiLine=Yes }
{ 1000000031;Label ;16500;0 ;2100 ;846 ;ParentControl=1000000030;
HorzAlign=Left;
VertAlign=Bottom;
FontSize=8;
FontBold=Yes;
MultiLine=Yes;
CaptionML=ENU=" Arrears" }
{ 1000000033;Label ;6600 ;0 ;900 ;846 ;Visible=No;
HorzAlign=Right;
VertAlign=Bottom;
FontSize=8;
FontBold=Yes;
MultiLine=Yes;
CaptionML=ENU=Interest Paid }
{ 1102750002;Label ;7500 ;0 ;600 ;846 ;Visible=No;
HorzAlign=Right;
VertAlign=Bottom;
FontSize=8;
FontBold=Yes;
MultiLine=Yes;
CaptionML=ENU=Paid Principal }
{ 1102750003;Label ;12000;0 ;1950 ;846 ;HorzAlign=Left;
VertAlign=Bottom;
FontSize=8;
FontBold=Yes;
MultiLine=Yes;
CaptionML=ENU=Balance }
{ 1000000051;Label ;8100 ;0 ;1500 ;846 ;HorzAlign=Left;
FontSize=8;
FontBold=Yes;
CaptionML=ENU=Payment }
{ 1000000052;Label ;10200;0 ;1500 ;846 ;HorzAlign=Left;
FontSize=8;
FontBold=Yes;
MultiLine=Yes;
CaptionML=ENU=Interest Charged }
{ 1000000053;Label ;14250;0 ;1500 ;846 ;HorzAlign=Left;
VertAlign=Bottom;
FontSize=8;
FontBold=Yes;
MultiLine=Yes;
CaptionML=ENU=Arrears }
}
}
{ PROPERTIES
{
SectionType=Body;
SectionWidth=19650;
SectionHeight=423;
OnPreSection=BEGIN
{"var":=0;
IF MonthEnds THEN
InitializeArreasVarTot := TRUE;
IF MonthEnds THEN
"var" := ArreasVarTot
ELSE
"var" := 0;}
IF MonthEnds THEN
BEGIN
InitializeArreasVarTot := TRUE;
"var" := ArreasVarTot;
END
ELSE
"var" := 0;
END;
}
CONTROLS
{
{ 1000000000;TextBox;150 ;0 ;1200 ;423 ;SourceExpr="Posting Date" }
{ 1000000010;TextBox;3000 ;0 ;1500 ;423 ;HorzAlign=Center;
SourceExpr="Document No." }
{ 1000000016;TextBox;4500 ;0 ;750 ;423 ;Visible=No;
SourceExpr=Description }
{ 1000000022;TextBox;5250 ;0 ;1200 ;423 ;Visible=No;
SourceExpr=Amount }
{ 1000000028;TextBox;1500 ;0 ;1350 ;423 ;Visible=No;
SourceExpr="Document Type" }
{ 1000000032;TextBox;6450 ;0 ;900 ;423 ;Visible=No;
BlankZero=No;
SourceExpr=-Fincharge }
{ 1102750000;TextBox;12000;0 ;1950 ;423 ;HorzAlign=Left;
BlankZero=No;
SourceExpr=TotCustBal1 }
{ 1102750001;TextBox;7500 ;0 ;450 ;423 ;Visible=No;
BlankZero=No;
SourceExpr=Principal }
{ 1000000030;TextBox;16500;0 ;1800 ;423 ;HorzAlign=Left;
NotBlank=No;
BlankZero=Yes;
SourceExpr="var" }
{ 1000000041;TextBox;8100 ;0 ;1500 ;423 ;HorzAlign=Left;
SourceExpr=Pay }
{ 1000000049;TextBox;10200;0 ;1500 ;423 ;HorzAlign=Left;
SourceExpr=FinCharg }
{ 1000000054;TextBox;14250;0 ;1800 ;423 ;HorzAlign=Left;
BlankZero=No;
SourceExpr=ArreasVar }
}
}
{ PROPERTIES
{
SectionType=Footer;
SectionWidth=19650;
SectionHeight=6345;
}
CONTROLS
{
{ 1102750004;TextBox;6450 ;0 ;1050 ;423 ;Visible=No;
Enabled=Yes;
HorzAlign=Center;
FontSize=8;
FontBold=Yes;
BlankZero=Yes;
SourceExpr=FinchargeTotal }
{ 1102750005;TextBox;7500 ;0 ;750 ;423 ;Visible=No;
Enabled=Yes;
HorzAlign=Center;
FontSize=8;
FontBold=Yes;
BlankZero=Yes;
SourceExpr=PrincipalTotal }
{ 1000000035;TextBox;12150;2961 ;2250 ;423 ;HorzAlign=Left;
FontSize=8;
FontBold=Yes;
SourceExpr=TotArrs }
{ 1000000037;Label ;7650 ;846 ;4350 ;423 ;FontSize=7;
FontBold=Yes;
CaptionML=ENU=Arrears for months Skipped: }
{ 1000000006;TextBox;12150;846 ;2550 ;423 ;Visible=Yes;
HorzAlign=Left;
FontSize=8;
FontBold=Yes;
SourceExpr=OtherArrears }
{ 1000000034;TextBox;14250;0 ;2250 ;423 ;HorzAlign=Left;
FontSize=8;
FontBold=Yes;
SourceExpr=ArreasVarTotal }
{ 1000000038;Label ;450 ;2961 ;4200 ;423 ;FontSize=10;
FontBold=Yes;
CaptionML=ENU=Amount Due }
{ 1000000039;TextBox;5550 ;846 ;1800 ;423 ;HorzAlign=Left;
FontSize=7;
FontBold=Yes;
SourceExpr=MonOwed }
{ 1000000040;Label ;600 ;846 ;5100 ;423 ;FontSize=7;
FontBold=Yes;
CaptionML=ENU="Number of Months Skipped =" }
{ 1000000043;Label ;450 ;2115 ;4200 ;423 ;FontSize=10;
FontBold=Yes;
CaptionML=ENU=Closing Balance }
{ 1000000044;TextBox;9000 ;2115 ;1950 ;423 ;HorzAlign=Left;
FontSize=8;
FontBold=Yes;
BlankZero=Yes;
SourceExpr=TotCustBal1 }
{ 1000000045;Label ;0 ;3807 ;19650;423 ;FontSize=8;
FontBold=Yes;
CaptionML=ENU="==========================================================================================================================================================================================" }
{ 1000000046;Label ;3900 ;4653 ;10950;423 ;HorzAlign=Center;
FontSize=8;
CaptionML=ENU=ERRORS AND OMMISSIONS EXEMPTED }
{ 1000000047;Label ;3900 ;5076 ;10950;423 ;HorzAlign=Center;
FontSize=8;
CaptionML=ENU=ENQUERIES TEL: 4222271-6 EXT 307, 342 }
{ 1000000048;Label ;1050 ;4230 ;1650 ;423 ;FontSize=8;
CaptionML=ENU=NOTE: }
}
}
}
}
}
REQUESTFORM
{
PROPERTIES
{
Width=9020;
Height=3410;
}
CONTROLS
{
}
}
CODE
{
VAR
Fincharge@1000000042 : Decimal;
CustBal@1000000041 : Decimal;
Principal@1000000040 : Decimal;
DateFilt@1000000039 : Text[50];
FinchargeTotal@1000000038 : Decimal;
PrincipalTotal@1000000037 : Decimal;
ArreasVar@1000000036 : Decimal;
ArreasVarTotal@1000000035 : Decimal;
"Plot No."@1000000034 : Code[10];
Name@1000000033 : Text[30];
TotalCustBal@1000000032 : Decimal;
"Balance (LCY)"@1000000031 : Decimal;
"Detailed Cust. Ledg. Entry"@1000000030 : Record 379;
CustBal1@1000000029 : Decimal;
TotCustBal1@1000000028 : Decimal;
BAK@1000000027 : Code[10];
"Fin. Charge Terms Code"@1000000026 : Code[10];
CustFilt@1000000025 : Text[50];
Cust@1000000024 : Record 18;
FromDate@1000000023 : Date;
ToDate@1000000022 : Date;
Calendar@1000000021 : Record 2000000007;
Months@1000000020 : Integer;
CustLedEntryMonth@1000000019 : Text[30];
Amt@1000000018 : Decimal;
TotalArr1@1000000017 : Decimal;
TotMonthsArr@1000000016 : Integer;
NumPayments@1000000015 : Integer;
Arrears2@1000000014 : Decimal;
TotMonOut@1000000013 : Decimal;
MonthsPaid@1000000012 : Integer;
MonthsArrears@1000000011 : Decimal;
"Monthly Install"@1000000010 : Decimal;
Payment@1000000009 : Option;
OtherArrears@1000000008 : Decimal;
MonOwed@1000000007 : Integer;
TotArrs@1000000006 : Decimal;
LastFieldNo@1000000005 : Integer;
GTotArrs@1000000004 : Decimal;
GTotCustBal1@1000000003 : Decimal;
GPrincipalTotal@1000000002 : Decimal;
GFinchargeTotal@1000000001 : Decimal;
Gtotal@1000000000 : Decimal;
absFinchargeTotal@1000000043 : Decimal;
CompanyInformation@1000000044 : Record 79;
"Finance Charge Terms"@1000000045 : Record 5;
FinChrgTerm@1000000046 : Record 5;
IntRate@1000000047 : Decimal;
InitMonth@1000000048 : Integer;
FilterText@1000000049 : Text[30];
Pay@1000000050 : Decimal;
FinCharg@1000000051 : Decimal;
ArreasVarTot@1000000052 : Decimal;
Date1@1000000053 : Date;
Expr3@1000000054 : Text[30];
P1@1000000055 : Text[30];
P2@1000000056 : Text[30];
P3@1000000057 : Text[30];
P4@1000000058 : Text[30];
newfin@1000000059 : Text[30];
intOldMonth@1000000060 : Integer;
AccumulatedAmt@1000000064 : Decimal;
PrevMonth@1000000063 : Integer;
MonthEnds@1000000062 : Boolean;
InitialiseTotal@1000000061 : Boolean;
"//sandeep"@1102753000 : Integer;
CLE@1102753001 : Record 21;
var@1000000065 : Decimal;
InitializeArreasVarTot@1000000066 : Boolean;
BEGIN
END.
}
}
0