Guest User

Untitled

a guest
Apr 24th, 2019
89
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