Menampilkan Prefered Vendor Di Report Sales Order

Berikut ini adalah cara untuk menampilkan Prefered Vendor yang ada pada master barang pada Sales Order Report (Laporan Pesanan Penjualan).

  • Masuk ke dalam Report Sales Order History

  • Klik Designer | di tab Data double klik IBX First_Query

  • Sebelum baris From tambahkan baris : ,b.personno, b.name

  • Sebelum baris WHERE 1=1 tambahkan baris : left outer join persondata b on SODET_ITEMNO.PREFEREDVENDOR=b.id

  • Jadi nantinya keseluruhan atas query SQL nya :

SELECT
SO_HISTORY.PROCEEDTYPE COLUMNVALUE_1,
SO_HISTORY.INVOICEID PRIMARYKEYVALUE_50001,
SO_HISTORY.PROCEEDINVOICENO COLUMNVALUE_2,
SO_HISTORY.PROCEEDDATE COLUMNVALUE_3,
SO_HISTORY.PROCEEDQUANTITY COLUMNVALUE_4,
SODET.QTYSHIPPED COLUMNVALUE_5,
SODET.SOID PRIMARYKEYVALUE_30001,
GET_REPORTCUSTOMFIELD.CUSTOMFIELD1 COLUMNVALUE_6,
GET_REPORTCUSTOMFIELD.PKFIELD PRIMARYKEYVALUE_60001,
DATA_COUNT.DATACOUNT COLUMNVALUE_7,
DATA_COUNT.DATACOUNT PRIMARYKEYVALUE_70001,
SO.SONO COLUMNVALUE_8,
SO.SOID PRIMARYKEYVALUE_10001,
SO.SODATE COLUMNVALUE_9,
SO_CUSTOMERID.NAME COLUMNVALUE_10,
SO_CUSTOMERID.ID PRIMARYKEYVALUE_64,
GET_OTHERSODATA.STATUS COLUMNVALUE_11,
GET_OTHERSODATA.SOID PRIMARYKEYVALUE_20001,
SO_HISTORY.SEQ COLUMNVALUE_12,
SODET_ITEMNO.ITEMNO COLUMNVALUE_13,
SODET_ITEMNO.ITEMNO PRIMARYKEYVALUE_144,
SODET.ITEMOVDESC COLUMNVALUE_14,
SODET.QUANTITY COLUMNVALUE_15,
SODET.ITEMUNIT COLUMNVALUE_16,
SO_HISTORY.PROCEEDGLHISTID COLUMNVALUE_17,
GET_REPORTCUSTOMFIELD.CUSTOMFIELD2 COLUMNVALUE_18,
b.personno, b.name
FROM
SO SO
LEFT OUTER JOIN GET_OTHERSODATA (SO.SOID) ON SO.SOID=GET_OTHERSODATA.SOID
LEFT OUTER JOIN SODET ON SO.SOID=SODET.SOID
LEFT OUTER JOIN GET_VALUE_SODET (SODET.SEQ, SO.SOID) ON SO.SOID=GET_VALUE_SODET.SOID AND SODET.SEQ=GET_VALUE_SODET.SEQ
LEFT OUTER JOIN SO_HISTORY (SODET.SOID, SODET.SEQ) ON SODET.SOID=SO_HISTORY.SOID AND SODET.SEQ=SO_HISTORY.SEQ
LEFT OUTER JOIN GET_REPORTCUSTOMFIELD ON 1=1
LEFT OUTER JOIN DATA_COUNT ON 1=1
LEFT OUTER JOIN PERSONDATA SO_CUSTOMERID ON SO.CUSTOMERID=SO_CUSTOMERID.ID
LEFT OUTER JOIN ITEM SODET_ITEMNO ON SODET.ITEMNO=SODET_ITEMNO.ITEMNO
left outer join persondata b on SODET_ITEMNO.PREFEREDVENDOR=b.id
WHERE 1=1
AND (SO.SODATE BETWEEN :PARAMDATE1 AND :PARAMDATE2) AND SO_HISTORY.PROCEEDINVOICENO IS NOT NULL
ORDER BY
SO.SONO ASC,
SO_HISTORY.SEQ ASC,
SO.SODATE ASC,
SO_HISTORY.PROCEEDGLHISTID ASC

  • Setelah perubahan di Query SQL-nya kita commit kita klik tab ReportPage

  • Isikan memo Caltulated Field dengan [First_Query.”NAME”]

  • save dalam format .FR3

Silahkan download contoh Reportnya disini

(Available for V4 & V5)