Help analyze Oracle AWR report

We are troubleshooting a performance issue on a 3 tier web application, which is on Websphere application server V7 and Oracle R11. The performance (system slowness and eventually cannot login) is reported in month end, usually around 10 - 11AM.
From the SQL ordered by Elapsed Time in AWR reports, we do see SQLs with significant long execution time. BTW, the database server is a 8 core AIX server.
We are asking both application developers and DBA to check why these SQL took such long time and anything from application and infrastructure perspective can be optimized. But the response is slow.
The complete AWR reports are attached.  Welcome any deeper analysis and comments on potential causes and further items to check.  


0731awr.PNGawr-20150731-1100.html
awr-20150731-1030.html
MatthewLiuAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Are you running Exadata?
Did you recently upgrade?

If so check out:
Reduced 'cell physical IO bytes saved by storage index' after storage cell upgrade to versions before 11.2.2.3.0 (Doc ID 1321398.1)


You do look I/O bound.  Look at indexing on the specific queries.  I also noticed some things like an ORDER BY on a "select count(*)".  No real need to do a sort to just count stuff.

Whenever a see a multi-page select statement, I really question it...
0
MatthewLiuAuthor Commented:
no, we are not using Exadata. we installed Oracle on an IBM P750 box.
yes, removing order by in the select count(*) makes sense and should improve the performance.
as the very long SQL, I believe it was generated by the foundation of the application (the foundation was created by the core development team and feature developers use the foundation to implement required features), it could be a little challenging for them to optimize, but it should be I think.

Is there any other finding, comment?
0
PortletPaulfreelancerCommented:
73 UNION ALLs
Is there no better way than this?
just an example, there are others very similar to it which suggests it is being used often
SELECT z.category1
 ,z.category2
 ,z.num
 ,z.itemid
 ,z.sign
 ,z.itemname
 ,z.orgcode
 ,z.amount
FROM (
 SELECT 1 AS category1
  ,'1.1.1' AS category2
  ,1 AS num
  ,'1' AS itemid
  ,'+' AS sign
  ,'销售营业金额(税前,不含提货劵销售和残值)' AS itemname
  ,ds1.orgcode
  ,trim(to_char(round(nvl(sum(ds1.amount), 0), 2), '99999999999990.99')) AS amount
 FROM (
  SELECT 1 AS category1
   ,'1.1.1' AS category2
   ,1 AS num
   ,'1' AS itemid
   ,'+' AS sign
   ,'销售营业金额(税前,不含提货劵销售和残值)' AS itemname
   ,o.orgcode
   ,sum(a.totalamount) AS amount
  FROM organization o
  LEFT JOIN ord a
  INNER JOIN ordertype b ON a.ordertypeid = b.ordertypeid AND b.ordertypecode NOT IN ('202', '303') ON a.sellerid = o.orgid AND a.STATUS IN (70, 80) AND a.ownerorgid = a.sellerid AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1 WHERE o.orgid IN (100000351)
  GROUP BY o.orgcode
  
  UNION ALL
  
  SELECT 1 AS category1
   ,'1.1.1' AS category2
   ,1 AS num
   ,'1' AS itemid
   ,'+' AS sign
   ,'销售营业金额(税前,不含提货劵销售和残值)' AS itemname
   ,o.orgcode
   ,- 1 * nvl(sum(ordd.amount), 0) AS amount
  FROM organization o
  LEFT JOIN ord a
  INNER JOIN orderdetail ordd ON a.orderid = ordd.orderid
  INNER JOIN item it ON ordd.selleritemid = it.itemid
  INNER JOIN category cat ON it.categoryid = cat.categoryid AND cat.categorycode = '00601'
  INNER JOIN ordertype b ON a.ordertypeid = b.ordertypeid AND b.ordertypecode NOT IN ('202', '303') ON a.sellerid = o.orgid AND a.STATUS IN (70, 80) AND a.ownerorgid = a.sellerid AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1 WHERE o.orgid IN (100000351)
  GROUP BY o.orgcode
  ) ds1
 GROUP BY ds1.orgcode
 
 UNION ALL
 
 SELECT 1 AS category1
  ,'1.1.1' AS category2
  ,2 AS num
  ,'1' AS itemid
  ,'+' AS sign
  ,'批发:' AS itemname
  ,o.orgcode
  ,trim(to_char(round(nvl(sum(a.totalamount), 0), 2), '99999999999990.99')) AS amount
 FROM organization o
 LEFT JOIN ord a
 INNER JOIN ordertype b ON a.ordertypeid = b.
 ORDER typeid AND b.ordertypecode <> '303' AND (b.isservice IS NULL OR b.isservice <> '1') ON a.sellerid = o.orgid AND a.ownerorgid = a.sellerid AND a.STATUS IN (70, 80) AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1
 WHERE o.orgid IN (100000351)
 GROUP BY o.orgcode
 
 UNION ALL
 
 SELECT 1 AS category1
  ,'1.1.2' AS category2
  ,3 AS num
  ,'1' AS itemid
  ,'+' AS sign
  ,'零售:' AS itemname
  ,ds2.orgcode
  ,trim(to_char(round(nvl(sum(ds2.amount), 0), 2), '99999999999990.99')) AS amount
 FROM (
  SELECT 1 AS category1
   ,'1.1.2' AS category2
   ,3 AS num
   ,'1' AS itemid
   ,'+' AS sign
   ,'零售:' AS itemname
   ,o.orgcode
   ,sum(a.totalamount) AS amount
  FROM organization o
  LEFT JOIN ord a
  INNER JOIN ordertype b ON a.ordertypeid = b.ordertypeid AND b.ordertypecode <> '202' AND b.isservice = '1' ON a.sellerid = o.orgid AND a.ownerorgid = a.sellerid AND a.STATUS IN (70, 80) AND a.gldate >= to_date('20 15-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1 WHERE o.orgid IN (100000351)
  GROUP BY o.orgcode
  
  UNION ALL
  
  SELECT 1 AS category1
   ,'1.1.2' AS category2
   ,3 AS num
   ,'1' AS itemid
   ,'+' AS sign
   ,'零售:' AS itemname
   ,o.orgcode
   ,- 1 * nvl(sum(ordd.amount), 0) AS amount
  FROM organization o
  LEFT JOIN ord a
  INNER JOIN orderdetail ordd ON a.orderid = ordd.orderid
  INNER JOIN item it ON ordd.selleritemid = it.itemid
  INNER JOIN category cat ON it.categoryid = cat.categoryid AND cat.categorycode = '00601'
  INNER JOIN ordertype b ON a.ordertypeid = b.ordertypeid AND b.ordertypecode <> '202' AND b.isservice = '1' ON a.sellerid = o.orgid AND a.ownerorgid = a.sellerid AND a.STATUS IN (70, 80) AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1 WHERE o.orgid IN (100000351)
  GROUP BY o.orgcode
  ) ds2
 GROUP BY ds2.orgcode
 
 UNION ALL
 
 SELECT 1 AS category1
  ,'1.1.3' AS category2
  ,4 AS num
  ,'1' AS itemid
  ,'+' AS sign
  ,'税后:' AS itemname
  ,orgcode
  ,trim(to_char(round(nvl(sum(round(amount, 2)), 0), 2), '99999999999990.99')) AS amount
 FROM (
  SELECT 1 AS category1
   ,'1.1.3' AS category2
   ,4 AS num
   ,'1' AS itemid
   ,'+' AS sign
   ,'税后:' AS itemname
   ,o.orgcode
   ,sum((c.netprice / (1 + c.taxrate) * c.orderqty)) AS amount
  FROM organization o
  LEFT JOIN ord a
  INNER JOIN ordertype b ON a.ordertypeid = b.ordertypeid AND b.ordertypecode NOT IN ('202', '303')
  INNER JOIN orderdetail c ON a.orderid = c.orderid ON o.orgid = a.sellerid AND a.ownerorgid = a.sellerid AND a.STATUS IN (70, 80) AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1 WHERE o.orgid IN (100000351)
  GROUP BY o.orgcode
  
  UNION ALL
  
  SELECT 1 AS category1
   ,'1.1.3' AS category2
   ,4 AS num
   ,'1' AS itemid
   ,'+' AS sign
   ,'税后:' AS itemname
   ,o.orgcode
   ,- 1 * sum((ordd.unitprice / (1 + ordd.taxrate) * OR dd.orderqty)) AS amount
  FROM organization o
  LEFT JOIN ord a ON o.orgid = a.sellerid
  INNER JOIN orderdetail ordd ON a.orderid = ordd.orderid
  INNER JOIN item it ON ordd.selleritemid = it.itemid
  INNER JOIN category cat ON it.categoryid = cat.categoryid AND cat.categorycode = '00601'
  INNER JOIN ordertype b ON a.ordertypeid = b.ordertypeid AND b.ordertypecode NOT IN ('202', '303') AND a.ownerorgid = a.sellerid AND a.STATUS IN (70, 80) AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1
  WHERE o.orgid IN (100000351)
  GROUP BY o.orgcode
  ) dp4
 GROUP BY orgcode
 
 UNION ALL
 
 SELECT 1 AS category1
  ,'1.2' AS category2
  ,5 AS num
  ,'1' AS itemid
  ,' ' AS sign
  ,'期间内新收到预收款(税前):' AS itemname
  ,ds3.orgcode
  ,trim(to_char(round(nvl(sum(ds3.amount), 0), 2), '99999999999990.99')) AS amount
 FROM (
  SELECT 1 AS category1
   ,'1.2' AS category2
   ,5 AS num
   ,'1' AS itemid
   ,' ' AS sign
   ,'期间内新收到预收款(税前):' AS itemname
   ,o.orgcode
   ,nvl(su m(a.totalamount), 0) AS amount
  FROM organization o
  LEFT JOIN payment a
  INNER JOIN paymentdetail a2 ON a.paymentid = a2.paymentid AND a2.settlemethod = 3 ON o.orgid = a.orgid AND a.paymenttype = 1 AND a.STATUS = 80 AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1 WHERE o.orgid IN (100000351)
  GROUP BY o.orgcode
  
  UNION ALL
  
  SELECT 1 AS category1
   ,'1.2' AS category2
   ,5 AS num
   ,'1' AS itemid
   ,' ' AS sign
   ,'期间内新收到预收款(税前):' AS itemname
   ,o.orgcode
   ,- 1 * nvl(sum(vs.SERVICEUNUSERDNUM * vs.ZMCLPRICE), 0) AS amount
  FROM organization o
  LEFT JOIN zmclvip a ON o.orgid = a.orgid AND a.cardstatus = 5
  INNER JOIN zmclvipservice vs ON vs.vipid = a.VIPID
  INNER JOIN payment pay ON pay.orgid = a.orgid AND pay.partnerid = a.zmclbuyerid AND pay.paymenttype = 1 AND pay.zmclismclvip = 'Y' AND pay.comments = '米其林会员卡转预收' AND EXISTS (
    SELECT 1
    FROM orderdetail
    WHERE orderid = pay.relatedtranid AND zmclvipid = a.vipid
    ) AND a.CARDLASTDATE >= (to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') - 1) AND a.CARDLASTDATE < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
  WHERE a.orgid IN (100000351)
  GROUP BY o.orgcode
  ) ds3
 GROUP BY ds3.orgcode
 
 UNION ALL
 
 SELECT 1 AS category1
  ,'1.2.1' AS category2
  ,6 AS num
  ,'1' AS itemid
  ,' ' AS sign
  ,'现金:' AS itemname
  ,ds3.orgcode
  ,trim(to_char(round(nvl(sum(ds3.amount), 0), 2), '99999999999990.99')) AS amount
 FROM (
  SELECT 1 AS category1
   ,'1.2.1' AS category2
   ,6 AS num
   ,'1' AS itemid
   ,' ' AS sign
   ,'现金:' AS itemname
   ,o.orgcode
   ,nvl(sum(a.totalamount), 0) AS amount
  FROM organization o
  LEFT JOIN payment a
  INNER JOIN paymentdetail a2 ON a.paymentid = a2.paymentid AND a2.settlemethod = 3 ON o.orgid = a.orgid AND a.paymenttype = 1 AND a.STATUS = 80 AND a.paymentmethod = 3 AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1 WHERE o.orgid IN (100000351)
  GROUP BY o.orgcode uni ON ALL
  SELECT 1 AS category1
   ,'1.2.1' AS category2
   ,6 AS num
   ,'1' AS itemid
   ,' ' AS sign
   ,'现金:' AS itemname
   ,o.orgcode
   ,- 1 * nvl(sum(vs.SERVICEUNUSERDNUM * vs.ZMCLPRICE), 0) AS amount
  FROM organization o
  LEFT JOIN zmclvip a ON o.orgid = a.orgid AND a.cardstatus = 5
  INNER JOIN zmclvipservice vs ON vs.vipid = a.VIPID
  INNER JOIN payment pay ON pay.orgid = a.orgid AND pay.partnerid = a.zmclbuyerid AND pay.paymenttype = 1 AND pay.zmclismclvip = 'Y' AND pay.comments = '米其林会员卡转预收' AND EXISTS (
    SELECT 1
    FROM orderdetail
    WHERE orderid = pay.relatedtranid AND zmclvipid = a.vipid
    ) AND pay.paymentmethod = 3 AND a.CARDLASTDATE >= (to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') - 1) AND a.CARDLASTDATE < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
  WHERE a.orgid IN (100000351)
  GROUP BY o.orgcode
  ) ds3
 GROUP BY ds3.orgcode
 
 UNION ALL
 
 SELECT 1 AS category1
  ,'1.2.2' AS category2
  ,7 AS num
  ,'1' AS itemid
  ,' ' AS sign
  ,'刷卡:' AS itemname
  ,ds3.orgcode
  ,trim(to_char(round(nvl(sum(ds3.amount), 0), 2), '99999999999990.99')) AS amount
 FROM (
  SELECT 1 AS category1
   ,'1.2.2' AS category2
   ,7 AS num
   ,'1' AS itemid
   ,' ' AS sign
   ,'刷卡:' AS itemname
   ,o.orgcode
   ,nvl(sum(a.totalamount), 0) AS amount
  FROM organization o
  LEFT JOIN payment a
  INNER JOIN paymentdetail a2 ON a.paymentid = a2.paymentid AND a2.settlemethod = 3 ON o.orgid = a.orgid AND a.paymenttype = 1 AND a.STATUS = 80 AND a.paymentmethod IN (4, 5) AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1 WHERE o.orgid IN (100000351)
  GROUP BY o.orgcode
  
  UNION ALL
  
  SELECT 1 AS category1
   ,'1.2.2' AS category2
   ,7 AS num
   ,'1' AS itemid
   ,' ' AS sign
   ,'刷卡:' AS itemname
   ,o.orgcode
   ,- 1 * nvl(sum(vs.SERVICEUNUSERDNUM * vs.ZMCLPRICE), 0) AS amount
  FROM organization o
  LEFT JOIN zmclvip a ON o.orgid = a.orgid AND a.cardstatus = 5
  INNER JOIN zmclvipservice vs ON vs.vipid = a.VIPID
  INNER JOIN payment pay ON pay.orgid = a.orgid AND pay.partnerid = a.zmclbuyerid AND pay.paymenttype = 1 AND pay.zmclismclvip = 'Y' AND pay.comments = '米其林会员卡转预收' AND EXISTS (
    SELECT 1
    FROM orderdetail
    WHERE orderid = pay.relatedtranid AND zmclvipid = a.vipid
    ) AND pay.paymentmethod IN (4, 5) AND a.CARDLASTDATE >= (to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') - 1) AND a.CARDLASTDATE < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
  WHERE a.orgid IN (100000351)
  GROUP BY o.orgcode
  ) ds3
 GROUP BY ds3.orgcode
 
 UNION ALL
 
 SELECT 1 AS category1
  ,'1.2.3' AS category2
  ,8 AS num
  ,'1' AS itemid
  ,' ' AS sign
  ,'其它(如:票据等):' AS itemname
  ,ds3.orgcode
  ,trim(to_char(round(nvl(sum(ds3.amount), 0), 2), '99999999999990.99')) AS amount
 FROM (
  SELECT 1 AS category1
   ,'1.2.3' AS category2
   ,8 AS num
   ,'1' AS itemid
   ,' ' AS sign
   ,'其它(如:票据等):' AS itemname
   ,o.orgcode
   ,nvl(sum(a.totalamount), 0) AS amount
  FROM organization o
  LEFT JOIN payment a
  INNER JOIN paymentdetail a2 ON a.paymentid = a2.paymentid AND a2.settlemethod = 3 ON o.orgid = a.orgid AND a.paymenttype = 1 AND a.sta tus = 80 AND a.paymentmethod IN (6, 7, 8) AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1 WHERE o.orgid IN (100000351)
  GROUP BY o.orgcode
  
  UNION ALL
  
  SELECT 1 AS category1
   ,'1.2.3' AS category2
   ,8 AS num
   ,'1' AS itemid
   ,' ' AS sign
   ,'其它(如:票据等):' AS itemname
   ,o.orgcode
   ,- 1 * nvl(sum(vs.SERVICEUNUSERDNUM * vs.ZMCLPRICE), 0) AS amount
  FROM organization o
  LEFT JOIN zmclvip a ON o.orgid = a.orgid AND a.cardstatus = 5
  INNER JOIN zmclvipservice vs ON vs.vipid = a.VIPID
  INNER JOIN payment pay ON pay.orgid = a.orgid AND pay.partnerid = a.zmclbuyerid AND pay.paymenttype = 1 AND pay.zmclismclvip = 'Y' AND pay.comments = '米其林会员卡转预收' AND EXISTS (
    SELECT 1
    FROM orderdetail
    WHERE orderid = pay.relatedtranid AND zmclvipid = a.vipid
    ) AND pay.paymentmethod IN (6, 7, 8) AND a.CARDLASTDATE >= (to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') - 1) AND a.CARDLASTDATE < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi :ss')
  WHERE a.orgid IN (100000351)
  GROUP BY o.orgcode
  ) ds3
 GROUP BY ds3.orgcode
 
 UNION ALL
 
 SELECT 1 AS category1
  ,'1.3' AS category2
  ,9 AS num
  ,'1' AS itemid
  ,'+' AS sign
  ,'实收金额(税前):' AS itemname
  ,o.orgcode
  ,trim(to_char(round(nvl(sum(b.realamtsettled), 0), 2), '99999999999990.99')) AS amount
 FROM organization o
 LEFT JOIN payment a
 INNER JOIN paymentdetail b
 INNER JOIN arap c
 INNER JOIN invoice d ON c.relatedtranid = d.invoiceid AND d.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') ON b.arapid = c.arapid AND c.relatedtrantype = 11 AND c.araptype = 1 ON a.paymentid = b.paymentid ON o.orgid = a.orgid AND a.STATUS = 80 AND a.paymenttype = 1 AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1 WHERE o.orgid IN (100000351) GROUP BY o.orgcode
 
 UNION ALL
 
 SELECT 1 AS category1
  ,'1.4' AS category2
  ,10 AS num
  ,'1' AS itemid
  ,'+' AS sign
  ,'其他营业外收入(税前):' AS itemname
  ,o.orgcode
  ,tri m(to_char(round(nvl(sum(a.zcttotalamount), 0), 2), '99999999999990.99')) AS amount
 FROM organization o
 LEFT JOIN arapadjustment a
 INNER JOIN arapadjustmentdetail b ON a.arapadjustmentid = b.arapadjustmentid AND b.arapadjusttype IN (5, 6) ON o.orgid = a.sourceorgid AND a.araptype = 1 AND a.STATUS = 80 AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1 WHERE o.orgid IN (100000351)
 GROUP BY o.orgcode
 
 UNION ALL
 
 SELECT 1 AS category1
  ,'1.5' AS category2
  ,11 AS num
  ,'1' AS itemid
  ,'-' AS sign
  ,'销售退货金额(税前):' AS itemname
  ,o.orgcode
  ,trim(to_char(round(nvl(0 - sum(x.arapsettleamt), 0), 2), '99999999999990.99')) AS amount
 FROM organization o
 LEFT JOIN (
  SELECT DISTINCT a.*
   ,b.orgid
  FROM paymentdetail a
   ,invoice b
   ,invoicedetail c
   ,billing d
   ,payment n
  WHERE a.paymentid = n.paymentid AND a.relatedtrancode = b.invoicecode AND b.invoiceid = c.invoiceid AND c.billingid = d.billingid AND d.billingtypeid = 3 AND b.orgid = n.orgid AND n.STATUS = 80 AND b.orgid IN (100000351) AND n.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND n.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1
  ) x ON o.orgid = x.orgid
 WHERE o.orgid IN (100000351)
 GROUP BY o.orgcode
 
 UNION ALL
 
 SELECT 1 AS category1
  ,'1.5.1' AS category2
  ,12 AS num
  ,'1' AS itemid
  ,'-' AS sign
  ,'未付:' AS itemname
  ,x.orgcode
  ,trim(to_char(round(0 - nvl(sum(x.balanceamount), 0), 2), '99999999999990.99')) AS amount
 FROM (
  SELECT DISTINCT d.*
   ,o.orgcode
  FROM organization o
  LEFT JOIN returnslip a
  INNER JOIN billing b
  INNER JOIN invoicedetail c
  INNER JOIN arap d ON c.invoiceid = d.relatedtranid AND d.relatedtrantype = 11 AND d.araptype = 1 ON c.billingid = b.billingid ON b.relatedtranid = a.returnid AND b.billingtypeid = 3 ON o.orgid = a.orgid AND a.returntype = 1 AND a.STATUS = 80 AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1 WHERE o.orgid IN (100000351) ) x GROUP BY x.orgcode
  
  UNION ALL
  
  SELECT 1 AS category1
   ,'1.6' AS category2
   ,13 AS num
   ,'1' AS itemid
   ,'-' AS sign
   ,'应收账款(税前)' AS itemname
   ,o.orgcode
   ,trim(to_char(round(nvl(sum(a.balanceamount), 0), 2), '99999999999990.99')) AS amount
  FROM organization o
  LEFT JOIN arap a
  INNER JOIN ord b ON b.orderid = a.zmclorderid AND b.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND b.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1 AND a.booksetid = 100006335 AND b.STATUS IN (70, 80) ON a.orgid = o.orgid AND a.relatedtrantype = 11 AND a.araptype = 1 WHERE o.orgid IN (100000351)
  GROUP BY o.orgcode
  
  UNION ALL
  
  SELECT 1 AS category1
   ,'1.6.1' AS category2
   ,14 AS num
   ,'1' AS itemid
   ,'-' AS sign
   ,'含退货:' AS itemname
   ,o.orgcode
   ,trim(to_char(round(nvl(x.balanceamount + y.balanceamount, 0), 2), '99999999999990.99')) AS amount
  FROM organization o
  LEFT JOIN (
   SELECT nvl(sum(a.balanceamou nt), 0) AS balanceamount
    ,a.orgid
   FROM arap a
    ,ord b
   WHERE a.araptype = 1 AND a.relatedtrantype = 11 AND b.STATUS IN (70, 80) AND b.orderid = a.zmclorderid AND a.orgid IN (100000351) AND b.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND b.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1 AND a.booksetid = 100006335
   GROUP BY a.orgid
   ) x ON x.orgid = o.orgid
  LEFT JOIN (
   SELECT nvl(sum(a.balanceamount), 0) AS balanceamount
    ,a.orgid
   FROM arap a
    ,returnslip b
   WHERE a.araptype = 1 AND a.zmclsliptype = 3 AND b.STATUS = 80 AND b.returnid = a.zmclslipid AND a.orgid IN (100000351) AND b.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND b.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1 AND a.booksetid = 100006335
   GROUP BY a.orgid
   ) y ON y.orgid = o.orgid
  WHERE o.orgid IN (100000351)
  
  UNION ALL
  
  SELECT 1 AS category1
   ,'1.7' AS category2
   ,15 AS num
   ,'1' AS itemid
   ,'-' AS sign
   ,'预收款冲销(税前)' AS itemname
   ,o.orgcode
   ,trim(to_char(round(nvl(sum(x.realamtsettled), 0), 2), '99999999999990.99')) AS amount
  FROM organization o
  LEFT JOIN (
   SELECT b.realamtsettled
    ,a.orgid
   FROM arapassign a
    ,arapassigndetail b
    ,invoice c
   WHERE a.arapassignid = b.arapassignid AND a.STATUS = 80 AND a.assigntype = 1 AND a.assignmethod = 11 AND b.relatedtrancode = c.invoicecode AND c.orgid = a.orgid AND a.orgid IN (100000351)
    /* --addedby scottzu Issue MCL-POS V202189 start */
    AND c.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND c.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1 /* --added by scottzhu Issue MCL-POS V202189 */ END
   ) x ON o.orgid = x.orgid
  WHERE o.orgid IN (100000351)
  GROUP BY o.orgcode
  
  UNION ALL
  
  SELECT 1 AS category1
   ,'1.8' AS category2
   ,16 AS num
   ,'1' AS itemid
   ,' ' AS sign
   ,'期间内实际收款总额(税前)' AS itemname
   ,x.orgcode
   ,trim(to_char(round(nvl(sum(x.amount), 0), 2), '99999999999990.99')) AS amount
  FROM (
   SELECT o.orgcode
    ,sum(a.totalamount) AS amount
   FROM OR ganization o
   LEFT JOIN payment a ON a.orgid = o.orgid AND a.STATUS = 80 AND a.paymenttype = 1 AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1
   WHERE o.orgid IN (100000351)
   GROUP BY o.orgcode
   
   UNION ALL
   
   SELECT o.orgcode
    ,sum(a.zcttotalamount) AS amount
   FROM organization o
   LEFT JOIN arapadjustment a
   INNER JOIN arapadjustmentdetail b ON a.arapadjustmentid = b.arapadjustmentid AND b.arapadjusttype IN (5, 6) ON a.sourceorgid = o.orgid AND a.araptype = 1 AND a.STATUS = 80 AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1 WHERE o.orgid IN (100000351)
   GROUP BY o.orgcode
   ) x
  GROUP BY x.orgcode
  
  UNION ALL
  
  SELECT 1 AS category1
   ,'1.8.1' AS category2
   ,17 AS num
   ,'1' AS itemid
   ,' ' AS sign
   ,'现金:' AS itemname
   ,y.orgcode
   ,trim(to_char(round(nvl(sum(y.amount), 0), 2), '99999999999990.99'))
  FROM (
   sele ct o.orgcode
   ,sum(a.totalamount) AS amount FROM organization o
   LEFT JOIN payment a ON a.orgid = o.orgid AND a.STATUS = 80 AND a.paymenttype = 1 AND a.paymentmethod = 3 AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1
   WHERE o.orgid IN (100000351)
   GROUP BY o.orgcode
   
   UNION ALL
   
   SELECT o.orgcode
    ,sum(a.zcttotalamount) AS amount
   FROM organization o
   LEFT JOIN arapadjustment a
   INNER JOIN arapadjustmentdetail b ON a.arapadjustmentid = b.arapadjustmentid AND b.arapadjusttype IN (5, 6) ON a.sourceorgid = o.orgid AND a.araptype = 1 AND a.STATUS = 80 AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1 WHERE o.orgid IN (100000351)
   GROUP BY o.orgcode
   ) y
  GROUP BY y.orgcode
  
  UNION ALL
  
  SELECT 1 AS category1
   ,'1.8.2' AS category2
   ,18 AS num
   ,'1' AS itemid
   ,' ' AS sign
   ,'支票:' AS itemname
   ,o.orgcode
   ,tri m(to_char(round(nvl(sum(a.totalamount), 0), 2), '99999999999990.99')) AS amount
  FROM organization o
  LEFT JOIN payment a ON o.orgid = a.orgid AND a.paymenttype = 1 AND a.paymentmethod = 7 AND a.STATUS = 80 AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1
  WHERE o.orgid IN (100000351)
  GROUP BY o.orgcode
  
  UNION ALL
  
  SELECT 1 AS category1
   ,'1.8.3' AS category2
   ,19 AS num
   ,'1' AS itemid
   ,' ' AS sign
   ,'刷卡:' AS itemname
   ,o.orgcode
   ,trim(to_char(round(nvl(sum(a.totalamount), 0), 2), '99999999999990.99')) AS amount
  FROM organization o
  LEFT JOIN payment a ON o.orgid = a.orgid AND a.STATUS = 80 AND a.paymenttype = 1 AND a.paymentmethod NOT IN (3, 7) AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1
  WHERE o.orgid IN (100000351)
  GROUP BY o.orgcode
  
  UNION ALL
  
  SELECT 1 AS category1
   ,'1.9' AS category2
   ,20 AS num
   ,'1' AS itemid
   ,' ' AS sign
   ,'期间内销售总额(含退货税前)' AS itemname
   ,x.orgcode
   ,trim(to_char(round(nvl(sum(x.amount), 0), 2), '99999999999990.99')) AS amount
  FROM (
   SELECT sum(a.totalamount) AS amount
    ,o.orgcode
   FROM organization o
   LEFT JOIN ord a
   INNER JOIN ordertype b ON a.ordertypeid = b.ordertypeid AND b.ordertypecode NOT IN ('202', '303') ON o.orgid = a.sellerid AND a.ownerorgid = a.sellerid AND a.STATUS IN (70, 80) AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1 WHERE o.orgid IN (100000351)
   GROUP BY o.orgcode
   
   UNION ALL
   
   SELECT - 1 * nvl(sum(ordd.amount), 0) AS amount
    ,o.orgcode
   FROM organization o
   LEFT JOIN ord a
   INNER JOIN ordertype b ON a.ordertypeid = b.ordertypeid AND b.ordertypecode NOT IN ('202', '303')
   INNER JOIN orderdetail ordd ON a.orderid = ordd.orderid
   INNER JOIN item it ON ordd.selleritemid = it.itemid
   INNER JOIN category cat ON it.categoryid = cat.categoryid AND cat.categorycod e = '00601' ON o.orgid = a.sellerid AND a.ownerorgid = a.sellerid AND a.STATUS IN (70, 80) AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1 WHERE o.orgid IN (100000351)
   GROUP BY o.orgcode
   
   UNION ALL
   
   SELECT sum(a.totalamount * - 1) AS amount
    ,o.orgcode
   FROM organization o
   LEFT JOIN returnslip a ON o.orgid = a.orgid AND a.returntype = 1 AND a.STATUS = 80 AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1
   WHERE o.orgid IN (100000351)
   GROUP BY o.orgcode
   ) x
  GROUP BY x.orgcode
  
  UNION ALL
  
  SELECT 2 AS category1
   ,'2.1' AS category2
   ,21 AS num
   ,'2' AS itemid
   ,'+' AS sign
   ,'采购汇总金额(税前)' AS itemname
   ,o.orgcode
   ,trim(to_char(round(nvl(sum(a.totalamount), 0), 2), '99999999999990.99')) AS amount
  FROM organization o
  LEFT JOIN ord a ON o.orgid = a.buyerid AND a.STATUS IN (70, 80) AND a.ownerorgi d = a.buyerid AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1 AND EXISTS (
    SELECT 1
    FROM invoice a
     ,invoicedetail b
     ,receipt c
    WHERE a.invoiceid = b.invoiceid AND a.STATUS = 80 AND b.relatedtrantype = 9 AND b.relatedtranid = c.receiptid AND a.orderid = c.relatedtranid
    )
  WHERE o.orgid IN (100000351)
  GROUP BY o.orgcode
  
  UNION ALL
  
  SELECT 2 AS category1
   ,'2.1.1' AS category2
   ,22 AS num
   ,'2' AS itemid
   ,'+' AS sign
   ,'税后:' AS itemname
   ,o.orgcode
   ,trim(to_char(round(nvl(sum(round(b.netprice / (1 + b.taxrate) * b.orderqty, 2)), 0), 2), '99999999999990.99')) AS amount
  FROM organization o
  LEFT JOIN ord a
  INNER JOIN orderdetail b ON a.orderid = b.orderid ON o.orgid = a.buyerid AND a.STATUS IN (70, 80) AND a.ownerorgid = a.buyerid AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1 AND EXISTS (
    SELECT 1
    FROM invoice a
     ,invoicedetail b
     ,receipt c
    WHERE a.invoiceid = b.invoiceid AND a.STATUS = 80 AND b.relatedtrantype = 9 AND b.relatedtranid = c.receiptid AND a.orderid = c.relatedtranid
    ) WHERE o.orgid IN (100000351)
  GROUP BY o.orgcode
  
  UNION ALL
  
  SELECT 2 AS category1
   ,'2.2' AS category2
   ,23 AS num
   ,'2' AS itemid
   ,'+' AS sign
   ,'其他营业外支付(税前)' AS itemname
   ,o.orgcode
   ,trim(to_char(round(nvl(sum(a.zcttotalamount), 0), 2), '99999999999990.99')) AS amount
  FROM organization o
  LEFT JOIN arapadjustment a
  INNER JOIN arapadjustmentdetail b ON a.arapadjustmentid = b.arapadjustmentid AND b.arapadjusttype IN (5, 6) ON o.orgid = a.sourceorgid AND a.araptype = 2 AND a.STATUS = 80 AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1 WHERE o.orgid IN (100000351)
  GROUP BY o.orgcode
  
  UNION ALL
  
  SELECT 2 AS category1
   ,'2.3' AS category2
   ,24 AS num
   ,'2' AS itemid
   ,'+' AS sign
   ,'实付金额(税前)' AS ite mname
   ,x.orgcode
   ,trim(to_char(round(nvl(sum(x.realamtsettled), 0), 2), '99999999999990.99')) AS amount
  FROM (
   SELECT DISTINCT b.*
    ,o.orgcode
   FROM organization o
   LEFT JOIN payment a
   INNER JOIN paymentdetail b
   INNER JOIN arap c
   INNER JOIN invoicedetail d
   INNER JOIN billing e ON d.billingid = e.billingid AND e.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') ON c.relatedtranid = d.invoiceid ON b.arapid = c.arapid AND c.relatedtrantype = 11 AND c.araptype = 2 ON a.paymentid = b.paymentid ON o.orgid = a.orgid AND a.paymenttype = 2 AND a.STATUS = 80 AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1 WHERE o.orgid IN (100000351) ) x GROUP BY x.orgcode
   
   UNION ALL
   
   SELECT 2 AS category1
    ,'2.4' AS category2
    ,25 AS num
    ,'2' AS itemid
    ,'-' AS sign
    ,'采购退货金额(税前)' AS itemname
    ,o.orgcode
    ,trim(to_char(round(nvl(0 - sum(x.arapsettleamt), 0), 2), '99999999999990.99')) AS amount
   FROM organization o
   LEFT JOIN (
    SELECT DISTINCT a.*
     ,b.orgid
    FROM paymentdetail a
     ,invoice b
     ,invoicedetail c
     ,billing d
     ,payment n
    WHERE a.paymentid = n.paymentid AND a.relatedtrancode = b.invoicecode AND b.invoiceid = c.invoiceid AND c.billingid = d.billingid AND d.billingtypeid = 4 AND b.orgid = n.orgid AND n.STATUS = 80 AND b.orgid IN (100000351) AND n.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND n.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1
    ) x ON o.orgid = x.orgid
   WHERE o.orgid IN (100000351)
   GROUP BY o.orgcode
   
   UNION ALL
   
   SELECT 2 AS category1
    ,'2.4.1' AS category2
    ,26 AS num
    ,'2' AS itemid
    ,'-' AS sign
    ,'未收:' AS itemname
    ,x.orgcode
    ,trim(to_char(round(0 - nvl(sum(x.balanceamount), 0), 2), '99999999999990.99')) AS amount
   FROM (
    SELECT DISTINCT d.*
     ,o.orgcode
    FROM organization o
    LEFT JOIN returnslip a
    INNER JOIN billing b
    INNER JOIN invoicedetail c
    INNER JOIN arap d ON c.invoiceid = d.relatedtranid AND d.relatedtrantype = 11 AND d.araptype = 2 ON c.billingid = b.billingid ON b.relatedtranid = a.returnid AND b.billingtypeid = 4 ON o.orgid = a.orgid AND a.returntype = 2 AND a.STATUS = 80 AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1 WHERE o.orgid IN (100000351) ) x GROUP BY x.orgcode
    
    UNION ALL
    
    SELECT 2 AS category1
     ,'2.5' AS category2
     ,27 AS num
     ,'2' AS itemid
     ,'-' AS sign
     ,'应付账款(税前)' AS itemname
     ,o.orgcode
     ,trim(to_char(round(nvl(sum(a.balanceamount), 0), 2), '99999999999990.99')) AS amount
    FROM organization o
    LEFT JOIN arap a
    INNER JOIN ord b ON b.orderid = a.zmclorderid AND b.STATUS = 80 AND b.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND b.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1 AND a.booksetid = 100006335 ON o.orgid = a.orgid AND a.relatedtrantype = 11 AND a.araptype = 2 WHERE o.orgid IN (100000351)
    GROUP BY o.orgcode
    
    UNION ALL
    
    SELECT 2 AS category1
     ,'2.5.1' AS category2
     ,28 AS num
     ,'2' AS itemid
     ,'-' AS sign
     ,'含退货:' AS itemname
     ,o.orgcode
     ,trim(to_char(round((nvl(x.balanceamount, 0) + nvl(y.balanceamount, 0)), 2), '99999999999990.99')) AS amount
    FROM organization o
    LEFT JOIN (
     SELECT nvl(sum(a0.balanceamount), 0) AS balanceamount
      ,a0.orgid
     FROM arap a0
      ,ord b
     WHERE a0.araptype = 2 AND a0.relatedtrantype = 11 AND b.STATUS = 80 AND b.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND b.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1 AND a0.booksetid = 100006335 AND b.orderid = a0.zmclorderid AND a0.orgid IN (100000351)
     GROUP BY a0.orgid
     ) x ON o.orgid = x.orgid
    LEFT JOIN (
     SELECT nvl(sum(a1.balanceamount), 0) AS balanceamount
      ,a1.orgid
     FROM arap a1
      ,returnslip b1
     WHERE a1.araptype = 2 AND a1.zmclsliptype = 7 AND b1.STATUS = 80 AND b1.returnid = a1.zmclslipid AND a1.orgid IN (100000351) AND b1.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a1.booksetid = 100006335 AND b1.glda te < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1
     GROUP BY a1.orgid
     ) y ON o.orgid = y.orgid
    WHERE o.orgid IN (100000351)
    
    UNION ALL
    
    SELECT 2 AS category1
     ,'2.6' AS category2
     ,29 AS num
     ,'2' AS itemid
     ,'-' AS sign
     ,'预付款冲销(税前)' AS itemname
     ,o.orgcode
     ,trim(to_char(round(nvl(sum(x.realamtsettled), 0), 2), '99999999999990.99')) AS amount
    FROM organization o
    LEFT JOIN (
     SELECT b.realamtsettled
      ,a.orgid
     FROM arapassign a
      ,arapassigndetail b
      ,invoice c
     WHERE a.arapassignid = b.arapassignid AND a.STATUS = 80 AND a.assigntype = 2 AND a.assignmethod = 11 AND b.relatedtrancode = c.invoicecode AND c.orgid = a.orgid AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1 AND a.orgid IN (100000351)
     ) x ON o.orgid = x.orgid
    WHERE o.orgid IN (100000351)
    GROUP BY o.orgcode
    
    UNION ALL
    
    SELECT 2 AS category1
     ,'2.7' AS category2
     ,30 AS num
     ,'2' AS itemid
     ,' ' AS sign
     ,'期间内实际付款总额( 含税)' AS itemname
     ,x.orgcode
     ,trim(to_char(round(nvl(sum(x.amount), 0), 2), '99999999999990.99')) AS amount
    FROM (
     SELECT o.orgcode
      ,sum(a.totalamount) AS amount
     FROM organization o
     LEFT JOIN payment a ON a.orgid = o.orgid AND a.STATUS = 80 AND a.paymenttype = 2 AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1
     WHERE o.orgid IN (100000351)
     GROUP BY o.orgcode
     
     UNION ALL
     
     SELECT o.orgcode
      ,sum(a.zcttotalamount) AS amount
     FROM organization o
     LEFT JOIN arapadjustment a
     INNER JOIN arapadjustmentdetail b ON a.arapadjustmentid = b.arapadjustmentid AND b.arapadjusttype IN (5, 6) ON a.sourceorgid = o.orgid AND a.araptype = 2 AND a.STATUS = 80 AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1 WHERE o.orgid IN (100000351)
     GROUP BY o.orgcode
     ) x
    GROUP BY x.orgcode
    
    UNION ALL
    
    SELECT 2 AS category1
     ,'2.7.1' AS category2
     ,31 AS num
     ,'2' AS itemid
     ,' ' AS sign
     ,'现金:' AS itemname
     ,y.orgcode
     ,trim(to_char(round(nvl(sum(y.amount), 0), 2), '99999999999990.99')) AS amount
    FROM (
     SELECT o.orgcode
      ,sum(a.totalamount) AS amount
     FROM organization o
     LEFT JOIN payment a ON a.orgid = o.orgid AND a.STATUS = 80 AND a.paymenttype = 2 AND a.paymentmethod = 3 AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1
     WHERE o.orgid IN (100000351)
     GROUP BY o.orgcode
     
     UNION ALL
     
     SELECT o.orgcode
      ,sum(a.zcttotalamount) AS amount
     FROM organization o
     LEFT JOIN arapadjustment a
     INNER JOIN arapadjustmentdetail b ON a.arapadjustmentid = b.arapadjustmentid AND b.arapadjusttype IN (5, 6) ON a.sourceorgid = o.orgid AND a.araptype = 2 AND a.STATUS = 80 AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1 WHERE o.orgid IN (1000003 51)
     GROUP BY o.orgcode
     ) y
    GROUP BY y.orgcode
    
    UNION ALL
    
    SELECT 2 AS category1
     ,'2.7.2' AS category2
     ,32 AS num
     ,'2' AS itemid
     ,' ' AS sign
     ,'支票:' AS itemname
     ,o.orgcode
     ,trim(to_char(round(nvl(sum(a.totalamount), 0), 2), '99999999999990.99')) AS amount
    FROM organization o
    LEFT JOIN payment a ON o.orgid = a.orgid AND a.paymenttype = 2 AND a.paymentmethod = 7 AND a.STATUS = 80 AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1
    WHERE o.orgid IN (100000351)
    GROUP BY o.orgcode
    
    UNION ALL
    
    SELECT 2 AS category1
     ,'2.7.3' AS category2
     ,33 AS num
     ,'2' AS itemid
     ,' ' AS sign
     ,'刷卡:' AS itemname
     ,o.orgcode
     ,trim(to_char(round(nvl(sum(a.totalamount), 0), 2), '99999999999990.99')) AS amount
    FROM organization o
    LEFT JOIN payment a ON o.orgid = a.orgid AND a.STATUS = 80 AND a.paymenttype = 2 AND a.paymentmethod NOT IN (3, 7) AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm- dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1
    WHERE o.orgid IN (100000351)
    GROUP BY o.orgcode
    
    UNION ALL
    
    SELECT 2 AS category1
     ,'2.8' AS category2
     ,34 AS num
     ,'2' AS itemid
     ,' ' AS sign
     ,'期间内采购总额(含退货税前)' AS itemname
     ,x.orgcode
     ,trim(to_char(round(nvl(sum(x.amount), 0), 2), '99999999999990.99')) AS amount
    FROM (
     SELECT 0 AS amount
      ,o.orgcode
     FROM organization o
     WHERE 1 = 1 AND o.orgid IN (100000351)
     
     UNION ALL
     
     SELECT a.totalamount AS amount
      ,o.orgcode
     FROM organization o
      ,ord a
     WHERE 1 = 1 AND o.orgid = a.buyerid AND a.ownerorgid = a.buyerid AND a.STATUS IN (70, 80) AND o.orgid IN (100000351) AND EXISTS (
       SELECT 1
       FROM invoice a
        ,invoicedetail b
        ,receipt c
       WHERE a.invoiceid = b.invoiceid AND a.STATUS = 80 AND b.relatedtrantype = 9 AND b.relatedtranid = c.receiptid AND a.orderid = c.relatedtranid AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1
       )
     
     UNION ALL
     
     SELECT (a.totalamount * - 1) AS amount
      ,o.orgcode
     FROM organization o
      ,returnslip a
     WHERE 1 = 1 AND o.orgid = a.orgid AND a.returntype = 2 AND a.STATUS = 80 AND o.orgid IN (100000351) AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1
     ) x
    GROUP BY x.orgcode
    
    UNION ALL
    
    SELECT 3 AS category1
     ,'3.1' AS category2
     ,35 AS num
     ,'3' AS itemid
     ,' ' AS sign
     ,'采购入库(税后)' AS itemname
     ,o.orgcode
     ,trim(to_char(round(nvl(sum(a.pretaxamount), 0), 2), '99999999999990.99')) AS amount
    FROM organization o
    LEFT JOIN receipt a ON o.orgid = a.orgid AND a.receipttype = 1 AND a.STATUS = 80 AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1
    WHERE o.orgid IN (100000351)
    GROUP BY o.orgcode
    
    UNION ALL
    
    SELECT 3 AS category1
     ,'3.2' AS category2
     ,36 AS num
     ,'3' AS itemid
     ,' ' AS sign
     ,'销售退货入库(税后)' AS i temname
     ,o.orgcode
     ,trim(to_char(round(nvl(sum(b.amount), 0), 2), '99999999999990.99')) AS amount
    FROM organization o
    LEFT JOIN returnslip a
    INNER JOIN invjournal b ON a.returnid = b.relatedtranid AND b.relatedtrantype = 24 AND a.orgid = b.orgid ON o.orgid = a.orgid AND a.returntype = 1 AND a.STATUS = 80 AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1 WHERE o.orgid IN (100000351)
    GROUP BY o.orgcode
    
    UNION ALL
    
    SELECT 3 AS category1
     ,'3.3' AS category2
     ,37 AS num
     ,'3' AS itemid
     ,' ' AS sign
     ,'盘点盘盈入库(税后)' AS itemname
     ,o.orgcode
     ,trim(to_char(round(nvl(sum(b.price * b.qty), 0), 2), '99999999999990.99')) AS amount
    FROM organization o
    LEFT JOIN invadjustment a
    INNER JOIN invadjustmentdetail b ON a.invadjustmentid = b.invadjustmentid ON o.orgid = a.orgid AND a.STATUS = 80 AND a.adjusttype = 1 AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('201 5-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1 WHERE o.orgid IN (100000351)
    GROUP BY o.orgcode
    
    UNION ALL
    
    SELECT 3 AS category1
     ,'3.4' AS category2
     ,38 AS num
     ,'3' AS itemid
     ,' ' AS sign
     ,'其它入库(税后)' AS itemname
     ,o.orgcode
     ,trim(to_char(round(nvl(sum(a.pretaxamount), 0), 2), '99999999999990.99')) AS amount
    FROM organization o
    LEFT JOIN receipt a ON o.orgid = a.orgid AND a.receipttype = 9 AND a.STATUS = 80 AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1
    WHERE o.orgid IN (100000351)
    GROUP BY o.orgcode
    
    UNION ALL
    
    SELECT 3 AS category1
     ,'3.5' AS category2
     ,39 AS num
     ,'3' AS itemid
     ,' ' AS sign
     ,'内部调拨入库(税后)' AS itemname
     ,o.orgcode
     ,trim(to_char(round(nvl(sum(b.costprice * b.qty), 0), 2), '99999999999990.99')) AS amount
    FROM organization o
    LEFT JOIN transfer a
    INNER JOIN transferdetail b ON a.transferid = b.transferid ON o.orgid = a.toorgid AND a.STATUS = 80 AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1 WHERE o.orgid IN (100000351)
    GROUP BY o.orgcode
    
    UNION ALL
    
    SELECT 3 AS category1
     ,'3.6' AS category2
     ,40 AS num
     ,'3' AS itemid
     ,' ' AS sign
     ,'期间内入库成本总额(税后)' AS itemname
     ,x.orgcode
     ,trim(to_char(round(nvl(sum(x.amount), 0), 2), '99999999999990.99')) AS amount
    FROM (
     SELECT round(nvl(sum(a.pretaxamount), 0), 2) AS amount
      ,o.orgcode
     FROM organization o
     LEFT JOIN receipt a ON o.orgid = a.orgid AND a.receipttype = 1 AND a.STATUS = 80 AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1
     WHERE o.orgid IN (100000351)
     GROUP BY o.orgcode
     
     UNION ALL
     
     SELECT round(nvl(sum(b.amount), 0), 2) AS amount
      ,o.orgcode
     FROM organization o
     LEFT JOIN returnslip a
     INNER JOIN invjournal b ON a.returnid = b.relatedtranid AND b.relatedtrantype = 24 AND a.orgid = b.orgid ON o.orgid = a.OR gid AND a.returntype = 1 AND a.STATUS = 80 AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1 WHERE o.orgid IN (100000351)
     GROUP BY o.orgcode
     
     UNION ALL
     
     SELECT round(nvl(sum(b.price * b.qty), 0), 2) AS amount
      ,o.orgcode
     FROM organization o
     LEFT JOIN invadjustment a
     INNER JOIN invadjustmentdetail b ON a.invadjustmentid = b.invadjustmentid ON o.orgid = a.orgid AND a.STATUS = 80 AND a.adjusttype = 1 AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1 WHERE o.orgid IN (100000351)
     GROUP BY o.orgcode
     
     UNION ALL
     
     SELECT round(nvl(sum(a.pretaxamount), 0), 2)
      ,o.orgcode
     FROM organization o
     LEFT JOIN receipt a ON o.orgid = a.orgid AND a.receipttype = 9 AND a.STATUS = 80 AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm- dd hh24:mi:ss') + 1
     WHERE o.orgid IN (100000351)
     GROUP BY o.orgcode
     
     UNION ALL
     
     SELECT round(nvl(sum(b.costprice * b.qty), 0), 2) AS amount
      ,o.orgcode
     FROM organization o
     LEFT JOIN transfer a
     INNER JOIN transferdetail b ON a.transferid = b.transferid ON o.orgid = a.toorgid AND a.STATUS = 80 AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1 WHERE o.orgid IN (100000351)
     GROUP BY o.orgcode
     ) x
    GROUP BY x.orgcode
    
    UNION ALL
    
    SELECT 4 AS category1
     ,'4.1' AS category2
     ,41 AS num
     ,'4' AS itemid
     ,' ' AS sign
     ,'销售出库(税后)' AS itemname
     ,o.orgcode
     ,trim(to_char(round(nvl(sum(b.costprice * b.qty), 0), 2), '99999999999990.99')) AS amount
    FROM organization o
    LEFT JOIN delivery a
    INNER JOIN deliverydetail b ON a.deliveryid = b.deliveryid ON o.orgid = a.sellerid AND a.STATUS = 80 AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-d d hh24:mi:ss') + 1 WHERE o.orgid IN (100000351)
    GROUP BY o.orgcode
    
    UNION ALL
    
    SELECT 4 AS category1
     ,'4.2' AS category2
     ,42 AS num
     ,'4' AS itemid
     ,' ' AS sign
     ,'采购退货出库(税后)' AS itemname
     ,o.orgcode
     ,trim(to_char(round(nvl(sum(a.pretaxamount), 0), 2), '99999999999990.99')) AS amount
    FROM organization o
    LEFT JOIN returnslip a ON o.orgid = a.orgid AND a.returntype = 2 AND a.STATUS = 80 AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1
    WHERE o.orgid IN (100000351)
    GROUP BY o.orgcode
    
    UNION ALL
    
    SELECT 4 AS category1
     ,'4.3' AS category2
     ,43 AS num
     ,'4' AS itemid
     ,' ' AS sign
     ,'盘点盘亏出库(税后)' AS itemname
     ,o.orgcode
     ,trim(to_char(round(nvl(sum(b.costamount), 0), 2), '99999999999990.99')) AS amount
    FROM organization o
    LEFT JOIN invadjustment a
    INNER JOIN invadjustmentdetail b ON a.invadjustmentid = b.invadjustmentid ON a.orgid = o.orgid AND a.STATUS = 80 AND a.adjusttype = 2 AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1 WHERE o.orgid IN (100000351)
    GROUP BY o.orgcode
    
    UNION ALL
    
    SELECT 4 AS category1
     ,'4.4' AS category2
     ,44 AS num
     ,'4' AS itemid
     ,' ' AS sign
     ,'其它出库(税后)' AS itemname
     ,o.orgcode
     ,trim(to_char(round(nvl(sum(b.costprice * b.qty), 0), 2), '99999999999990.99')) AS amount
    FROM organization o
    LEFT JOIN selfuseslip a
    INNER JOIN selfuseslipdetail b ON a.selfuseslipid = b.selfuseslipid ON o.orgid = a.orgid AND a.STATUS = 80 AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1 WHERE o.orgid IN (100000351)
    GROUP BY o.orgcode
    
    UNION ALL
    
    SELECT 4 AS category1
     ,'4.5' AS category2
     ,45 AS num
     ,'4' AS itemid
     ,' ' AS sign
     ,'内部调拨出库(税后)' AS itemname
     ,o.orgcode
     ,trim(to_char(round(nvl(sum(b.costprice * b.qty), 0), 2), '99999999999990.99')) AS amount
    FROM organization o
    LEFT JOIN transfer a
    INNER JOIN transferdetail b ON a.transferid = b.transferid ON o.orgid = a.fromorgid AND a.STATUS = 80 AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1 WHERE o.orgid IN (100000351)
    GROUP BY o.orgcode
    
    UNION ALL
    
    SELECT 4 AS category1
     ,'4.6' AS category2
     ,46 AS num
     ,'4' AS itemid
     ,' ' AS sign
     ,'期间内出库成本总额(税后)' AS itemname
     ,x.orgcode
     ,trim(to_char(round(nvl(sum(x.amount), 0), 2), '99999999999990.99')) AS amount
    FROM (
     SELECT round(nvl(sum(b.costprice * b.qty), 0), 2) AS amount
      ,o.orgcode
     FROM organization o
     LEFT JOIN delivery a
     INNER JOIN deliverydetail b ON a.deliveryid = b.deliveryid ON o.orgid = a.sellerid AND a.STATUS = 80 AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1 WHERE o.orgid IN (100000351)
     GROUP BY o.orgcode
     
     UNION ALL
     
     SELECT round(nvl(sum(a.pretaxamount), 0), 2) AS amount
      ,o.orgcode
     FROM organization o
     LEFT JOIN returnslip a ON o.orgid = a.orgid AND a.returntype = 2 AND a.STATUS = 80 AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1
     WHERE o.orgid IN (100000351)
     GROUP BY o.orgcode
     
     UNION ALL
     
     SELECT round(nvl(sum(b.costamount), 0), 2) AS amount
      ,o.orgcode
     FROM organization o
     LEFT JOIN invadjustment a
     INNER JOIN invadjustmentdetail b ON a.invadjustmentid = b.invadjustmentid ON a.orgid = o.orgid AND a.STATUS = 80 AND a.adjusttype = 2 AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1 WHERE o.orgid IN (100000351)
     GROUP BY o.orgcode
     
     UNION ALL
     
     SELECT round(nvl(sum(b.costprice * b.qty), 0), 2) AS amount
      ,o.orgcode
     FROM organization o
     LEFT JOIN selfuseslip a
     INNER JOIN selfuseslipdetail b ON a.selfuseslipid = b.selfuseslipid ON o.orgid = a.orgid AND a.sta tus = 80 AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1 WHERE o.orgid IN (100000351)
     GROUP BY o.orgcode
     
     UNION ALL
     
     SELECT round(nvl(sum(b.costprice * b.qty), 0), 2) AS amount
      ,o.orgcode
     FROM organization o
     LEFT JOIN transfer a
     INNER JOIN transferdetail b ON a.transferid = b.transferid ON o.orgid = a.fromorgid AND a.STATUS = 80 AND a.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1 WHERE o.orgid IN (100000351)
     GROUP BY o.orgcode
     ) x
    GROUP BY x.orgcode
    
    UNION ALL
    
    SELECT 5 AS category1
     ,'5.1' AS category2
     ,47 AS num
     ,'5' AS itemid
     ,' ' AS sign
     ,'期间内毛利润总额(税后)' AS itemname
     ,o.orgcode
     ,trim(to_char(round(nvl(sum(x.pretaxamount - x.costamount), 0), 2), '99999999999990.99')) AS amount
    FROM organization o
    LEFT JOIN (
     SELECT h.orgid
      ,h.pretaxamount
      ,h.costamount
     FROM billing h
     WHERE 1 = 1 AND h.gldate < to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1 AND h.gldate >= to_date('2015-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND (h.billingtypeid = 1 OR h.billingtypeid = 3 OR h.billingtypeid = 5) AND h.relatedtrantype = 10 AND h.orgid IN (100000351) AND EXISTS (
       SELECT 1
       FROM ord a
       WHERE a.orderid = h.orderid AND a.STATUS IN (70, 80) AND a.ownerorgid = a.sellerid
       )
     ) x ON o.orgid = x.orgid
    WHERE o.orgid IN (100000351)
    GROUP BY o.orgcode
    
    UNION ALL
    
    SELECT 6 AS category1
     ,'6.1' AS category2
     ,48 AS num
     ,'6' AS itemid
     ,' ' AS sign
     ,'期初库存余额(税后)' AS itemname
     ,o.orgcode
     ,trim(to_char(round(nvl(sum(t.initialamt), 0), 2), '99999999999990.99')) AS amount
    FROM organization o
    LEFT JOIN invbalance t ON o.orgid = t.orgid AND t.orgid IN (100000351) AND t.periodid = 100141650
    WHERE o.orgid IN (100000351)
    GROUP BY o.orgcode
    
    UNION ALL
    
    SELECT 6 AS category1
     ,'6.2' AS category2
     ,49 AS num
     ,'6' AS itemid
     ,' ' AS sign
     ,'期末库存余额(税后)' AS itemname
     ,o.orgcode
     ,trim(to_char(round(nvl(sum(t.endamt), 0), 2), '99999999999990.99')) AS amount
    FROM organization o
    LEFT JOIN invbalance t ON o.orgid = t.orgid AND t.orgid IN (100000351) AND t.periodid = 100141650
    WHERE o.orgid IN (100000351) AND (t.initialqty <> 0 OR t.debit <> 0 OR t.credit <> 0 OR t.endqty <> 0)
    GROUP BY o.orgcode
    
    UNION ALL
    
    SELECT 6 AS category1
     ,'6.2.1' AS category2
     ,50 AS num
     ,'6' AS itemid
     ,' ' AS sign
     ,'轮胎:' AS itemname
     ,o.orgcode
     ,trim(to_char(round(nvl(sum(t.endamt), 0), 2), '99999999999990.99')) AS amount
    FROM organization o
    LEFT JOIN invbalance t
    INNER JOIN item a
    INNER JOIN category f ON a.categoryid = f.categoryid AND f.categorycode LIKE '001%' ON a.categoryid = f.categoryid ON o.orgid = t.orgid AND t.itemid = a.itemid AND t.orgid = a.orgid AND t.orgid IN (100000351) AND t.periodid = 100141650 WHERE o.orgid IN (100000351) GROUP BY o.orgcode
    
    UNION ALL
    
    SELECT 6 AS category1
     ,'6.2.2' AS category2
     ,51 AS num
     ,'6' AS itemid
     ,' ' AS sign
     ,'配件:' AS itemname
     ,o.orgcode
     ,t rim(to_char(round(nvl(sum(t.endamt), 0), 2), '99999999999990.99')) AS amount
    FROM organization o
    LEFT JOIN invbalance t
    INNER JOIN item a
    INNER JOIN category f ON a.categoryid = f.categoryid AND f.categorycode LIKE '002%' ON a.categoryid = f.categoryid ON o.orgid = t.orgid AND t.itemid = a.itemid AND t.orgid = a.orgid AND t.orgid IN (100000351) AND t.periodid = 100141650 WHERE o.orgid IN (100000351) GROUP BY o.orgcode
    
    UNION ALL
    
    SELECT 7 AS category1
     ,'7.1' AS category2
     ,52 AS num
     ,'7' AS itemid
     ,' ' AS sign
     ,'应收总额(税前)' AS itemname
     ,o.orgcode
     ,trim(to_char(round(nvl(sum(p.balanceamount), 0), 2), '99999999999990.99')) AS amount
    FROM organization o
    LEFT JOIN arapbalance p ON o.orgid = p.sourceorgid AND p.accperiodid = 100141650 AND p.araptype = 1 AND p.booksetid = 100006335
    WHERE o.orgid IN (100000351)
    GROUP BY o.orgcode
    
    UNION
    
    SELECT 7 AS category1
     ,'7.2' AS category2
     ,53 AS num
     ,'7' AS itemid
     ,' ' AS sign
     ,'应付总额(税前)' AS itemname
     ,o.orgcode
     ,trim(to_char(round(nvl(sum(p.balanceamount), 0), 2), '99999999999990.99')) AS amount
    FROM organization o
    LEFT JOIN arapbalance p ON o.orgid = p.sourceorgid AND p.accperiodid = 100141650 AND p.araptype = 2 AND p.booksetid = 100006335
    WHERE o.orgid IN (100000351)
    GROUP BY o.orgcode
    ) z
   ORDER BY z.num
   )
  )
 )

Open in new window

whist not a performance issue;  SQL code comments buried into large SQL queries beginning with just 2 dashes are a potential disaster

/* comment */

is far safer!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

slightwv (䄆 Netminder) Commented:
>>makes sense and should improve the performance.

I doubt it will make a huge difference.

I'm concerned that things seem to slow down over time.  I saw some lock issues so check your connections to the database.

The entire cause might be the app isn't properly releasing connections.  If a connection locks a row and stays around never releasing the lock and a few weeks go by, Oracle will need to do a lot of work to get a consistent read on new transactions.

>>Is there any other finding, comment?

If it isn't a cleanup of connections issue, and you cannot change the SQL to make huge leaps in performance, then you'll need to reconfigure and/or add hardware.  Look at the disks.  See if you can reconfigure things to move parts of Oracle around so you can access things like tables and indexes from different disks.  Maybe move more read only tables and heavy write tables around.

When all else fails:  Throw hardware at it!!!   The more read/write heads you have, the better.
0
MatthewLiuAuthor Commented:
Could you exploit more the lock issues? Is it shown in the AWR report?
0
slightwv (䄆 Netminder) Commented:
>>Could you exploit more the lock issues? I

That is what I get by going from memory.  I thought I remembered high locks but cannot find the metric that made me thing that.

I cannot find what made me think that but given the fact that is slows down over the month, something more than just normal usage is causing it.

For the snap I looped at you have doubled sessions in the 30 minutes.  200 new session in the next 30 minutes.  Is that "normal" for your users?

I did see a few more things that I would look into.

Unless there is a reason, compatible should be set to your current version.  You have: 11.2.0.0.0.

I would also look into what this stat means "cell physical IO interconnect bytes".  That is what lead me to Exadata when I looked up what it meant.
0
Geert GOracle dbaCommented:
you have enq: CF - contention 20 seconds ...
how many controlfiles do you have and how big are they ?

and loads of row lock contention
> multiple connections trying to update the same rows

i hope you have a tuning dba at your site ?
having a quick at such a union all query ...
i see a lot of this : WHERE a.orgid IN (100000351)
should that 100000351 be a parameter ?

the tuning dba might have a first quote: "use bind variables"
there is also some hard parsing going on to confirm that

WHERE a.orgid IN (100000351)
it's different orgid in different union all queries and from what i see ... always 1 argument
WHERE a.orgid = :param_orgid

> that would yield 1 query instead of everytime a new one
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MatthewLiuAuthor Commented:
@Geert Gruwez  2 control fies and 13.7 MB each
0
MatthewLiuAuthor Commented:
here are the execution plan of top 10 SQL (by elapse time). any comment?
executionplan.zip
0
slightwv (䄆 Netminder) Commented:
I only checked the first few and I don't see a problem with any of them.  I cannot explain the elapsed time based on the individual SQL statements.

To be honest, I've not seen anyone that can take an AWR report and say "look here" for the issues.  I tend to use them as a guide on where to narrow my focus.

We have already pointed out a couple of places to start looking.  I would LOVE to know what the following metric is telling you:
 "cell physical IO interconnect bytes"

It seems to be the largest number in the report.

I would probably open an SR with Oracle Support.  They have the tools and knowledge about each individual metric and what it means.  Some of those, they just don't share.
0
MatthewLiuAuthor Commented:
The DBA's response to question "is the 'cell physical IO interconnect bytes' figures at normal level?"
usually we use "avg wait(ms)" to confirm the performance of IO.
sample:
IO.png
the "Avg wait(ms)" is more than 20ms , IO could be slow.
0
slightwv (䄆 Netminder) Commented:
I'm not sure what you are telling us.

You asked the DBA about 'X' and his reply was "well if "Y" is < 20ms everything is OK".  You then show us "Y" is > 20ms?

Have you contacted Oracle Support?

The AWR report itself doesn't tell us much.  Every database is different.  What is "normal" for one database may not be "normal" for any other.  They all have their own personality.

Since you stated that things slow down and people cannot log in towards the end of the month tells me some resources aren't being released.  I'm not sure I can tell you what that is just based on a couple of AWR reports.

I look for things that jump out at me.  I've posted what those are.  If the DBA says they are normal, I'm not sure what else I can tell you.
0
MatthewLiuAuthor Commented:
DBA was saying that they had no idea of 'cell physical IO interconnect bytes', they usually only check "avg wait(ms)" . And based on that, they think the performance was slow, but should not be that bad that users cannot even login.
Anyway, with all these tries, I do not think we can find anything solid with existing data. I escalated to have this case raised to Oracle support. The system performs normal until month end, last day of the month and first day of next, we will have to wait and see
0
Geert GOracle dbaCommented:
looking at an an average for performance tuning ???? duh !

you can have millions of query performing subsecond
and at the end of the moment have 1 query running for 2 days

the average will still look good

not really a good approach

i'd look at the end of the moment what querry's are actually running on the database
and figure out which are the longest running and consume the most resources like buffer gets and physical reads
0
slightwv (䄆 Netminder) Commented:
>>The system performs normal until month end, last day of the month and first day of next, we will have to wait and see

I would look at end of month processing.  A maxed out server can delay logins long enough they either time out or appear to never log in and the users quit trying.
0
MatthewLiuAuthor Commented:
@Geert Gruwez the AWR reports I uploaded was last month end data, peek time was 10-11am and 2-4pm
0
Geert GOracle dbaCommented:
Sowwy, i never use awr.
I've got a website which gives me top anything for any period i want.
I haven't cleaned out the data yet, so i can go back about 3 years.

We call it ITSS. It's The Stupid Sql.
Which needs tuning is added in our mind.
In 99% of the cases tuning the sql solves performance.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.