Menampilkan PO No di Laporan Accurate Report Outstanding Payable untuk memunculkan di laporan, untuk cara settingnya silahakan lihat tutorial dibawah ini.

Langkah-langkah untuk Menampilkan PO No di Laporan Accurate, adalah:

  • Klik menu Reports | Index to Reports | Account Payables & Vendors | Outstanding Payable;
  • Klik Modify | Available Columns | Double klik Calculate Field 1;
  • Klik Columns & Filters | Centang Calculate Field 1 | Klik OK;

Klik Designer;

  • Klik Tab Data | Klik IBX, tekan F11 pada keyboard | Klik tanda titik tiga pada kolom SQL;
  • Ubah Variable

 GET_REPORTCUSTOMFIELD.CUSTOMFIELD1 COLUMNVALUE_19,

menjadi

(select LIST (distinct PO.PONO) FROM PO where PO.POID in (select POID from APITMDET itmdet where itmdet.APINVOICEID=APINV.APINVOICEID)) COLUMNVALUE_19,

dan hasil keseluruhan adalah

SELECT

APINV.INVOICENO COLUMNVALUE_1,

APINV.APINVOICEID PRIMARYKEYVALUE_10001,

APINV.INVOICEDATE COLUMNVALUE_2,

GET_TERMSDUE.DUEDATE COLUMNVALUE_3,

GET_TERMSDUE.TERMID PRIMARYKEYVALUE_20001,

APINV.INVOICEAMOUNT COLUMNVALUE_4,

OWING_PI.OWING COLUMNVALUE_5,

OWING_PI.APINVOICEID PRIMARYKEYVALUE_30001,

OWING_PI.OWINGDC COLUMNVALUE_6,

GET_TERMSDUE.AGEFRDUE COLUMNVALUE_7,

GET_PROPERTYOFTIME.REPORTDATE COLUMNVALUE_8,

GET_PROPERTYOFTIME.PROPERTYDATE PRIMARYKEYVALUE_40001,

GET_PROPERTYOFTIME.REPORTWEEK COLUMNVALUE_9,

GET_PROPERTYOFTIME.REPORTPERIOD COLUMNVALUE_10,

GET_PROPERTYOFTIME.REPORTQUARTER COLUMNVALUE_11,

GET_PROPERTYOFTIME.REPORTYEAR COLUMNVALUE_12,

GET_PROPERTYOFTIME.MONTHCOMP COLUMNVALUE_13,

GET_PROPERTYOFTIME.QUARTERCOMP COLUMNVALUE_14,

DATA_COUNT.DATACOUNT COLUMNVALUE_15,

DATA_COUNT.DATACOUNT PRIMARYKEYVALUE_60001,

APINV_VENDORID_CURRENCYID.CURRENCYNAME COLUMNVALUE_16,

APINV_VENDORID_CURRENCYID.CURRENCYID PRIMARYKEYVALUE_102,

APINV_VENDORID.PERSONNO COLUMNVALUE_17,

APINV_VENDORID.ID PRIMARYKEYVALUE_70,

APINV_VENDORID.NAME COLUMNVALUE_18,

(select LIST (distinct PO.PONO) FROM PO where PO.POID in (select POID from APITMDET itmdet where itmdet.APINVOICEID=APINV.APINVOICEID)) COLUMNVALUE_19,

GET_REPORTCUSTOMFIELD.PKFIELD PRIMARYKEYVALUE_50001

 

FROM

APINV APINV

LEFT OUTER JOIN GET_TERMSDUE (APINV.APINVOICEID, APINV.INVOICEDATE, :PARAMDATE1, APINV.TERMSID) ON GET_TERMSDUE.TERMID=APINV.TERMSID

LEFT OUTER JOIN OWING_PI (:PARAMDATE1, APINV.APINVOICEID) ON OWING_PI.APINVOICEID=APINV.APINVOICEID

LEFT OUTER JOIN GET_PROPERTYOFTIME (APINV.INVOICEDATE, :PARAMDATE1, :PARAMDATE1) ON APINV.INVOICEDATE=GET_PROPERTYOFTIME.PROPERTYDATE

LEFT OUTER JOIN GET_REPORTCUSTOMFIELD ON 1=1

LEFTOUTER JOIN DATA_COUNT ON 1=1

LEFT OUTER JOIN PERSONDATA APINV_VENDORID ON APINV.VENDORID=APINV_VENDORID.ID

LEFT OUTER JOIN CURRENCY APINV_VENDORID_CURRENCYID ON APINV_VENDORID.CURRENCYID=APINV_VENDORID_CURRENCYID.CURRENCYID

WHERE 1=1

AND (NOT OWING_PI.OWING IS NULL AND NOT OWING_PI.OWINGDC IS NULL) AND (OWING_PI.OWING<>0 OR OWING_PI.OWINGDC<>0) AND APINV.INVOICEDATE<=:PARAMDATE1

ORDER BY

APINV_VENDORID_CURRENCYID.CURRENCYNAME ASC,

APINV_VENDORID.PERSONNO ASC,

APINV.INVOICEDATE ASC

 

 

  • Klik Ok atau tanda centang dan save report tsb.

Notes:

Jika Calculate Field yang digunakan adalah baris ke-19 maka COLUMNVALUE_19, apabila baris ke-20 maka COLUMNVALUE_20, dst.

 

Manualbook Accurate

 

Related Posts: