SELECT A.PUR_NO , A.LPO_NO, A.REF_NO, A.PUR_DATE, A.DIVISION_ID, INITCAP(B.DIVISION_NAME) AS DIVISION_NAME,
INITCAP(E.STORE_NAME) AS STORE_NAME, A.SUPPLIER_ID, INITCAP(D.SUPPLIER_NAME) AS SUPPLIER_NAME,
(D.CONTACT_NO1 || D.CONTACT_NO2) AS CONTACT_NO, A.DISCOUNT, A.OTHER_CHARGES,
(SELECT
(SUM( ROUND((CASE WHEN NVL(B.QTY,0) = 0
THEN NULL
ELSE
((NVL(B.RATE,0) /
(select sum(nvl(rate,0)) from inv.purchase_c where pur_no = b.pur_no)*100)/
NVL(B.QTY,0)) * (NVL(A.CARGO_CHARGES,0) / 100) + NVL(B.RATE,0)
END),3) * NVL(B.QTY,0) - NVL(B.QTY,0) * NVL(B.DISCOUNT,0)))
FROM INV.PURCHASE_C B
WHERE B.PUR_NO = A.PUR_NO) AS INVC_AMT_WITH_CHARGES,
(SELECT (SUM( ROUND((CASE WHEN NVL(B.QTY,0) = 0
THEN NULL
ELSE
((NVL(B.RATE,0) /
(select sum(nvl(rate,0)) from inv.purchase_c where pur_no = b.pur_no)*100)/
NVL(B.QTY,0)) * (NVL(A.CARGO_CHARGES,0) / 100) + NVL(B.RATE,0)
END),3) * NVL(B.QTY,0) - NVL(B.QTY,0) * NVL(B.DISCOUNT,0)))
FROM INV.PURCHASE_C B
WHERE B.PUR_NO = A.PUR_NO) - NVL(A.DISCOUNT,0) + NVL(A.OTHER_CHARGES,0) AS INVC_NET_AMOUNT,
NVL(A.PAID_AMOUNT,0) + NVL(( SELECT SUM(NVL(AMOUNT,0)) FROM INV.PAYMENT_VOUCHER WHERE INVOICE_NO = A.PUR_NO ),0) AS CASH_PAID
FROM INV.PURCHASE_M A, INV.COMPANY_DIVISION B, INV.SUPPLIER D, INV.STORES E
WHERE (A.DIVISION_ID = B.DIVISION_ID(+))AND (A.SUPPLIER_ID = D.SUPPLIER_ID(+))
AND (A.STORE_CODE = E.STORE_CODE(+))