SELECT  SE.SYMBOL ITEM,  SE.SYMBOL, AFACCTNO, CUSTODYCD, TRADE, 0 TOPUP, 0 MARGIN, RECEIVING, nvl(OD.remainqttyNS,0) SECURED,
                NVL(SEC.BASICPRICE,0) BASICPRICE, NVL(NVL(se.COSTPRICE, SEP.COSTPRICE),0) COSTPRICE, 0 RETAIL,
                ROUND(
                    (TRADE + DFTRADING + ABSTANDING + RESTRICTQTTY + BLOCKED + REMAINQTTY + WITHDRAW + DEPOSIT + SENDDEPOSIT + RECEIVING)
                    * NVL(NVL(se.COSTPRICE, SEP.COSTPRICE),0),0) COSTPRICEAMT, -- GIA TRI VON
                (TRADE + DFTRADING + ABSTANDING + RESTRICTQTTY + BLOCKED + REMAINQTTY + WITHDRAW + DEPOSIT + SENDDEPOSIT + RECEIVING)
                    * SEP.MARKETPRICE MARKETAMT, -- GIA TRI THI TRUONG
                ((TRADE + DFTRADING + ABSTANDING + RESTRICTQTTY + BLOCKED + REMAINQTTY + WITHDRAW + DEPOSIT + SENDDEPOSIT + RECEIVING)
                    * SEP.MARKETPRICE - ROUND(
                                            (TRADE + DFTRADING + ABSTANDING + RESTRICTQTTY + BLOCKED + REMAINQTTY + WITHDRAW + DEPOSIT + SENDDEPOSIT + RECEIVING)
                                             * NVL(NVL(se.COSTPRICE, SEP.COSTPRICE),0),0)) PL, -- LOI LO TAM TINH
                ROUND(CASE WHEN (TRADE + DFTRADING+ ABSTANDING + RESTRICTQTTY + BLOCKED + REMAINQTTY + WITHDRAW + DEPOSIT + SENDDEPOSIT + RECEIVING)
                                * NVL(NVL(se.COSTPRICE, SEP.COSTPRICE),0) = 0 THEN 0
                    ELSE ((TRADE + DFTRADING + ABSTANDING + RESTRICTQTTY + BLOCKED + REMAINQTTY + WITHDRAW + DEPOSIT + SENDDEPOSIT + RECEIVING)
                             * SEP.MARKETPRICE
                             - (TRADE + DFTRADING + ABSTANDING + RESTRICTQTTY + BLOCKED + REMAINQTTY + WITHDRAW + DEPOSIT + SENDDEPOSIT + RECEIVING)
                             * NVL(NVL(se.COSTPRICE, SEP.COSTPRICE),0)) / ((TRADE + DFTRADING + ABSTANDING + RESTRICTQTTY + BLOCKED + REMAINQTTY + WITHDRAW + DEPOSIT + SENDDEPOSIT + RECEIVING)
                                                     * NVL(NVL(se.COSTPRICE, SEP.COSTPRICE),0)) END,4) * 100 PCPL, -- % LAI LO TAM TINH
                RECEIVING - SECURITIES_RECEIVING_T0 - SECURITIES_RECEIVING_T1 - SECURITIES_RECEIVING_T2 - SECURITIES_RECEIVING_T3 RECEIVING_RIGHT, -- CK Quyen Ve
                SECURITIES_RECEIVING_T0 RECEIVING_T0,
                SECURITIES_RECEIVING_T1 RECEIVING_T1,
                SECURITIES_RECEIVING_T2 RECEIVING_T2,
                SECURITIES_RECEIVING_T3 RECEIVING_T3,
                SE.TRADE + SE.DFTRADING + SE.ABSTANDING +
                SE.RESTRICTQTTY + SE.BLOCKED +
                SE.REMAINQTTY + SE.WITHDRAW +
                SE.DEPOSIT + SE.SENDDEPOSIT +
                SE.RECEIVING TOTAL_QTTY, -- Tong CK
                a1.cdcontent MARKETCODE, a1.en_cdcontent MARKETCODE_EN -- ten san

        FROM BUF_SE_ACCOUNT SE, sbsecurities sb, securities_info sec,
            (SELECT seacctno, SUM(CASE WHEN o.exectype IN('NS','MS') THEN o.remainqtty ELSE 0 END) remainqttyNS
            FROM odmast o
            WHERE deltd <>'Y' AND o.exectype IN('NS','NB','MS')
            AND o.txdate =(SELECT to_date(VARVALUE,'DD/MM/YYYY') FROM sysvar WHERE grname='SYSTEM' AND varname='CURRDATE')
            GROUP BY seacctno) OD,
            (
            SELECT se.acctno seacctno, setr.costprice,
                sei.basicprice marketprice
                FROM afmast af, aftype aft, semast se, sbsecurities sb, securities_info sei,
                (SELECT    SE.AFACCTNO, SE.CODEID, SE.CUSTODYCD,
                    CASE WHEN MAX(SE.PREVQTTY)+SUM(NVL(SEC.INQTTY,0)-NVL(SEC.OUTQTTY,0)-NVL(SEC.ODOUTQTTY,0)) = 0 THEN 0 ELSE
                            ROUND(
                          (MAX(SE.PREVQTTY*SE.COSTPRICE)
                           +SUM((NVL(SEC.INAMT,0))
                                - (NVL(SEC.ODOUTAMT,0))
                                -(NVL(SEC.OUTAMT,0))
                              /*+(CASE WHEN OD.EXECTYPE LIKE '%S' THEN 0 ELSE NVL(OD.FEEACR,0)END)*/)
                           )/(MAX(SE.PREVQTTY)+SUM(NVL(SEC.INQTTY,0)-NVL(SEC.OUTQTTY,0)-NVL(SEC.ODOUTQTTY,0))),0
                           )
                      END costprice
                      FROM (SELECT ACCTNO, CODEID, ORDERID,
                                   SUM(CASE WHEN PTYPE ='I' THEN QTTY ELSE 0 END) INQTTY,
                                   SUM(CASE WHEN PTYPE ='O' AND ORDERID IS NOT NULL THEN QTTY ELSE 0 END) ODOUTQTTY,
                                   SUM(CASE WHEN PTYPE ='O' AND ORDERID IS NULL THEN QTTY ELSE 0 END) OUTQTTY,
                                   SUM(CASE WHEN PTYPE ='I' THEN QTTY*COSTPRICE ELSE 0 END) INAMT,
                                   SUM(CASE WHEN PTYPE ='O' AND ORDERID IS NOT NULL THEN QTTY*RTCOSTPRICE ELSE 0 END) ODOUTAMT,
                                   SUM(CASE WHEN PTYPE ='O' AND ORDERID IS NULL THEN QTTY*COSTPRICE ELSE 0 END) OUTAMT
                            FROM SECMAST
                            WHERE TXDATE = getcurrdate
                                  AND DELTD <> 'Y'
                            GROUP BY ACCTNO, CODEID, ORDERID
                            )SEC, /* ODMAST OD, ODTYPE OT,*/ VW_SEMAST_CUSTODYCD SE
                      WHERE /*SEC.ORDERID = OD.ORDERID(+)
                          AND OD.ACTYPE = OT.ACTYPE(+)
                          AND*/ SE.AFACCTNO = SEC.ACCTNO(+)
                          AND SE.CODEID = SEC.CODEID(+)
                      GROUP BY SE.AFACCTNO, SE.CODEID, SE.CUSTODYCD) setr
                WHERE af.actype = aft.actype AND af.acctno = se.afacctno
                AND se.codeid = sb.codeid AND se.codeid = sei.codeid
                AND se.CODEID = setr.CODEID(+)
                AND se.afacctno = setr.afacctno(+)
                AND SB.SECTYPE NOT IN ('004','009')
               ) sep, allcode a1
        WHERE  se.codeid = sb.codeid
            AND se.codeid = sec.codeid(+)
            AND se.acctno = sep.seacctno(+)
            AND se.acctno = od.seacctno(+)
            AND sb.tradeplace = a1.cdval
            AND a1.cdname = 'TRADEPLACE' AND a1.CDTYPE = 'OD' AND a1.cduser = 'Y'
          AND CUSTODYCD = '085C000066'
          AND AFACCTNO LIKE '0001000077'
          AND TRADE + DFTRADING + ABSTANDING + RESTRICTQTTY + BLOCKED + REMAINQTTY + WITHDRAW + DEPOSIT + SENDDEPOSIT + RECEIVING <> 0
        ORDER BY CUSTODYCD, AFACCTNO, SYMBOL