Apabila Anda ingin menampilkan nilai Existing Credit dari suatu customer yang dihasilkan dari kondisi lebih bayar customer kepada perusahaan, berikut ini langkah-langkahnya jika ingin menampilkannya pada saat mencetak atau preview Sales Order.
- Setup | Form Template | Sales Invoice. Edit Template Sales Order yang dimaksud.
- Klik button Design As frf, lalu pilih Page2. Klik sekali di IBX ke dua, kemudian tekan F11 di keyboard, double klik bagian SQL.
- Di SQL Editor tsb, tambahkan “(select sum(OverPay-OverPayused) from ARPMT where BilltoID=a.CustomerID) ExistingCredit” sebelum “from SO a”
sehingga secara keseluruhan menjadi :
select a.SONo InvoiceNo, a.SODate InvoiceDate, a.ShipTo1, a.ShipTo2,
a.ShipTo3, a.ShipTo4, a.ShipTo5, a.PoNo PurchaseOrderNo, a.FOB,
a.EstShipDate ShipDate, a.Description, a.CashDiscount, a.InvAmount InvoiceAmount,
a.DP Payment, a.DPUsed, a.Rate, a.Tax1Amount, a.Tax2Amount, a.RoundedTax1Amount,
a.RoundedTax2Amount,a.Tax1Rate, a.Tax2Rate,a.CashDiscPC, a.Freight,
a.TaxInclusive InclusiveTax, a.CustomerIsTaxable, x1.TaxName TaxName1,
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.TaxAddress1, c.TaxAddress2,
c.Fax, c.Email, c.WebPage,c.TAX2EXEMPTIONNO,
t.TERMNAME, t.NetDays, t.TERMMEMO, cu.CURRENCYNAME, s.FIRSTNAME, s.LASTNAME, v.NAME SHIPNAME,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,
(select sum(OverPay-OverPayused) from ARPMT where BilltoID=a.CustomerID) ExistingCredit
from SO 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.Terms
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
Where a.SOID=:SOID
a.ShipTo3, a.ShipTo4, a.ShipTo5, a.PoNo PurchaseOrderNo, a.FOB,
a.EstShipDate ShipDate, a.Description, a.CashDiscount, a.InvAmount InvoiceAmount,
a.DP Payment, a.DPUsed, a.Rate, a.Tax1Amount, a.Tax2Amount, a.RoundedTax1Amount,
a.RoundedTax2Amount,a.Tax1Rate, a.Tax2Rate,a.CashDiscPC, a.Freight,
a.TaxInclusive InclusiveTax, a.CustomerIsTaxable, x1.TaxName TaxName1,
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.TaxAddress1, c.TaxAddress2,
c.Fax, c.Email, c.WebPage,c.TAX2EXEMPTIONNO,
t.TERMNAME, t.NetDays, t.TERMMEMO, cu.CURRENCYNAME, s.FIRSTNAME, s.LASTNAME, v.NAME SHIPNAME,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,
(select sum(OverPay-OverPayused) from ARPMT where BilltoID=a.CustomerID) ExistingCredit
from SO 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.Terms
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
Where a.SOID=:SOID
Tidak ada komentar:
Posting Komentar