Re-engineering Ellipse report: MSR179

Bagi Anda pengguna salah satu aplikasi ERP, yaitu Ellipse (bikinan Mincom), mungkin tahu report MSR179.RDL (Monthly Supply Management Reports). Ini adalah salah satu report Ellipse yang paling ruwet (disamping bahasanya yang sudah generasi jadul) juga karena intens menggunakan rutin dari Cobol.

Karena suatu hal, saya harus melakukan re-engineering report tersebut ke dalam bahasa lain yang lebih applicable. Dalam hal ini saya mengkonversi ke PLSQL Oracle. Berikut querynya:

SELECT FULL_PERIOD,INVT_STAT_CODE,STOCK_CODE,TRAN_TYPE,TRAN_CATEGORY,
PO_REQ_NO,
PO_REQ_ITEM_NO,
ACCOUNT_CODE,
EQUIP_CODE,
WORK_ORDER_NO,
PROJECT_NO,
(CASE WHEN INV_MOV_VAL<0 THEN ABS(QUANTITY)*(-1) ELSE QUANTITY END) QTY,
TRANS_PRICE,
INV_MOV_VAL FROM (
SELECT A.FULL_PERIOD,
(SELECT f170.INVT_STAT_CODE
FROM ELLIPSE.MSF170 f170
WHERE f170.STOCK_CODE=B.STOCK_CODE
AND f170.DSTRCT_CODE=B.DSTRCT_CODE) INVT_STAT_CODE,
B.STOCK_CODE,
A.TRAN_TYPE,
(CASE WHEN A.TRAN_TYPE IN ('SRD','SRO','SCR') THEN 'RECEIPT'
WHEN A.TRAN_TYPE IN ('FAO','ISS','RSI') THEN 'ISSUE'
WHEN A.TRAN_TYPE IN ('ADJ','ADM','ORD','ORF','STO') THEN 'ADJUSTMENT'
ELSE 'UNKNOWN' END) TRAN_CATEGORY,
(CASE WHEN A.TRAN_TYPE IN ('SRD','SRO','SCR')
THEN (SELECT R9.PO_NO_R FROM ELLIPSE.MSF900_R R9
WHERE R9.TRANSACTION_NO=B.TRANSACTION_NO
AND R9.PROCESS_DATE=A.PROCESS_DATE
AND R9.STOCK_CODE_R=B.STOCK_CODE
AND R9.DSTRCT_CODE=B.DSTRCT_CODE)
WHEN A.TRAN_TYPE IN ('ORD','ORF')
THEN (SELECT B9.PO_NO_B FROM ELLIPSE.MSF900_B B9
WHERE B9.TRANSACTION_NO=B.TRANSACTION_NO
AND B9.PROCESS_DATE=A.PROCESS_DATE
AND B9.STOCK_CODE_B=B.STOCK_CODE
AND B9.DSTRCT_CODE=B.DSTRCT_CODE)
WHEN A.TRAN_TYPE IN ('ISS','ISI','COS','ICO','IST','RSI')
THEN (SELECT S9.ISSUE_REQ_NO_S FROM ELLIPSE.MSF900_S S9
WHERE S9.TRANSACTION_NO=B.TRANSACTION_NO
AND S9.PROCESS_DATE=A.PROCESS_DATE
AND S9.STOCK_CODE_S=B.STOCK_CODE
AND S9.DSTRCT_CODE=B.DSTRCT_CODE)
ELSE NULL END) PO_REQ_NO,
(CASE WHEN A.TRAN_TYPE IN ('SRD','SRO','SCR')
THEN (SELECT R9.PO_ITEM_R FROM ELLIPSE.MSF900_R R9
WHERE R9.TRANSACTION_NO=B.TRANSACTION_NO
AND R9.PROCESS_DATE=A.PROCESS_DATE
AND R9.STOCK_CODE_R=B.STOCK_CODE
AND R9.DSTRCT_CODE=B.DSTRCT_CODE)
WHEN A.TRAN_TYPE IN ('ORD','ORF')
THEN (SELECT B9.PO_ITEM_B FROM ELLIPSE.MSF900_B B9
WHERE B9.TRANSACTION_NO=B.TRANSACTION_NO
AND B9.PROCESS_DATE=A.PROCESS_DATE
AND B9.STOCK_CODE_B=B.STOCK_CODE
AND B9.DSTRCT_CODE=B.DSTRCT_CODE)
WHEN A.TRAN_TYPE IN ('ISS','ISI','COS','ICO','IST','RSI')
THEN (SELECT S9.IREQ_ITEM_NO_S FROM ELLIPSE.MSF900_S S9
WHERE S9.TRANSACTION_NO=B.TRANSACTION_NO
AND S9.PROCESS_DATE=A.PROCESS_DATE
AND S9.STOCK_CODE_S=B.STOCK_CODE
AND S9.DSTRCT_CODE=B.DSTRCT_CODE)
ELSE NULL END) PO_REQ_ITEM_NO,
A.ACCOUNT_CODE,
(CASE WHEN A.TRAN_TYPE IN ('ISS','ISI','COS','ICO','IST','RSI')
THEN (SELECT S9.EQUIP_NO_S FROM ELLIPSE.MSF900_S S9
WHERE S9.TRANSACTION_NO=B.TRANSACTION_NO
AND S9.PROCESS_DATE=A.PROCESS_DATE
AND S9.STOCK_CODE_S=B.STOCK_CODE
AND S9.DSTRCT_CODE=B.DSTRCT_CODE)
WHEN A.TRAN_TYPE='FAO'
THEN (SELECT F9.EQUIP_NO_F FROM ELLIPSE.MSF900_F F9
WHERE F9.TRANSACTION_NO=B.TRANSACTION_NO
AND F9.PROCESS_DATE=A.PROCESS_DATE
AND F9.STOCK_CODE_F=B.STOCK_CODE
AND F9.DSTRCT_CODE=B.DSTRCT_CODE)
ELSE NULL END) EQUIP_CODE,
(CASE WHEN A.TRAN_TYPE IN ('ISS','ISI','COS','ICO','IST','RSI')
THEN (SELECT S9.WORK_ORDER_S FROM ELLIPSE.MSF900_S S9
WHERE S9.TRANSACTION_NO=B.TRANSACTION_NO
AND S9.PROCESS_DATE=A.PROCESS_DATE
AND S9.STOCK_CODE_S=B.STOCK_CODE
AND S9.DSTRCT_CODE=B.DSTRCT_CODE)
ELSE NULL END) WORK_ORDER_NO,
(CASE WHEN A.TRAN_TYPE IN ('ISS','ISI','COS','ICO','IST','RSI')
THEN (SELECT S9.PROJECT_NO_S FROM ELLIPSE.MSF900_S S9
WHERE S9.TRANSACTION_NO=B.TRANSACTION_NO
AND S9.PROCESS_DATE=A.PROCESS_DATE
AND S9.STOCK_CODE_S=B.STOCK_CODE
AND S9.DSTRCT_CODE=B.DSTRCT_CODE)
ELSE NULL END) PROJECT_NO,
(CASE WHEN A.TRAN_TYPE IN ('SRD','SRO','SCR')
THEN (SELECT R9.QTY_RCV_UOI_R FROM ELLIPSE.MSF900_R R9
WHERE R9.TRANSACTION_NO=B.TRANSACTION_NO
AND R9.PROCESS_DATE=A.PROCESS_DATE
AND R9.STOCK_CODE_R=B.STOCK_CODE
AND R9.DSTRCT_CODE=B.DSTRCT_CODE)
WHEN A.TRAN_TYPE IN ('ORD','ORF')
THEN (SELECT B9.QTY_ADJ_UOI_B FROM ELLIPSE.MSF900_B B9
WHERE B9.TRANSACTION_NO=B.TRANSACTION_NO
AND B9.PROCESS_DATE=A.PROCESS_DATE
AND B9.STOCK_CODE_B=B.STOCK_CODE
AND B9.DSTRCT_CODE=B.DSTRCT_CODE)
WHEN A.TRAN_TYPE IN ('STO','ADJ','ADM')
THEN (SELECT A9.QTY_ADJ_UOI_A FROM ELLIPSE.MSF900_A A9
WHERE A9.TRANSACTION_NO=B.TRANSACTION_NO
AND A9.PROCESS_DATE=A.PROCESS_DATE
AND A9.STOCK_CODE_A=B.STOCK_CODE
AND A9.DSTRCT_CODE=B.DSTRCT_CODE)
WHEN A.TRAN_TYPE='FAO'
THEN (SELECT F9.LITRES_ISS_F FROM ELLIPSE.MSF900_F F9
WHERE F9.TRANSACTION_NO=B.TRANSACTION_NO
AND F9.PROCESS_DATE=A.PROCESS_DATE
AND F9.STOCK_CODE_F=B.STOCK_CODE
AND F9.DSTRCT_CODE=B.DSTRCT_CODE)
WHEN A.TRAN_TYPE IN ('ISS','ISI','COS','ICO','IST','RSI')
THEN (SELECT S9.QUANTITY_ISS_S FROM ELLIPSE.MSF900_S S9
WHERE S9.TRANSACTION_NO=B.TRANSACTION_NO
AND S9.PROCESS_DATE=A.PROCESS_DATE
AND S9.STOCK_CODE_S=B.STOCK_CODE
AND S9.DSTRCT_CODE=B.DSTRCT_CODE)
ELSE NULL END) QUANTITY,
(CASE WHEN A.TRAN_TYPE IN ('SRD','SRO','SCR')
THEN (SELECT R9.NET_PR_UOI_R FROM ELLIPSE.MSF900_R R9
WHERE R9.TRANSACTION_NO=B.TRANSACTION_NO
AND R9.PROCESS_DATE=A.PROCESS_DATE
AND R9.STOCK_CODE_R=B.STOCK_CODE
AND R9.DSTRCT_CODE=B.DSTRCT_CODE)
WHEN A.TRAN_TYPE IN ('ORD','ORF')
THEN (SELECT B9.NET_PR_ADJ_I_B FROM ELLIPSE.MSF900_B B9
WHERE B9.TRANSACTION_NO=B.TRANSACTION_NO
AND B9.PROCESS_DATE=A.PROCESS_DATE
AND B9.STOCK_CODE_B=B.STOCK_CODE
AND B9.DSTRCT_CODE=B.DSTRCT_CODE)
WHEN A.TRAN_TYPE IN ('STO','ADJ','ADM')
THEN (SELECT A9.PRICE_CHANGE_A FROM ELLIPSE.MSF900_A A9
WHERE A9.TRANSACTION_NO=B.TRANSACTION_NO
AND A9.PROCESS_DATE=A.PROCESS_DATE
AND A9.STOCK_CODE_A=B.STOCK_CODE
AND A9.DSTRCT_CODE=B.DSTRCT_CODE)
WHEN A.TRAN_TYPE='FAO'
THEN (SELECT F9.RATE_AMOUNT_F FROM ELLIPSE.MSF900_F F9
WHERE F9.TRANSACTION_NO=B.TRANSACTION_NO
AND F9.PROCESS_DATE=A.PROCESS_DATE
AND F9.STOCK_CODE_F=B.STOCK_CODE
AND F9.DSTRCT_CODE=B.DSTRCT_CODE)
WHEN A.TRAN_TYPE IN ('ISS','ISI','COS','ICO','IST','RSI')
THEN (SELECT S9.AVERAGE_PR_S FROM ELLIPSE.MSF900_S S9
WHERE S9.TRANSACTION_NO=B.TRANSACTION_NO
AND S9.PROCESS_DATE=A.PROCESS_DATE
AND S9.STOCK_CODE_S=B.STOCK_CODE
AND S9.DSTRCT_CODE=B.DSTRCT_CODE)
ELSE NULL END) TRANS_PRICE,
(CASE WHEN A.TRAN_TYPE IN ('ISS','ISI','COS','ICO','IST','RSI','ISC','ICI','COC','ICC','ISD')
THEN (SELECT (CASE WHEN (NVL((SELECT SUBSTR(a.ASSOC_REC,1,1) FROM ELLIPSE.MSF010 a
WHERE a.TABLE_TYPE='TC'
AND a.TABLE_CODE = s.TAX_CODE_S),'0') IN ('4','5','6'))
OR (s.TAX_PERCENT_S=0)
THEN (A.TRAN_AMOUNT-(s.ON_COST_AMT_S+s.WH_ONCOST_AMT_S+s.FREIGHT_AMT_S))
WHEN s.TAX_PERCENT_S>0
THEN (A.TRAN_AMOUNT-(s.ON_COST_AMT_S+s.WH_ONCOST_AMT_S+s.FREIGHT_AMT_S))/
((100+s.TAX_PERCENT_S)/100)
ELSE ((A.TRAN_AMOUNT-(s.ON_COST_AMT_S+s.WH_ONCOST_AMT_S+s.FREIGHT_AMT_S))-
(s.TAX_PERCENT_S*(A.TRAN_AMOUNT-(s.ON_COST_AMT_S+s.WH_ONCOST_AMT_S+
s.FREIGHT_AMT_S))/100))
END)
FROM ELLIPSE.MSF900_S s
WHERE S.TRANSACTION_NO=B.TRANSACTION_NO
AND s.PROCESS_DATE=A.PROCESS_DATE
AND s.STOCK_CODE_S=B.STOCK_CODE
AND s.DSTRCT_CODE=B.DSTRCT_CODE)
WHEN A.TRAN_TYPE IN ('FAO','FAC' )
THEN (SELECT (CASE WHEN (NVL((SELECT SUBSTR(a.ASSOC_REC,1,1) FROM ELLIPSE.MSF010 a
WHERE a.TABLE_TYPE='TC'
AND a.TABLE_CODE = f.TAX_CODE_F),'0') IN ('4','5','6'))
OR (f.TAX_PERCENT_F=0)
THEN (A.TRAN_AMOUNT-(f.ON_COST_AMT_F+f.FREIGHT_AMT_F))
WHEN f.TAX_PERCENT_F>0
THEN (A.TRAN_AMOUNT-(f.ON_COST_AMT_F+f.FREIGHT_AMT_F))/((100+f.TAX_PERCENT_F)/100)
ELSE ((A.TRAN_AMOUNT-(f.ON_COST_AMT_F+f.FREIGHT_AMT_F))-
(f.TAX_PERCENT_F*(A.TRAN_AMOUNT-(f.ON_COST_AMT_F+f.FREIGHT_AMT_F))/100))
END)
FROM ELLIPSE.MSF900_F f
WHERE F.TRANSACTION_NO=B.TRANSACTION_NO
AND f.PROCESS_DATE=A.PROCESS_DATE
AND f.STOCK_CODE_F=B.STOCK_CODE
AND f.DSTRCT_CODE=B.DSTRCT_CODE)
ELSE A.TRAN_AMOUNT END) INV_MOV_VAL
FROM ELLIPSE.MSF900 A,
ELLIPSE.MSFX96 B
WHERE B.DSTRCT_CODE=A.DSTRCT_CODE
AND B.PROCESS_DATE=A.PROCESS_DATE
AND B.TRANSACTION_NO=A.TRANSACTION_NO
AND B.USERNO = A.USERNO
AND A.TRAN_TYPE IN ('ADJ','ADM','ORD','ORF','STO','FAO','ISS','RSI','SCR','SRD','SRO')
) WHERE FULL_PERIOD=:ACC_PERIOD

ACC_PERIOD adalah variabel string, yang mengidentifikasi periode mana yang akan diambil. Format yang digunakan adalah YYYYMM (misal: ‘200804’) .

Anda bisa juga menggunakan query diatas untuk kepentingan lainnya, karena outputnya tersaji dalam bentuk detilnya (per-transaction type).

Selamat mencoba!