Intelligent Budget Plugin

[red1]

Redoing the full test above with same 3 years range use for C/A/L/P trends including a PercentageBase alternative reference. Displaying again the Budget Planning, Activity and full output. The next post is the full FitNesse story script. Just copy paste in your FitNesse wiki and use it. Make sure you RUN_Server2 first to get costing engine to settle (for few mins and you can stop back the RUN_Server2 if you want). Compare the Balance/Short figures to be correct as below. (The figures or full text cannot be checked due to some runtime embedded trailing space that makes an error match. I will need time to extract that into distinct output so that you need not even look at it, i may do it later when i finish of the other fun parts.)

3YearsStart.png (105.38 KiB) Viewed 15799 times

3YearsPlanActivity.png (84.3 KiB) Viewed 15799 times

3YearsOutput.png (183.48 KiB) Viewed 15799 times

[red1]

!contents -R2 -g -p -f -h
!define TEST_SYSTEM {slim}
//!define COMMAND_PATTERN {java -Xdebug -Xrunjdwp:transport=dt_socket,server=y,suspend=y,address=1044 -cp %p %m}
!path /Applications/FitnesseSlim/fitnesse.jar
!path /Users/red1/Documents/workspace/FitnesseBudget360/bin/
!path /Applications/Adempiere/lib/Adempiere.jar
!path /Applications/Adempiere/lib/AdempiereCLib.jar
!path /Applications/Adempiere/Budget360/FitnesseBudget360.jar

!|import|
|utils|
|budget|

!|ADempiere Login|
|User | Password|Client|Role|Login?|
|GardenAdmin| GardenAdmin |GardenWorld|GardenWorld Admin|true|

//master data setup Budget Config Purchasing Key > 11000 Checking to be non-doc
//PopulateFacts setup 3 years of @Fact_Acct.Name data
!|User Setup |
| Business Partner |Organisation| Warehouse |Year Periods|Get Periods?|
|Joe Block | HQ|HQ |2013|12|

!|User Setup |
|Year Periods|Initial Amount|Increment Amount|Populate Facts|Master Data?|Accounts Posting?|
|2014|10|1|41000|true |I|

!|User Setup |
|Initial Amount|Increment Amount|Populate Facts|
|5|1|16100|

//setup budget plan rules
!|Budget Plan|
|Business Partner|Is Sales Trx|Percentage Base|Account ID|Org|Period|Project|Product|Qty|CR Amount|Percent|Create?|
|Joe Block|N|||HQ|Jun-14|Standard|Seeder|0|0|40|true|
|Joe Block|Y|||HQ||Standard||0|0|32|true|
||N|||||Standard|Hoe|0|1500|0|true|
|Joe Block|N|16100|||||Seeder|0|0|30|true|
||N||||||Hoe|50|0|0|true|
||N||60130|||||0|1200|0|true|

//execute transaction
!|Budget Activity|
|Transaction Type|Project|Product|Qty|Acct|Execute Transaction?|
|Sales Order|Standard|Seeder|1||true|
|Sales Order||Hoe|1||true|

!|Budget Activity|
|Config Years|Config Months|Trend|Update Revenue?|
|3|0|A|true|

!|Budget Activity|
|Transaction Type|Project|Product|Qty|Acct|Execute Transaction?|
|Purchase Order|Standard|Seeder|1||true|
|Invoice Vendor| |Hoe|1||true|
|Purchase Order|Standard|Hoe|1||true|
|Purchase Order||Seeder|1||true|
|GL Journal|||500|60130|true|

!|Budget Activity|
|Config Years|Config Months|Trend|Update Revenue?|
|3|0|L|true|

!|Budget Activity|
|Transaction Type|Project|Product|Qty|Acct|Execute Transaction?|
|Purchase Order|Standard|Seeder|1||true|
|Invoice Vendor| |Hoe|1||true|
|Purchase Order|Standard|Hoe|1||true|
|Purchase Order||Seeder|1||true|
|GL Journal|||500|60130|true|

!|Budget Activity|
|Config Years|Config Months|Trend|Update Revenue?|
|3|0|P|true|

!|Budget Activity|
|Transaction Type|Project|Product|Qty|Acct|Execute Transaction?|
|Purchase Order|Standard|Seeder|1||true|
|Invoice Vendor| |Hoe|1||true|
|Purchase Order|Standard|Hoe|1||true|
|Purchase Order||Seeder|1||true|
|GL Journal|||500|60130|true|

//!|Budget Activity|
//|Get Account?|
//|true|

!|Query:Check Notes|
|Notice|Doc No|Message|Other Info|
|Notice |80003 ||MTable[259-C_Order]|
|Notice |800003 ||MTable[259-C_Order]|
|Notice |10000010 ||MTable[318-C_Invoice]|
|Notice |800004 ||MTable[259-C_Order]|
|Notice |800005 ||MTable[259-C_Order]|
|Notice |1002 ||MTable[224-GL_Journal]|
|Notice |800006 ||MTable[259-C_Order]|
|Notice |10000011 ||MTable[318-C_Invoice]|
|Notice |800007 ||MTable[259-C_Order]|
|Notice |800008 ||MTable[259-C_Order]|
|Notice |1003 ||MTable[224-GL_Journal]|
|Notice |800009 ||MTable[259-C_Order]|
|Notice |10000012 ||MTable[318-C_Invoice]|
|Notice |800010 ||MTable[259-C_Order]|
|Notice |800011 ||MTable[259-C_Order]|
|Notice |1004 ||MTable[224-GL_Journal]|

//!|Query:Accounting Consequence|
//|Table |Month |Record ID |Table Name |Doc Type |Product |Account |Debit |Credit|
//|Fact_Acct | | | | | || |

!define COLLAPSE_SETUP {true}
!define COLLAPSE_TEARDOWN {true}

[red1]

Here i created a separate script 3 Months All Trends, including the PercentageBase reference to 16100 Land accounts sample populated together with the 41000 Trade Revenue. Next page is the story script.

3MonthsStart.png (97.62 KiB) Viewed 15798 times

3MonthsPlanActivity.png (84.2 KiB) Viewed 15798 times

3MonthsOutput.png (164.6 KiB) Viewed 15798 times

[red1]

!contents -R2 -g -p -f -h
!define TEST_SYSTEM {slim}
//!define COMMAND_PATTERN {java -Xdebug -Xrunjdwp:transport=dt_socket,server=y,suspend=y,address=1044 -cp %p %m}
!path /Applications/FitnesseSlim/fitnesse.jar
!path /Users/red1/Documents/workspace/FitnesseBudget360/bin/
!path /Applications/Adempiere/lib/Adempiere.jar
!path /Applications/Adempiere/lib/AdempiereCLib.jar
!path /Applications/Adempiere/Budget360/FitnesseBudget360.jar

!|import|
|utils|
|budget|

!|ADempiere Login|
|User | Password|Client|Role|Login?|
|GardenAdmin| GardenAdmin |GardenWorld|GardenWorld Admin|true|

//master data setup Budget Config
//PopulateFacts setup 3 years of @Fact_Acct.Name data
!|User Setup |
| Business Partner |Organisation| Warehouse |Year Periods|Get Periods?|
|Joe Block | HQ|HQ |2013|12|

!|User Setup |
|Year Periods|Initial Amount|Increment Amount|Populate Facts|Master Data?|Accounts Posting?|
|2014|10|1|41000|true |I|

!|User Setup |
|Initial Amount|Increment Amount|Populate Facts|
|5|1|16100|

//setup budget plan rules
!|Budget Plan|
|Business Partner|Is Sales Trx|Percentage Base|Account ID|Org|Period|Project|Product|Qty|CR Amount|Percent|Create?|
|Joe Block|N|||HQ|Jun-14|Standard|Seeder|0|0|40|true|
|Joe Block|Y|||HQ||Standard||0|0|32|true|
||N|||||Standard|Hoe|0|1500|0|true|
|Joe Block|N|16100|||||Seeder|0|0|30|true|
||N||||||Hoe|50|0|0|true|
||N||60130|||||0|1200|0|true|

//execute transaction
!|Budget Activity|
|Transaction Type|Project|Product|Qty|Acct|Execute Transaction?|
|Sales Order|Standard|Seeder|1||true|
|Sales Order||Hoe|1||true|

!|Budget Activity|
|Config Years|Config Months|Trend|Update Revenue?|
|0|3|A|true|

!|Budget Activity|
|Transaction Type|Project|Product|Qty|Acct|Execute Transaction?|
|Purchase Order|Standard|Seeder|1||true|
|Invoice Vendor| |Hoe|1||true|
|Purchase Order|Standard|Hoe|1||true|
|Purchase Order||Seeder|1||true|
|GL Journal|||500|60130|true|

!|Budget Activity|
|Config Years|Config Months|Trend|Update Revenue?|
|0|3|L|true|

!|Budget Activity|
|Transaction Type|Project|Product|Qty|Acct|Execute Transaction?|
|Purchase Order|Standard|Seeder|1||true|
|Invoice Vendor| |Hoe|1||true|
|Purchase Order|Standard|Hoe|1||true|
|Purchase Order||Seeder|1||true|
|GL Journal|||500|60130|true|

!|Budget Activity|
|Config Years|Config Months|Trend|Update Revenue?|
|0|3|P|true|

!|Budget Activity|
|Transaction Type|Project|Product|Qty|Acct|Execute Transaction?|
|Purchase Order|Standard|Seeder|1||true|
|Invoice Vendor| |Hoe|1||true|
|Purchase Order|Standard|Hoe|1||true|
|Purchase Order||Seeder|1||true|
|GL Journal|||500|60130|true|

//!|Budget Activity|
//|Get Account?|
//|true|

!|Query:Check Notes|
|Notice|Doc No|Message|Other Info|
|Notice |80003 ||MTable[259-C_Order]|
|Notice |800003 ||MTable[259-C_Order]|
|Notice |10000010 ||MTable[318-C_Invoice]|
|Notice |800004 ||MTable[259-C_Order]|
|Notice |800005 ||MTable[259-C_Order]|
|Notice |1002 ||MTable[224-GL_Journal]|
|Notice |800006 ||MTable[259-C_Order]|
|Notice |10000011 ||MTable[318-C_Invoice]|
|Notice |800007 ||MTable[259-C_Order]|
|Notice |800008 ||MTable[259-C_Order]|
|Notice |1003 ||MTable[224-GL_Journal]|
|Notice |800009 ||MTable[259-C_Order]|
|Notice |10000012 ||MTable[318-C_Invoice]|
|Notice |800010 ||MTable[259-C_Order]|
|Notice |800011 ||MTable[259-C_Order]|
|Notice |1004 ||MTable[224-GL_Journal]|

//!|Query:Accounting Consequence|
//|Table |Month |Record ID |Table Name |Doc Type |Product |Account |Debit |Credit|
//|Fact_Acct | | | | | || |

!define COLLAPSE_SETUP {true}
!define COLLAPSE_TEARDOWN {true}

[red1]

I have successfully converted back to iDempiere and uploaded tested binaries to https://sourceforge.net/projects/red1/files/p2/Budget/ The source is also pushed to my bitbucket.org/red1/

EclipseiDempiereBudget.png (177.18 KiB) Viewed 15766 times

What is interesting this time is that the FitNesse testing is much easier (i forgot how easy it was and was clumsily trying to put in variables setting as described in another of my forum here but actually you can just ignore it) and the TrxName instance does not drop! That means you can interrogate it further after the FitNesse story ends. So i made a manual Trx.rollback().
Source of org.idempiere.fitnesse. budget is also pushed to my bitbucket repo. I pasted below the ending tests results of ThreeMonthsAllTrends and then ThreeYearAllTrends for perusal.
At the bottom I show the use of Felix Console to install the plugins. Now i am left with a last task to redo the Budget.PDF as much has changed these weeks on it.

iDempiereFitNesse3Months.png (188.79 KiB) Viewed 15766 times

iDempiereFitNesseThreeYears.png (198.28 KiB) Viewed 15766 times

FelixConsoleFitnesse.png (85.36 KiB) Viewed 15766 times

[red1]

The Budget module is now considered finally complete. The total guide with working binaries uploaded to http://sourceforge.net/projects/red1/files/p2/Budget/
Some more starter images made to the Budget.pdf shown here to make the investment into this module faster in assessment and adoption.

MenuLayout.png (59.66 KiB) Viewed 15730 times

BudgetCode.png (179.85 KiB) Viewed 15730 times

[red1]

Under advice from Steven Sackett of ADAXA, Australia, that convinced me that accountants love only Excel spreadsheets, I began to create an import loader for the Budget Plan Lines. It will import into I_Budget table. Then it can be imported. It can also do a sub BudgetReference tab details for each BudgetPlanLine when defined without a new line.

Import Loader.png (68.95 KiB) Viewed 15695 times

Imported Model.png (76.12 KiB) Viewed 15695 times

[red1]

The import process code done and tested on both ADempiere and iDempiere. All binaries and iBudgetImportTest.csv as sample import excel format uploaded to http://sourceforge.net/projects/red1/files/p2/Budget/ . As usual all source in the same bitbucket.

[red1]

Deepak Pansheriya made two improvements for this:
https://bitbucket.org/red1/budget360/pu ... ement/diff
https://bitbucket.org/red1/budget360/pu ... and-adding
He documented, quite easy to understand and is a great gap cruncher as it brings this budget planner back to the accounting facts for budgeting. http://logilite.com/budget-journal-enha ... et-module/

[red1]

Did a review of Deepak Loglite INDIA's contribution that converts the Budget Plan Details into Accounting Facts (Budget) to streamline for conventional iDempiere use. Merged his 2Pack into the present 2Pack, and as usual, i put in an auto PackOut for reuse of the 2Pack.
Please find at http://sourceforge.net/projects/red1/files/p2/Budget/
org.idempiere.budget_2.1.0.201509211950.jar - OSGi plugin + 2Pack
iBudget.zip - 2Pack only

Source is also committed to http://bitbucket.org/red1/org.idempiere.budget.
Corrected a mistake in defining fields that refers to IDs, have to also suffix _ID, thus PeriodTo_ID, PeriodFrom_ID are the correct usage.
Not yet tested, and will be doing that in the ongoing http://red1.org/iDempiereFitNesse.pdf while thinking of adding a process also to convert Budget Plan to Sales/Purchase Forecast also underway at the same time. (DONE)

In my Eclipse IDE source test, the plugin works and installed its embedded 2Pack, and i can try the BudgetPlan process to Accts done by LogLite.