Menampilkan PO No. di Report Outstanding Payable pada Accurate Dekstop
Langkah-langkah untuk menampilkan PO No. di Report Outstanding Payable, adalah:
- Klik menu Report | Index to Reports | Accounting Payables & Vendor | 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.INVOIC
EDATE, :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 LEFT OUTER 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 tersebut.
Notes:
Jika Calculate Field yang digunakan adalah baris ke-19 maka COLUMNVALUE_19, apabila baris ke-20 maka COLUMNVALUE_20, dst.
(Available for V4 & V5)
Leave a Reply
Want to join the discussion?Feel free to contribute!