SHARE
TWEET

Untitled

a guest Apr 24th, 2019 69 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT  SE.SYMBOL ITEM,  SE.SYMBOL, AFACCTNO, CUSTODYCD, TRADE, 0 TOPUP, 0 MARGIN, RECEIVING, nvl(OD.remainqttyNS,0) SECURED,
  2.                 NVL(SEC.BASICPRICE,0) BASICPRICE, NVL(NVL(se.COSTPRICE, SEP.COSTPRICE),0) COSTPRICE, 0 RETAIL,
  3.                 ROUND(
  4.                     (TRADE + DFTRADING + ABSTANDING + RESTRICTQTTY + BLOCKED + REMAINQTTY + WITHDRAW + DEPOSIT + SENDDEPOSIT + RECEIVING)
  5.                     * NVL(NVL(se.COSTPRICE, SEP.COSTPRICE),0),0) COSTPRICEAMT, -- GIA TRI VON
  6.                 (TRADE + DFTRADING + ABSTANDING + RESTRICTQTTY + BLOCKED + REMAINQTTY + WITHDRAW + DEPOSIT + SENDDEPOSIT + RECEIVING)
  7.                     * SEP.MARKETPRICE MARKETAMT, -- GIA TRI THI TRUONG
  8.                 ((TRADE + DFTRADING + ABSTANDING + RESTRICTQTTY + BLOCKED + REMAINQTTY + WITHDRAW + DEPOSIT + SENDDEPOSIT + RECEIVING)
  9.                     * SEP.MARKETPRICE - ROUND(
  10.                                             (TRADE + DFTRADING + ABSTANDING + RESTRICTQTTY + BLOCKED + REMAINQTTY + WITHDRAW + DEPOSIT + SENDDEPOSIT + RECEIVING)
  11.                                              * NVL(NVL(se.COSTPRICE, SEP.COSTPRICE),0),0)) PL, -- LOI LO TAM TINH
  12.                 ROUND(CASE WHEN (TRADE + DFTRADING+ ABSTANDING + RESTRICTQTTY + BLOCKED + REMAINQTTY + WITHDRAW + DEPOSIT + SENDDEPOSIT + RECEIVING)
  13.                                 * NVL(NVL(se.COSTPRICE, SEP.COSTPRICE),0) = 0 THEN 0
  14.                     ELSE ((TRADE + DFTRADING + ABSTANDING + RESTRICTQTTY + BLOCKED + REMAINQTTY + WITHDRAW + DEPOSIT + SENDDEPOSIT + RECEIVING)
  15.                              * SEP.MARKETPRICE
  16.                              - (TRADE + DFTRADING + ABSTANDING + RESTRICTQTTY + BLOCKED + REMAINQTTY + WITHDRAW + DEPOSIT + SENDDEPOSIT + RECEIVING)
  17.                              * NVL(NVL(se.COSTPRICE, SEP.COSTPRICE),0)) / ((TRADE + DFTRADING + ABSTANDING + RESTRICTQTTY + BLOCKED + REMAINQTTY + WITHDRAW + DEPOSIT + SENDDEPOSIT + RECEIVING)
  18.                                                      * NVL(NVL(se.COSTPRICE, SEP.COSTPRICE),0)) END,4) * 100 PCPL, -- % LAI LO TAM TINH
  19.                 RECEIVING - SECURITIES_RECEIVING_T0 - SECURITIES_RECEIVING_T1 - SECURITIES_RECEIVING_T2 - SECURITIES_RECEIVING_T3 RECEIVING_RIGHT, -- CK Quyen Ve
  20.                 SECURITIES_RECEIVING_T0 RECEIVING_T0,
  21.                 SECURITIES_RECEIVING_T1 RECEIVING_T1,
  22.                 SECURITIES_RECEIVING_T2 RECEIVING_T2,
  23.                 SECURITIES_RECEIVING_T3 RECEIVING_T3,
  24.                 SE.TRADE + SE.DFTRADING + SE.ABSTANDING +
  25.                 SE.RESTRICTQTTY + SE.BLOCKED +
  26.                 SE.REMAINQTTY + SE.WITHDRAW +
  27.                 SE.DEPOSIT + SE.SENDDEPOSIT +
  28.                 SE.RECEIVING TOTAL_QTTY, -- Tong CK
  29.                 a1.cdcontent MARKETCODE, a1.en_cdcontent MARKETCODE_EN -- ten san
  30.  
  31.         FROM BUF_SE_ACCOUNT SE, sbsecurities sb, securities_info sec,
  32.             (SELECT seacctno, SUM(CASE WHEN o.exectype IN('NS','MS') THEN o.remainqtty ELSE 0 END) remainqttyNS
  33.             FROM odmast o
  34.             WHERE deltd <>'Y' AND o.exectype IN('NS','NB','MS')
  35.             AND o.txdate =(SELECT to_date(VARVALUE,'DD/MM/YYYY') FROM sysvar WHERE grname='SYSTEM' AND varname='CURRDATE')
  36.             GROUP BY seacctno) OD,
  37.             (
  38.             SELECT se.acctno seacctno, setr.costprice,
  39.                 sei.basicprice marketprice
  40.                 FROM afmast af, aftype aft, semast se, sbsecurities sb, securities_info sei,
  41.                 (SELECT    SE.AFACCTNO, SE.CODEID, SE.CUSTODYCD,
  42.                     CASE WHEN MAX(SE.PREVQTTY)+SUM(NVL(SEC.INQTTY,0)-NVL(SEC.OUTQTTY,0)-NVL(SEC.ODOUTQTTY,0)) = 0 THEN 0 ELSE
  43.                             ROUND(
  44.                           (MAX(SE.PREVQTTY*SE.COSTPRICE)
  45.                            +SUM((NVL(SEC.INAMT,0))
  46.                                 - (NVL(SEC.ODOUTAMT,0))
  47.                                 -(NVL(SEC.OUTAMT,0))
  48.                               /*+(CASE WHEN OD.EXECTYPE LIKE '%S' THEN 0 ELSE NVL(OD.FEEACR,0)END)*/)
  49.                            )/(MAX(SE.PREVQTTY)+SUM(NVL(SEC.INQTTY,0)-NVL(SEC.OUTQTTY,0)-NVL(SEC.ODOUTQTTY,0))),0
  50.                            )
  51.                       END costprice
  52.                       FROM (SELECT ACCTNO, CODEID, ORDERID,
  53.                                    SUM(CASE WHEN PTYPE ='I' THEN QTTY ELSE 0 END) INQTTY,
  54.                                    SUM(CASE WHEN PTYPE ='O' AND ORDERID IS NOT NULL THEN QTTY ELSE 0 END) ODOUTQTTY,
  55.                                    SUM(CASE WHEN PTYPE ='O' AND ORDERID IS NULL THEN QTTY ELSE 0 END) OUTQTTY,
  56.                                    SUM(CASE WHEN PTYPE ='I' THEN QTTY*COSTPRICE ELSE 0 END) INAMT,
  57.                                    SUM(CASE WHEN PTYPE ='O' AND ORDERID IS NOT NULL THEN QTTY*RTCOSTPRICE ELSE 0 END) ODOUTAMT,
  58.                                    SUM(CASE WHEN PTYPE ='O' AND ORDERID IS NULL THEN QTTY*COSTPRICE ELSE 0 END) OUTAMT
  59.                             FROM SECMAST
  60.                             WHERE TXDATE = getcurrdate
  61.                                   AND DELTD <> 'Y'
  62.                             GROUP BY ACCTNO, CODEID, ORDERID
  63.                             )SEC, /* ODMAST OD, ODTYPE OT,*/ VW_SEMAST_CUSTODYCD SE
  64.                       WHERE /*SEC.ORDERID = OD.ORDERID(+)
  65.                           AND OD.ACTYPE = OT.ACTYPE(+)
  66.                           AND*/ SE.AFACCTNO = SEC.ACCTNO(+)
  67.                           AND SE.CODEID = SEC.CODEID(+)
  68.                       GROUP BY SE.AFACCTNO, SE.CODEID, SE.CUSTODYCD) setr
  69.                 WHERE af.actype = aft.actype AND af.acctno = se.afacctno
  70.                 AND se.codeid = sb.codeid AND se.codeid = sei.codeid
  71.                 AND se.CODEID = setr.CODEID(+)
  72.                 AND se.afacctno = setr.afacctno(+)
  73.                 AND SB.SECTYPE NOT IN ('004','009')
  74.                ) sep, allcode a1
  75.         WHERE  se.codeid = sb.codeid
  76.             AND se.codeid = sec.codeid(+)
  77.             AND se.acctno = sep.seacctno(+)
  78.             AND se.acctno = od.seacctno(+)
  79.             AND sb.tradeplace = a1.cdval
  80.             AND a1.cdname = 'TRADEPLACE' AND a1.CDTYPE = 'OD' AND a1.cduser = 'Y'
  81.           AND CUSTODYCD = '085C000066'
  82.           AND AFACCTNO LIKE '0001000077'
  83.           AND TRADE + DFTRADING + ABSTANDING + RESTRICTQTTY + BLOCKED + REMAINQTTY + WITHDRAW + DEPOSIT + SENDDEPOSIT + RECEIVING <> 0
  84.         ORDER BY CUSTODYCD, AFACCTNO, SYMBOL
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top