Menampilkan Existing Credit Customer di preview Sales Invoice

  1. Setup | Form Template | Sales Invoice. Edit Template yang dimaksud.
  2. Klik button Design As frf, lalu pilih Page2. Klik sekali di IBX ke dua, kemudian tekan F11 di keyboard, double klik bagian SQL.
  3. Di SQL Editor tsb,  tambahkan “(select sum(OverPay-OverPayused) from ARPMT where BilltoID=a.CustomerID) ExistingCredit” sebelum “from ARINV a“

Sehingga secara keseluruhan menjadi :

select a.*,
x1.TaxName TaxName1, x1.Rate Tax1Rate, x2.TaxName TaxName2,
c.ADDRESSLINE1, c.ADDRESSLINE2, c.CITY, c.CONTACT, c.COUNTRY, c.DEFAULTINVDESCRIPTION,
c.NAME, c.PERSONNO, c.PHONE, c.STATEPROV, c.ZIPCODE, c.TAX1EXEMPTIONNO, c.PersonMessage,
c.Fax, c.Email, c.WebPage, c.TaxAddress1, c.TaxAddress2, c.TAX2EXEMPTIONNO,
t.TERMNAME, t.NetDays, t.TERMMEMO, cu.CURRENCYNAME, s.FIRSTNAME, s.LASTNAME, v.NAME SHIPNAME,
w.Name WarehouseName, br.ExchangeRate BankRate
,c.CHRRESERVED1,c.CHRRESERVED2,c.CHRRESERVED3,c.CHRRESERVED4,
c.CHRRESERVED5,c.CHRRESERVED6,c.CHRRESERVED7,c.CHRRESERVED8,
c.CHRRESERVED9,c.CHRRESERVED10,c.CURRRESERVED1,c.CURRRESERVED2,
c.CURRRESERVED3,c.DATERESERVED1,c.DATERESERVED2, sn.SNList,
(select sum(OverPay-OverPayused) from ARPMT where BilltoID=a.CustomerID)
ExistingCredit
from ARINV a
Inner join PersonData c on a.CustomerID=c.ID
Inner Join Currency cu on c.CurrencyID=cu.CurrencyID
Left Outer Join TermOPmt t on t.termID=a.TermsID
Left Outer Join Salesman s on a.SalesmanID=s.SalesmanID
Left Outer Join Shipment v on a.ShipVia=v.ShipID
Left Outer Join Tax x1 ON x1.TaxID=a.Tax1ID
Left Outer Join Tax x2 ON x2.TaxID=a.Tax2ID
Left Outer Join WareHS w ON w.WarehouseID=a.WarehouseID
Left Outer Join Currency br on br.CurrencyID=(select CurrencyID from GLAccnt where GLAccount=a.DepositTo)
Left Outer Join View_SN_ByTrans(a.ARInvoiceID, ‘ARINV’) sn ON sn.ID=a.ARInvoiceID
Where a.ARInvoiceID=:ARINVOICEID

4. Kemudian klik tanda centang(ok), kembali ke Page1.

5. Masih di Designer ke menu File | Data Disctionary – pilih folder Invoice,  kemudian klik icon New Variable dibawah, akan muncul satu field NewVariable rename menjadi “ExistingCredit“. Biarkan posisi kita masih di ExistingCredit tsb,  Values-nya(kolom bagian kanan) kita isi dengan cara memilih “EXISTINGCREDIT” dari “DialoqForm.qrySIARInv“.

6. Close Designer, pilih Yes untuk konfirmasi Save Changes.

7. Pilih Designer lalu klik Insert Rectangle Object lalu klik dimana kita mau meletakkan informasi Existing Credit tersebut, lalu isi  Text Editor dengan [ExistingCredit] lalu klik tanda centang(ok) lalu klik Close di designer pilih Yes untuk konfirmasi Save Changes.

Gallery | This entry was posted in Template. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s