CREATE OR REPLACE FORCE VIEW TOPUP.VIEW_FINANCE_SALESTAT_AGENT
(
   ID,
   STATDATE,
   AGENTID,
   PARMONEY,
   ACTUALMONEY,
   INMONEY,
   OUTMONEY,
   ADDMONEY,
   INDISCOUNT,
   OUTDISCOUNT,
   AGENTNAME,
   CREATEDATE,
   LASTDATE,
   PROFITRATE,
   GROSSPROFIT,
   CORRECTPAR,
   CORRECTMONEY,
   BATCH,
   BALANCE
)
AS
     SELECT   u.id,
              u.statdate,
              u.GROUP_ID,
              u.parmoney,
              actualmoney,
              u.inmoney,
              u.outmoney,
              NVL (u.addmoney, 0),
              CASE
                 WHEN actualmoney = 0 THEN 0
                 ELSE u.inmoney / u.actualmoney
              END
                 AS indiscount,
              CASE
                 WHEN actualmoney = 0 THEN 0
                 ELSE u.outmoney / u.actualmoney
              END
                 AS outdiscount,
              g.logogram,
              u.createdate,
              u.lastdate,
              CASE
                 WHEN u.actualmoney - u.inmoney = 0 THEN 0
                 ELSE (u.outmoney - u.inmoney) / (u.actualmoney - u.inmoney)
              END
                 AS profitrate,
              (u.outmoney - u.inmoney),
              NVL (refundmoney, 0),
              NVL (refundprice, 0),
              '',
              NVL (u.balance, 0)
       FROM         (SELECT   t.id id,
                              t.statdate,
                              t.GROUP_ID,
                              t.parmoney,
                              t.actualmoney,
                              t.inmoney,
                              t.outmoney,
                              t.createdate,
                              t.lastdate,
                              m.addmoney,
                              m.balance
                       FROM      (  SELECT   MIN (a.id) id,
                                             a.statdate,
                                             a.GROUP_ID,
                                             SUM (a.parmoney) parmoney,
                                             SUM (a.actualmoney) actualmoney,
                                             SUM (a.inmoney) inmoney,
                                             SUM (a.outmoney) outmoney,
                                             MIN (a.createdate) createdate,
                                             MIN (a.lastdate) lastdate
                                      FROM   tab_topup_stat_agent a
                                  GROUP BY   a.statdate, a.GROUP_ID) t
                              LEFT JOIN
                                 tab_topup_stat_money m
                              ON t.GROUP_ID = m.GROUP_ID
                                 AND t.statdate = m.statdate
                     UNION
                     SELECT   m.id,
                              m.statdate,
                              m.GROUP_ID,
                              0 parmoney,
                              0 actualmoney,
                              0 inmoney,
                              0 outmoney,
                              m.createdate,
                              m.lastdate,
                              m.addmoney,
                              m.balance
                       FROM      (  SELECT   MIN (a.id) id,
                                             a.statdate,
                                             a.GROUP_ID,
                                             SUM (a.parmoney) parmoney,
                                             SUM (a.actualmoney) actualmoney,
                                             SUM (a.inmoney) inmoney,
                                             SUM (a.outmoney) outmoney,
                                             MIN (a.createdate) createdate,
                                             MIN (a.lastdate) lastdate
                                      FROM   tab_topup_stat_agent a
                                  GROUP BY   a.statdate, a.GROUP_ID) t
                              RIGHT JOIN
                                 tab_topup_stat_money m
                              ON t.GROUP_ID = m.GROUP_ID
                                 AND t.statdate = m.statdate
                      WHERE   m.addmoney > 0 AND t.GROUP_ID IS NULL) u
                 LEFT JOIN
                    tab_base_group g
                 ON u.GROUP_ID = g.id
              LEFT JOIN
                 (  SELECT   SUM (aoi.refundmoney) refundmoney,
                             SUM (aoi.refundprice) refundprice,
                             aoi.statdate,
                             aoi.agent_id
                      FROM   tab_topup_auditorderitem aoi
                  GROUP BY   aoi.STATDATE, aoi.agent_id) t3
              ON g.id = t3.agent_id AND u.statdate = t3.statdate
   ORDER BY   u.id;