Nov 11

Financial Report improvement featuring Multi report sources

When doing implementation we often struggle to produce Financial Report with complex multi-dimensional accounting layouts. One of the case is when we would like to breakdown an account by Cost Centers or by Sales Regions. In traditional accounting, we used to find a structure of Chart of Account which composed of many duplicated accounts, for example one set of travel expense accounts for General activity and another set of the same travel expense accounts for Sales activity. With multi-dimensional feature that Adempiere has, we often suggest our clients to get rid of those duplicated accounts and tell them that later on they can break them down by any other dimensions they want.

Sample report breakdown by Dept dimension

Sample report breakdown by Dept dimension

Well, not that easy. When it comes to Financial Report, we get hit by the wall that it is so difficult to prepare such a layout using the existing functionality. There is Combination feature in Report Source tab introduced sometimes ago but it is also not offering an easy way we thought. You can use that feature if the dimension only contains small data but what if you have hundreds of records, such as “tell me how much is the Revenue breakdown by products”.

Team Goodwill have tried to improve the current Report Source tab to allow combining multiple report sources in one Report Line using the And/Or condition. Future development would include support for condition in parentheses. For example you can combine two report sources, Account “AND” Business Partner, to produce a Receivables detail report showing the balance for each customer.

Just take a look at the sample report above. The sample #1 is a Report Line using single report source, which is only Account type. By choosing a summary account, it can then expand all its child accounts.

The sample #2 is a Report Line using two report source, one is Account type, and the other is Activity. The Activity is purposely left empty because we want to show all. The system would always expand the details of the report source with the highest Sequence Number, in this case the Activity.

Travel Expense - ALL DEPT (breakdown by Dept)

Travel Expense – ALL DEPT (breakdown by Dept)

Here, we used Activity dimension as “Departments”.

The Tree of Activity as Departments

The Tree of Activity as Departments

Ok, let’s take a look another examples. In sample #5, we show Travel Expense and filtered by General departments only.

Sample Report Filtered and Breakdown

Sample Report Filtered and Breakdown

This is how we do it. We add two report sources like above. But this time we enter “General” activity. In this sample, when the Sequence number is the same, the system would expand the details of the last report source we entered. And since “General” is a summary level activity, system will show all its children.

Travel Expense - General only (breakdown by Dept)

Travel Expense – General only (breakdown by Dept)

In sample #6, thing is getting serious. We want to show Travel Expense, filtered by Sales dept and breakdown by Sales Region. Here how we do it.

Travel Expense - Sales Dept (breakdown by Sales Region)

Travel Expense – Sales Dept (breakdown by Sales Region)

That’s it folks. We would like hearing your feedback on how to improve this functionality. Let’s make Adempiere great again :)

Aug 02

Contoh Kasus: Pembelian Aset melalui Leasing

PT. APL melakukan pembelian aset mobil secara leasing kepada PT. Tunas. Harga OTR mobil tersebut adalah Rp. 210.900.000,00, yang terdiri dari:

  • harga mobil Rp. 165.681.818,00 + PPN 10%
  • biaya administrasi (biaya balik nama) Rp. 28.650.000,00

PT. APL melakukan pembayaran sebesar Rp. 5.000.000,00 untuk DP dan Rp. 74.981.070,00 untuk uang muka, biaya asuransi, biaya administrasi, dan angsuran pertama. Detail transaksi adalah:

  • DP sebesar Rp. 5.000.000,00
  • Uang Muka sebesar Rp. 58.270.000,00
  • Biaya Asuransi sebesar Rp. 10.833.170,00
  • Biaya Administrasi sebesar Rp. 1.200.000,00
  • Angsuran pertama sebesar Rp. 4.677.900,00

Proses leasing dilakukan oleh BCA Finance.

Langkah penyelesaian:

1. Buat dokumen Purchase Invoice (PI) Dealer kepada PT. Tunas. PI senilai 210.900.000,00.

2. Buat dokumen Payment kepada PT. Tunas

  • Payment 1 kepada PT. Tunas dengan nilai Rp. 5.000.000,00.
  • Payment 2 kepada PT. Tunas dengan nilai Rp. 74.981.070,00.
  • Lakukan Payment Allocation untuk PT. Tunas. Alokasi Payment 1 dengan PI Dealer.
  • Lakukan Payment Allocation untuk PT. Tunas. Alokasi Payment 2 dengan PI Dealer sebesar Rp. 58.270.000,00.
  • Lakukan Payment Allocation untuk PT. Tunas. Alokasi Payment 2 dengan Charge Biaya Asuransi sebesar Rp. 10.833.170,00.
  • Lakukan Payment Allocation untuk PT. Tunas. Alokasi Payment 2 dengan Charge Biaya Administrasi sebesar Rp. 1.200.000,00.
  • Lakukan Payment Allocation untuk PT. Tunas. Alokasi PI Dealer dengan Charge Pokok Leasing sebesar sisa (Rp. 147.630.000,00) dari PI Dealer.

3. Bentuk Invoice Leasing

Buat dokumen Purchase Invoice (PI) Leasing kepada BCA Finance. Detail PI adalah:

  • Line 1: Charge Pokok Leasing sebesar Rp. 147.630.000,00.
  • Line 2: Charge Bunga Leasing sebesar Rp. 20.771.541,00.

4. Buat dokumen Asset Addition untuk PT. Tunas. Dokumen yang harus dibuat adalah:

  • Addition 1 dengan Charge Biaya Asuransi sebesar Rp. 10.833.170,00.
  • Addition 2 dengan Charge Biaya Administrasi sebesar Rp. 1.200.000,00.
  • Addition 3 dengan Charge Bunga Leasing sebesar Rp. 20.771.541,00.

5. Lakukan pembayaran angsuran pertama

Lakukan Payment Allocation untuk PT. Tunas dengan Invoice Partner BCA Finance. Alokasi antara sisa Payment 2 dengan Invoice Leasing.

6. Aktivasi aset dengan melakukan proses Complete terhadap Asset Addition

 

Aug 02

Fixed Asset: Leasing

Pembelian Aset Tetap dengan menggunakan metode leasing adalah salah satu cara memperoleh aset. Leasing adalah setiap kegiatan pembiayaan perusahaan dalam bentuk penyediaan atau menyewakan barang-barang modal untuk digunakan oleh perusahaan lain dalam jangka waktu tertentu dengan kriteria sebagai berikut:

  • Pembayaran sewa dilakukan secara berkala
  • Masa sewa ditentukan sesuai dengan jenis barang modal yang dileasingkan
  • Disertai hak opsi, yaitu hak dari perusahaan pengguna barang modal untuk mengembalikan atau membeli barang modal pada akhir jangka waktu perjanjian leasing.

Berikut adalah langkah-langkah utama untuk menyelesaikan proses pembelian aset yang menggunakan metode leasing.

1. Pembuatan Dokumen Purchase Invoice dari Dealer

Sebelum membuat Purchase Invoice, pengguna harus membuat Purchase Order terlebih dahulu. Detail dari Purchase Order sama seperti PO aset lain. Nilai yang kita masukkan ke kolom Price adalah harga dealer, yang biasanya terdiri dari harga mobil, biaya balik nama, dan potongan harga (harga OTR).

Setelah membuat PO, pengguna dapat membuat Material Receipt atau langsung membuat Purchase Invoice, bergantung pada kondisi di lapangan.

Jika dokumen Invoice datang bersama dengan fisik mobil, pengguna membuat Material Receipt lalu membuat Purchase Invoice. Jika dokumen Invoice datang lebih dahulu dibanding fisik mobil, maka pengguna Purchase Invoice terlebih dahulu.

Untuk kondisi kedua, pengguna perlu memperhatikan proses Matching antara Purchase Invoice dengan Material Receipt. Jika dokumen Matching Receipt – Invoice belum terbentuk, maka pengguna harus melakukan proses Matching manual melalui menu Matching PO-Receipt-Invoice.

Purchase Invoice akan membentuk jurnal Asset In Transit [D] terhadap Hutang Dagang [C].

Proses ini juga membentuk dokumen Asset dan Asset Addition secara otomatis. Harga perolehan pada Asset Addition sebesar harga OTR yang tertera pada Purchase Invoice. Jurnal yang terbentuk adalah Harga Perolehan [D] terhadap Asset In Transit [C].

2. Pembayaran untuk Dealer

Setelah menerima Invoice, maka kita harus melakukan pembayaran untuk Dealer. Nilai yang harus dibayarkan antara lain uang muka dan biaya lainnya. Untuk proses ini pengguna membuat dokumen Payment. Ada dua dokumen Payment yang akan terbentuk, yaitu:

  • Payment ke dealer untuk pembayaran uang muka. Jurnal yang terbentuk adalah Seleksi Pembayaran atau Uang Muka [D] terhadap Bank [C].
  • Payment ke dealer untuk pembayaran biaya lain-lain. Jurnal yang terbentuk adalah Biaya Lain-lain [D] terhadap Bank [C].

Alokasi dilakukan menggunakan menu Payment Allocation. Pengguna akan melakukan alokasi antara Payment dengan Purchase Invoice dari dealer. Proses ini terbagi menjadi 2 langkah, yaitu:

  • Alokasi Payment dengan Purchase Invoice sebesar nilai uang muka. Jurnal yang terbentuk adalah Hutang Dagang [D] terhadap Seleksi Pembayaran atau Uang Muka [C].
  • Sisa dari Purchase Invoice akan kita write-off ke charge Pokok Leasing. Jurnal yang terbentuk adalah Hutang Dagang [D] terhadap Pokok Leasing [C].

3. Pembuatan Purchase Invoice Leasing

Proses ini dijalankan dengan membuat Purchase Invoice. Detail dari Purchase Invoice adalah dua baris yang terdiri atas charge Pokok Leasing sebesar sisa Invoice dealer (setelah dipotong uang muka) dan charge Bunga Leasing yang besarannya telah ditentukan.

Jurnal yang terjadi adalah Pokok Leasing [D] dan Bunga Leasing [D] terhadap Hutang Leasing [D].

4. Asset Addition untuk Biaya Lain-lain (dealer) dan Bunga Leasing

Di langkah ini kita akan membuat dokumen Asset Addition untuk biaya lain-lain dan Bunga Leasing. Prosesnya adalah membuat Asset Addition tipe Manual dengan charge biaya lain-lain dan Bunga Leasing. Jumlah dokumen sesuai dengan banyaknya jenis biaya yang dibebankan dan Bunga Leasing.

Jurnal yang terbentuk adalah Harga Perolehan [D] terhadap Biaya Lain-lain [C] dan Bunga Leasing [C].

5. Pembayaran Angsuran Pertama

Angsuran pertama dibayarkan dengan alokasi pembayaran terhadap Hutang Leasing. Pengguna harus membuat dokumen Payment dengan nominal sebesar angsuran pertama. Payment tersebut kemudian dialokasikan dengan Purchase Invoice Leasing melalui penunjukan langsung (kolom Invoice di dokumen Payment) atau alokasi melalui Payment Allocation. Jurnal yang terbentuk adalah

  • Seleksi Pembayaran [D] terhadap Bank [C]
  • Hutang Leasing [D] terhadap Seleksi Pembayaran [C]

6. Aktivasi Asset

Setelah pembayaran angsuran pertama, pengguna dapat mengaktifkan aset dengan melakukan proses Complete terhadap Asset Addition yang terbentuk.

Sep 03

Guide to Disable Weak Diffie-Hellman in Adempiere

Diffie-Hellman key exchange is a popular cryptographic algorithm that allows Internet protocols to agree on a shared key and negotiate a secure connection. It is fundamental to many protocols including HTTPS, SSH, IPsec, SMTPS, and protocols that rely on TLS.

Logjam is a new attack against the Diffie-Hellman key-exchange protocol used in TLS. Basically:

The Logjam attack allows a man-in-the-middle attacker to downgrade vulnerable TLS connections to 512-bit export-grade cryptography. This allows the attacker to read and modify any data passed over the connection. The attack is reminiscent of the FREAK attack, but is due to a flaw in the TLS protocol rather than an implementation vulnerability, and attacks a Diffie-Hellman key exchange rather than an RSA key exchange. The attack affects any server that supports DHE_EXPORT ciphers, and affects all modern web browsers. 8.4% of the Top 1 Million domains were initially vulnerable.

Adempiere 370 too can’t get away with this vulnerability issue. The good news is that it is only susceptible to passive eavesdropping from an attacker with nation-state resources, so you should be less worry. Nevertheless, to eliminate the risk, we have to disable the use of the weak Diffie-Hellman group and use a stronger 2048-bit group. If you don’t do this, your server is no longer accessible using the most recent release of popular browsers. You would get Secure Connection Failed error (see also our post about how to temporarily solve the error).

JSSE Configuration

If you run your own Adempiere 370 server, you might want to follow this advice. Open file serverTemplate.xml that resides in @ADEMPIERE_HOME@\jboss\server\adempiere\deploy\jboss-web.deployer. Look for Connector that define a SSL HTTP/1.1 and add ciphers attribute as shown below.

<!-- Define a SSL HTTP/1.1 Connector on port 8443
This connector uses the JSSE configuration, when using APR, the
connector should be using the OpenSSL style configuration
described in the APR documentation -->
<Connector port="@ADEMPIERE_SSL_PORT@" address="${jboss.bind.address}" protocol="HTTP/1.1" SSLEnabled="true"
maxThreads="150" scheme="https" secure="true"
clientAuth="false"
keystoreFile="@ADEMPIERE_KEYSTORE@"
keystorePass="@ADEMPIERE_KEYSTOREPASS@"
sslProtocol="TLS"
ciphers="TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256,TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA,
TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384,TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA,TLS_ECDHE_RSA_WITH_RC4_128_SHA,
TLS_RSA_WITH_AES_128_CBC_SHA256,TLS_RSA_WITH_AES_128_CBC_SHA,TLS_RSA_WITH_AES_256_CBC_SHA256,
TLS_RSA_WITH_AES_256_CBC_SHA,SSL_RSA_WITH_RC4_128_SHA"
/>

Choosing a cipher suite

A cipher suite is really four different ciphers in one, describing the key exchange, bulk encryption, message authentication and random number function. In this particular case, we’re focusing on the bulk encryption cipher.

The JSSE list of cipher suites is here and there is an extensive comparison list. There’s a number of different ciphers available, and the list has changed substantially between JDK 1.7 and JDK 1.6.

In 1.6, the default list is out of order — some of the weaker ciphers show up before the stronger ciphers do. Not only that, but 1.6 has no support for Elliptic Curve cryptography (ECC) ciphers, which are much stronger and allow for perfect forward secrecy. In 1.7, the default cipher list is reportedly pretty good. Unfortunately Adempiere 370 is still using 1.6.

Choosing a browser

Make sure you have the most recent version of your browser installed, and check for updates frequently. Google Chrome (including Android Browser), Mozilla Firefox, Microsoft Internet Explorer, and Apple Safari are all deploying fixes for the Logjam attack.

Reference

Guide to Deploying Diffie-Hellman for TLS
SSL/TLS, ciphers, perfect forward secrecy and Tomcat
Fixing the Most Dangerous Code in the World

Nov 07

Alokasi pembayaran multi-currency

Di Indonesia kita tentunya tidak lepas dari transaksi yang mempergunakan mata uang asing. Di bagian Finance, kita lazim melakukan pelunasan invoice dalam mata uang asing dengan mempergunakan mata uang Rupiah. Karena perbedaan mata uang ini, biasanya kita mendapati perbedaan selisih kurs antara kedua mata uang tersebut.

Pertanyaannya, bagaimanakah kita menangani proses transaksi yang demikian di dalam Goodwill ERP ? Mudah atau sulitkah ?

Sebenarnya proses transaksi demikian tidak memiliki perbedaan dengan transaksi yang sama mata uang. Untuk lebih jelasnya dalam video berikut ini kami memeragakan bagaimana melakukannya di dalam window Payment.

Youtube channel Goodwill ERP – Alokasi pembayaran beda mata uang.

Untuk transaksi yang lebih kompleks, seperti pembayaran multi payment atau parsial, seperti biasa Anda bisa melakukannya lewat form Payment Allocation, dengan memperhatikan dua hal berikut ini.

  • Kolom Currency di Payment Allocation harus dipilih sesuai dengan Currency Invoice yang kita mau alokasikan.

Jika Invoice kita dalam USD maka pilihlah Currency “USD”. Kemudian centanglah kolom Multi-Currency. Maka baik Invoice maupun Payment Anda yang beda mata uang tersebut akan muncul di layar.

  • Kolom Date di Payment Allocation harus dipilih sesuai dengan Tanggal Payment yang kita mau alokasikan.

Tanggal yang dipilih sebagai tanggal Payment Allocation akan menentukan kurs mana yang akan digunakan. Oleh sebab itu jika Anda melakukan pelunasan dengan 2 atau lebih Payment, Anda harus mencermati tanggal-tanggalnya.

  • Jika tanggal masing-masing Payment sama semua, maka Anda bisa melakukan satu kali alokasi sekaligus.
  1. Invoice A Dialokasikan dengan Payment 1, 2 & 3 (tanggal sama semua). Proses.

  • Jika tanggal masing-masing Payment berbeda-beda, maka alokasi dilakukan secara bertahap dengan urutan seperti berikut :
  1. Invoice A Dialokasikan dengan Payment 1 (sesuai tanggal Payment 1). Proses.

  2. Invoice A Dialokasikan dengan Payment 2 (sesuai tanggal Payment 2). Proses.

  3. Invoice A Dialokasikan dengan Payment 3 (sesuai tanggal Payment 3). Proses.

Semoga penjelasan ini bermanfaat. Selamat mencoba!

Dec 08

Recalculating average costing backwardly

Dealing with average costing could be a nightmare for most of us. Chuck Boecking, a fellow Adempiere consultant based in US explained it very well in his blog why we (not to) choose average costing in a ERP.

ERP is a perpetual inventory accounting system. Therefore, the system  will post every inventory transaction as they happen. When you use average invoicing, your inventory valuation will almost always be inaccurate. For example, Let’s assume you buy a bunch of products over time over different costs. Then, over time, you sell all product. The resulting inventory GL balance will almost always be non-zero. Therefore, you will have a phantom inventory balance. If your operations are even a little complex, the variances that result from average invoice costing can be difficult to track and explain.

Most of the time we heard about recommendation on using Standard Costing as a way to escape from this nightmare. In earlier release of Compiere (v. 2.5), even there are only Standard Costing and Last PO price method. However in Indonesia, the requirement for standard accounting practice is to use either Average Costing or FIFO/LIFO. I still can recall back in 2005 how our team had to develop our own average costing functionality to satisfy the clients’ need.

Eventually Compiere overhauled its costing engine and introduced average costing and FIFO/LIFO. But it’s so poor and immature we could hardly use it in real world. Our team once again drilled down the costing engine and did some major hacks. We contributed it back to community in 2006 although we know it’s not crystal clear perfect yet. At least now we had a working average costing in Adempiere (Hengsin Low later added more workarounds in 2010).

One culprit we still found anyway is the fact that the ERP is allowing a back-dated transaction. Since we are using a moving (weighted) average costing, we could have trouble if user is entering transactions in a not so chronological manner.

I am thinking to have a process where we could tell the ERP to recalculate the costing for a given period. Our team had made a good effort so far. However I’m still not satisfied and then I stumbled on this link referenced by one of my client, whom now became a good friend of mine.

The idea is to calculate the average costing backwardly, instead of from the beginning which is very time and resource consuming. I copy the content of the link here, in case it’s lost.

So let’s begin.

There are two tables:
-the one that holds inventory transactions, and
-the one that holds the latest inventory valuation

I am trying to make an inventory valuation report using average costing method based on a certain date. Doing it the normal way, calculating from the beginning until that specific date, will yield variable response time. Imagine calculating on five years worth of data ( and thousands different inventory items ). It will take considerable amount of time ( and my company is not silicon-valley grade. meaning, 2 core cpu and 8 GB of RAM only) so I am calculating it backwardly: from the latest (current) backtrack to that specific date.

(Every month the accounting dept will check on data, so the calculation will only deal with 1 month’s worth of data, forever. equal to consistent unchanging performance)

I have merged the table into one on the script below

create table test3 ( rn integer, amt numeric, qty integer, oqty integer);
insert into test3 (rn,amt,qty,oqty) values (0,2260038.16765793,8,0);
insert into test3 (rn,amt,qty,oqty) values (1,1647727.2727,3,0);
insert into test3 (rn,amt,qty,oqty) values (2,2489654.75326715,0,1);
insert into test3 (rn,amt,qty,oqty) values (3,2489654.75326715,0,1);
insert into test3 (rn,amt,qty,oqty) values (4,1875443.6364,1,0);
insert into test3 (rn,amt,qty,oqty) values (5,1647727.2727,3,0);
insert into test3 (rn,amt,qty,oqty) values (6,3012987.01302857,0,1);
insert into test3 (rn,amt,qty,oqty) values (7,3012987.01302857,0,1);

select * from test3; (already sorted desc so rn=1 is the newest transaction)

rn  amt        qty  oqty
0   2260038.168 8   0    --> this is the current average
1   1647727.273 3   0
2   2489654.753 0   1
3   2489654.753 0   1
4   1875443.636 1   0
5   1647727.273 3   0
6   3012987.013 0   1
7   3012987.013 0   1

Average Costing Method backtracking ( given current avg calculate last transaction avg, and so on until nth transactions )

Avg (n) = ((Avg(n-1) * (Cum Qty(n)+In Qty(n))) – (In Amount(n) * In Qty (n)) + (Avg(n-1) * Out Qty(n))/(Cum Qty(n)+Out Amount(n))

Cumulative qty for backtracking transactions would be minus for in, plus for out. So if current qty is 8, transaction in qty before is 3, then cumulative qty for that transaction is 5.

To calculate the average for one transaction before last, then we use current average to use in that transaction calculation.

with recursive
runsum (id,amt,qty,oqty,sqty,avg) as
    (select data.id, data.amt, data.qty, data.oqty, data.sqty, data.avg
     from (
        select rn as id,amt,qty, oqty,
        sum(case when rn=0 then qty else
             case when oqty=0 then qty*-1
                else oqty end end) over (order by rn) as sqty, lag(amt) over (order by rn) as avg
          from test3 ) data
         ),
counter (maximum) as
         (select count(rn)
          from test3
         ),
trans (n, id,amt,qty,oqty,sqty,prevavg,avg) as
    (select 0 n, id,amt,qty,oqty, sqty,avg,avg
      from runsum
     union
    select trans.n+1, runsum.id,trans.amt,trans.qty, trans.oqty, trans.sqty,
    lag(trans.avg) over (order by 1),
    case when runsum.sqty=0 then runsum.amt else
    ((trans.prevavg*(runsum.sqty+trans.qty))-(runsum.amt*trans.qty)+(trans.prevavg*trans.oqty))/(runsum.sqty+trans.oqty)
    end
    from runsum join trans using (id)
    where trans.n<(select maximum*2 from counter))
select *
from trans
where prevavg is null and avg is not null
order by id;

The result is supposed to be like this

rn  amt        qty oqty sqty sum avg
1   1647727.273 3   0   5   2627424.705
2   2489654.753 0   1   6   2627424.705
3   2489654.753 0   1   7   2627424.705
4   1875443.636 1   0   6   2752754.883
5   1647727.273 3   0   3   3857782.493
6   3012987.013 0   1   4   3857782.493
7   3012987.013 0   1   5   3857782.493

I hope we could have something working in near future. So stay tuned.

Goodwill Consulting is a long time Adempiere / Idempiere supporter since their inception. We are offering software-as-a-service solution on the cloud based on Adempiere / Idempiere. For more information, you can drop us a visit at www.goodwillerp.com

Nov 27

Invoicing and payment terms

Commonly used invoice payment terms and their meanings

This list explains many of the terms commonly used on invoices.

Invoice payment terms

Net 7 Payment seven days after invoice date
Net 10 Payment ten days after invoice date
Net 30 Payment 30 days after invoice date
Net 60 Payment 60 days after invoice date
Net 90 Payment 90 days after invoice date
EOM End of month
21 MFI 21st of the month following invoice date
1 % 10 Net 30 1 per cent discount if payment received within ten days otherwise payment 30 days after invoice date
COD Cash on delivery
Cash account Account conducted on a cash basis, no credit
Letter of credit A documentary credit confirmed by a bank, often used for export
Bill of exchange A promise to pay at a later date, usually supported by a bank
CND Cash next delivery
CBS Cash before shipment
CIA Cash in advance
CWO Cash with order
1MD Monthly credit payment of a full month’s supply
2MD As above plus an extra calendar month
Contra Payment from the customer offset against the value of supplies purchased from the customer
Stage payment Payment of agreed amounts at stages

Many of them are working out-of-the-box in our Goodwill ERP. However in implementations we sometimes find a very unusual requirement. Like in one case, in Karawang, they sell  motorcycles with payment term “after harvesting” (what?) which means the customer will pay you after the next harvest season. Only in Indonesia, grrr…. :D

Anyway, if you need a special treatment for your business case, let us know. We can help you.

Nov 11

Payment Term with Fixed Payment Schedule date

How often you wish you could do something to tweak your ERP ? The blessing to use an open source ERP is you have access to its source codes. Isn’t it great if you could just make a lil bit change so you can get exactly what you want, and not to depend on what your software dictates you.

During one implementation in a car dealer, we were facing with one requirement to support installment sales. We thought it should be easy as we knew Adempiere has already payment schedule feature. Just open up Payment Term window and create a new payment term with Fixed Due Date being checked.

1-year-installment

1-year-installment

And then we entered all the schedules with equally divided percentages, for example if it is a one-year installment than the percentage is 100/12, which 8.3333. The net days is how Adempiere will determine the due date of each schedule, which in this case is the accumulation of 30 days that end up to 360 days (supposedly equal to one year length).

Payment Schedule

Payment Schedule

However, the Fixed Due Date option didn’t give what we expected. The Due date is falling on different date each month! And the worst is in one occasion, two schedules can due on the same month (see month of July in the picture below).

Order Schedule before tweak

Order Schedule before tweak

How it happens ? Because every month has a different length in days, you can’t just accumulate the net days with an exact 30-day length. That explains how we end up with 360 days a year, and not 365 days as in actual.

Luckily we are using open source ERP. There is nothing can stop us. The show must go on. So what we did it back in the day is to bring a new option called Fixed Payment Schedule date. This option will complement the existing Fixed Due date feature.

1-year-installment_fixed

1-year-installment_fixed

The following is how the schedule is generated using this new option. It exactly gives what our client wants that for each month of the installment period, the Due date will certainly fall into the same date. And that shows the power of open source ERP!

Order Schedule after fixed

Order Schedule after fixed

If you think this feature is worthy enough to be included in the next release of Adempiere or Idempiere, let us know. We will also be grateful to have your feedback. Until next time!

Goodwill Consulting is a long time Adempiere / Idempiere supporter since their inception. We are offering software-as-a-service solution on the cloud based on Adempiere / Idempiere. For more information, you can drop us a visit at www.goodwillerp.com

Oct 25

Pengaturan Akses Field di Adempiere

Ketika Anda dihadapkan pada situasi dimana Anda harus membatasi siapa yang bisa mengakses data tertentu, Anda tidak perlu khawatir karena Adempiere memiliki fitur Role-based Security Access yang sangat lengkap. Anda bisa mengatur akses data mulai dari level Tabel, Column atau Field, sampai ke Record. Dalam kesempatan ini, saya akan bahas bagaimana melakukan pengaturan akses field di Adempiere.

Misalnya, Anda diminta untuk membatasi siapa yang dapat mengganti Price List di jendela Sales Order. Ketika kita memasukkan Business Partner, field Price List ini akan terisi dengan Price List yang sudah diatur dalam master Business Partner tersebut. Sehingga idealnya, field ini tidak boleh diganti-ganti lagi. Staf Sales Admin dapat melihat field tersebut tetapi mereka tidak diperkenankan untuk mengubahnya. Hanya Sales Manager saja yang diperkenankan untuk mengubahnya.

Untuk melakukan pengaturan ini, Anda bisa membuka menu Role Data Access. Kemudian pilihlah Role yang hendak kita setup, misalnya role Sales Admin. Pindah ke tab Column Access, pilih tabel C_Order, lalu pilih kolom M_PriceList_ID. Centang Exclude dan Read Only. Simpan.

Ya, cukup begitu saja. Mudah bukan ? Sekarang Anda logout dan login dengan role Sales Admin. Bukalah menu Sales Order. Anda masih bisa menemukan field Price List tetapi Anda tidak dapat mengubah-ubahnya lagi. Jika Anda login sebagai Sales Manager, Anda masih dapat mengubah field tersebut.

Kasus lain, mungkin Anda harus menyembunyikan seluruh kolom yang berkaitan dengan harga, misalnya kolom Total Lines dan Grand Total di jendela Sales Order. Juga kolom Price, Unit Price, List Price dan Line Net Amount di tab Sales Order Line.

Anda bisa melakukan langkah-langkah yang sama seperti di atas. Jika Read Only tidak dicentang, maka berarti kolom-kolom tersebut tidak dapat dilihat sama sekali oleh role bersangkutan.

Namun demikian langkah-langkah ini akan sangat panjang karena jumlah kolom yang berkaitan dengan harga sangatlah banyak. Untuk itu tim Goodwill menambahkan satu fitur di dalam menu Role, dimana Anda cukup mengatur apakah role tersebut “Can Read Price” dan / atau “Can Read PO”.

Jika Anda mencentang “Can Read Price” maka role tersebut dapat membaca semua kolom yang berkaitan dengan harga, jika tidak dicentang maka sebaliknya role tersebut tidak dapat membaca harga, bahkan sampai termasuk harga-harga yang ada di dalam jendela Product Info, Order Info dan Invoice Info, serta harga-harga yang ada di dalam report tertentu.

akses field

Sedangkan “Can Read PO” mempunyai maksud apabila dicentang maka role tersebut dapat mengakses informasi mengenai harga beli dan harga pokok. Dengan membuang centangnya, maka Anda bisa memastikan role tersebut tidak dapat membaca informasi yang sangat rahasia ini.

Kedua fitur ini sangat memudahkan Anda untuk melakukan pengaturan yang sangat penting ini karena harga adalah salah satu informasi yang sangat sensitif dan patut dijaga dari pihak-pihak yang tidak berkepentingan.

Goodwill Consulting adalah bagian dari komunitas pengembang dan pendukung Adempiere. Jika Anda membutuhkan bantuan profesional untuk implementasi Adempiere, silahkan menghubungi kami.

Oct 24

Penerapan Cost Center atau Profit Center di Adempiere

Dalam implementasi Adempiere, sering kita dihadapkan pada pertanyaan bagaimana menerapkan Cost Center atau Profit Center dengan tepat. Adempiere menyediakan berbagai pilihan untuk model konsep ini.

Pertama, dan yang terpenting adalah pengaturan Organisasi. Di Adempiere, Anda dapat membuat entitas organisasi tanpa batas . Anda dapat menentukan dan membedakan berbagai jenis organisasi ( Divisi , Cabang, Anak Perusahaan, Sister Company, dll). Anda juga bisa menciptakan beberapa hirarki organisasi yang berbeda ( misalnya dari sudut pandang kepemilikian, geografis, pelaporan , dsb ) . Sebuah Organisasi setidaknya mempunyai Asset, entah itu berupa kas atau persediaan barang. Jadi ketika Anda membuat sebuah Organisasi, biasanya akan diikuti pula dengan pembuatan buku kas dan gudang.

Setiap transaksi dalam Adempiere (seperti Invoice dan Payment) harus diasosiasikan dengan suatu Organisasi, karena nantinya Organisasi tersebut juga dipakai dalam laporan pembukuan.

Organisasi dapat dihubungkan dengan Business Partner (Mitra Usaha) sehingga Anda bisa menciptakan transaksi antar Organisasi dengan mudah. Dari sudut pandang akuntansi, entitas Organisasi ini merupakan segment yang harus balance. Oleh karena itu, secara default, Adempiere akan menciptakan jurnal hutang & piutang inter-company secara otomatis sehingga Anda bisa mendapatkan laporan yang balance dari sudut mana pun. Adempiere bahkan juga mampu menciptakan dokumen lawan transaksi secara otomatis, jika fitur Counter Document diaktifkan.

Tetapi mungkin pula Anda tidak membutuhkan fitur ini. Mungkin Anda mempunyai bisnis proses yang melibatkan banyak transaksi antar unit, dan Anda sama sekali tidak membutuhkan tambahan jurnal inter-company. Dengan sedikit pengaturan, Anda bisa membuat Adempiere bekerja seperti yang Anda inginkan.

Dalam Adempiere, ada dua segment Organisasi yakni Organization (AD_Org_ID) dan Transaction Organization (AD_OrgTrx_ID). Anda bisa menempatkan badan usaha (legal entity) sebagai Organization, sementara unit-unit usaha yang lebih kecil sebagai Transaction Organization. Adempiere tidak akan menciptakan tambahan jurnal inter-company pada  segment Transaction Organization.

Contoh implementasi seperti ini adalah pada perusahaan dealer mobil yang memiliki 10 cabang yang masing-masing memiliki 1 sampai 3 unit bisnis yakni Sales, Spareparts dan Services. Karena cabang adalah legal entity (NPWP sendiri, laporan neraca dan rugi laba yang terpisah), maka Anda menempatkan cabang sebagai Organization. Sedangkan 3 unit bisnis tadi sebagai Transaction Organization. Dengan demikian, Anda dapat membagi satu Invoice ke dalam beberapa unit bisnis ( misalnya baris transaksi jasa masuk ke unit Services dan baris transaksi barang masuk ke unit Spareparts ).

Dengan sedikit customization, Anda bisa melakukan pengaturan misalnya cabang X mempunyai ketiga unit tersebut sementara cabang Y hanya memiliki unit Sales saja. Tekniknya adalah cukup dengan menggunakan Dynamic Validation. Untuk kasus yang lebih kompleks, Anda bisa juga menerapkan Model Validation.

Apabila kondisi Cost Center mempunyai struktur yang lebih lepas (tidak terkait dengan struktur Organisasi, tidak mempunyai Asset), Anda cukup menggunakan segment Activity. Semoga tulisan ini bermanfaat dan memberikan pencerahan.

Goodwill Consulting adalah bagian dari komunitas pengembang dan pendukung Adempiere. Jika Anda membutuhkan bantuan profesional untuk implementasi Adempiere, silahkan menghubungi kami.