you're reading...
ACCURATE, Report

Menampilkan Qty Produksi di Report Sales Order History

Misal diketahui ada Sales Order dengan kategori barang Finished Good. Barang tersebut akan dilakukan kembali proses pengerjaannya di modul manufactured. Jika kita ingin mengetahui berapa qty atas barang tersebut yang sudah di release dan result di Report Sales Order History, berikut ini langkah panduannya :

  • Masuk ke dalam Report | Index To Report | Sales Order History
  • Masuk ke tab Available Columns |  double klik di bagian Calculated Fieldnya (Contoh Calculated Field1) | Selected Columns & filters | ceklist Calculated Field yang sudah di masukkan.
  • Klik DesignerData |  double click IBX First_Query

    Solcen Juni 1

  • Edit SQLnya pada bagian sebelum baris “From” ganti baris :

    GET_REPORTCUSTOMFIELD.CUSTOMFIELD2 COLUMNVALUE_18menjadi :

    GET_WODETFINISHEDPORTION.FINISHEDQTY COLUMNVALUE_18
  • Sebelum baris “WHERE 1=1” tambahkan baris :
    LEFT JOIN WODET ON WODET.SOID = SO.SOID
    LEFT JOIN WO ON WO.ID = WODET.WOID
    LEFT OUTER JOIN GET_WODETFINISHEDPORTION (WODET.ID) ON WODET.ID = GET_WODETFINISHEDPORTION.WODETID
  • Sehingga SQLnya menjadi :
    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_WODETFINISHEDPORTION.FINISHEDQTY COLUMNVALUE_18
    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 JOIN WODET ON WODET.SOID = SO.SOID
    LEFT JOIN WO ON WO.ID = WODET.WOID
    LEFT OUTER JOIN GET_WODETFINISHEDPORTION (WODET.ID) ON WODET.ID = GET_WODETFINISHEDPORTION.WODETID
    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
  • Close | Save

Berikut ini contoh reportnya, silahkan di download disini

(Available for V4)

Advertisements

Discussion

Comments are closed.

%d bloggers like this: