一个执行47小时、耗尽CPU的SQL优化

woyigerenktv

anlinew 发表于 2014-2-20 15:28
看起来像是空调行业
一个组织一个月的市场数据应该不会恐怖到哪儿里去

非也，非也。。。

woyigerenktv

TX__wangbeng 发表于 2014-2-11 16:06
发下执行计划。返回多少数据？

返回结果是0

TX__wangbeng

woyigerenktv 发表于 2014-2-27 15:36
返回结果是0

收集统计信息，去掉HINT。

anlinew

woyigerenktv 发表于 2014-2-27 15:33
非也，非也。。。

非空调还是非数据量不恐怖？

整个空调行业旺季月销量也不过千万吧，一个销售组织能有多少。。。

anlinew

hash join被禁用了？
感觉你们这系统被折腾的不轻，hint也乱搞
从执行计划看，一个组织3万多数据，3千多个组织，如果数据比较均衡的话，总数据量1亿左右，不算很恐怖，不过玩儿不好就恐怖了。。。

Yong Huang

Another thing I see is that full table scan on employee contributes a large part of the total cost, and also the sort on mkt_term. Can a suitable index be used on employee? Can the sort (or merge join) involving mkt_term be avoided?

anlinew

这里也是我有疑问因此怀疑系统被动的比较厉害的地方，CBO通常这里应该用hash 来替代merge sort

这个是典型的统计分析类业务，employee相当于常说的“维表”，字面理解应该是员工或者说是“业务员”，估计employee_id上是有索引的，并且通常还是主键
往往：
1、理论上这个表不会是大表，就算 merge sort ，该表访问一次，在这里基本也不会成为真正的瓶颈
2、往往要分析的数据会用到几乎维表全部的数据，比如楼主例子里的1个月的业务数据几乎应该涵盖当前分析组织的所有业务员

通常这种情况下，employee_id上的主键索引并不能起到很好的优化作用，因为尽管是通过employee_id索引来访问，但是会有大量loop，最后不仅基本访问了所有数据，还有重复访问，比全表扫描成本还高

对于楼主的应用场景，看起来组织比较多，比较好的方式是用organization_id 上的索引过滤到一个组织的数据，然后与事实表过滤后的结果集做hash join

看起来组织这个维度在楼主的系统里冗余的比较好，如果真是有3千多个组织，并且大量业务不会跨组织，倒是有个比较简单的优化方案可以采取。

woyigerenktv

anlinew 发表于 2014-2-28 08:52
非空调还是非数据量不恐怖？

整个空调行业旺季月销量也不过千万吧，一个销售组织能有多少。。。

他说第三，没人敢说第二

woyigerenktv

anlinew 发表于 2014-3-4 17:41
这里也是我有疑问因此怀疑系统被动的比较厉害的地方，CBO通常这里应该用hash 来替代merge sort

这个是典 ...

INSERT INTO SALES.CCS_IMS_DEALER_CONTROL@RDIMS SELECT :B4 , TY.IMS_CUST_ID, TY.IMS_CUST_CODE, TY.IMS_CUST_NAME, TY.ORGANIZATION_ID, TY.ORGANIZATION_NAME, DECODE(TY.BILL_TYPE, 0, '鍚�', '鏄�')BILL_TYPE, DECODE(TY.YEAR, 0, '鍚�', '鏄�')YEAR, DECODE(TY.SMONTH, 0, '鍚�', '鏄�')SMONTH, DECODE(TY.EMONTH, 0, '鍚�', '鏄�')EMONTH, TY.QTY_APPLY, TY.QTY_BILL, TY.QTY_ONHAND, TY.EXCH_RATE, TY.RATE_DIFF, TY.QTY1, TY.AMT1, TY.SCALE1, TY.PRICE1, TY.QTY_RATE, TY.QTY2, TY.AMT2, TY.SCALE2, TY.PRICE2, TY.AMT_RATE, TY.QTY_INV, CASE WHEN TY.BILL_TYPE = 0 THEN 0 ELSE ROUND(65+TY.FEN1+TY.FEN2+TY.FEN3-TY.FEN4+TY.FEN5, 4) END LJTH_RATE, TY.QTY_BILL_DRAFT, TY.QTY_BILL_THREE, TY.QTY_BILL_FIRST FROM (SELECT TS.*, CASE WHEN TS.SMONTH = 1 THEN 5 ELSE 0 END FEN1, CASE WHEN TS.YEAR = 1 THEN 10 ELSE 0 END FEN5, CASE WHEN TS.EXCH_RATE > 0.9 THEN 10 ELSE 10*TS.EXCH_RATE END FEN2, CASE WHEN TS.PRICE1 > 0.9 THEN 10 ELSE 10*TS.PRICE1 END FEN3, CASE WHEN (TS.QTY_RATE > 0.01) AND (TS.QTY_RATE <= 0.05) THEN TS.QTY_RATE/0.01 WHEN (TS.QTY_RATE = 0) AND (TS.PRICE1 = 0) THEN 5 WHEN TS.QTY_RATE > 0.05 THEN 5 ELSE 0 END FEN4 FROM (SELECT TP.IMS_CUST_ID , TP.IMS_CUST_CODE , TP.IMS_CUST_NAME , TP.ORGANIZATION_ID , TP.ORGANIZATION_NAME , SUM(TP.BILL_TYPE) BILL_TYPE, CASE WHEN SUM(QTY_APPLY) <= 0 THEN 0 ELSE 1 END YEAR, CASE WHEN SUM(QTY1) <= 0 THEN 0 ELSE 1 END SMONTH, CASE WHEN SUM(QTY2) <= 0 THEN 0 ELSE 1 END EMONTH , ROUND(DECODE(SUM(QTY_TOTAL), NULL, 0, 0, 0, S UM(QTY_B ILL) / SUM(QTY_TOTAL)), 8) EXCH_RATE , ROUND(DECODE((SUM(QTY_BILL) +SUM(QTY_ONHAND)), NULL, 0, 0, 0, SUM(QTY_ONHAND) /(SUM(QTY_ONHAND)+SUM(QTY_BILL))), 8) RATE_DIFF, ROUND(DECODE(SUM(QTY_BILL1), NULL, 0, 0, 0, SUM(AMT1) / SUM(QTY_BILL1)), 4) PRICE1 , ROUND(DECODE((SUM(AMT1) +SUM(SCALE1)), NULL, 0, 0, 0, SUM(SCALE1) /(SUM(SCALE1)+SUM(AMT1))), 8) QTY_RATE , ROUND(DECODE(SUM(QTY_BILL2), NULL, 0, 0, 0, SUM(AMT2) / SUM(QTY_BILL2)), 4) PRICE2 , ROUND(DECODE((SUM(AMT2) +SUM(SCALE2)), NULL, 0, 0, 0, SUM(SCALE2) /(SUM(SCALE2)+ SUM(AMT2))), 8) AMT_RATE , SUM(QTY_APPLY) QTY_APPLY , SUM(QTY_TOTAL) QTY_TOTAL , SUM(QTY_BILL) QTY_BILL , SUM(QTY_ONHAND) QTY_ONHAND , SUM(QTY1) QTY1 , SUM(QTY_BILL1) QTY_BILL1 , SUM(AMT1) AMT1 , SUM(SCALE1) SCALE1 , SUM(QTY2) QTY2 , SUM(QTY_BILL2) QTY_BILL2 , SUM(AMT2) AMT2 , SUM(SCALE2) SCALE2, SUM(QTY_INV) QTY_INV, SUM(QTY_BILL_DRAFT) QTY_BILL_DRAFT, SUM(QTY_BILL_THREE) QTY_BILL_THREE, SUM(QTY_BILL_FIRST) QTY_BILL_FIRST FROM (SELECT T.IMS_CUST_ID , T.IMS_CUST_CODE , T.IMS_CUST_NAME , T.ORGANIZATION_ID, T.ORGANIZATION_NAME , CASE WHEN IV.QTY_ONHAND > 0 THEN 1 ELSE 0 END BILL_TYPE, 0 QTY_APPLY , 0 QTY_TOTAL , 0 QTY_BILL , 0 QTY_ONHAND , 0 QTY1 , 0 QTY_BILL1 , 0 AMT1 , 0 SCALE1 , 0 QTY2 , 0 QTY_BILL2 , 0 AMT2 , 0 SCALE2, 0 QTY_INV, 0 QTY_BILL_DRAFT, 0 QTY_BILL_THREE, 0 QTY_BILL_FIRST FROM CCS_IMS_ZONE_ORGNIZATION T, CCS_IMS_CURRENT_INV IV WHERE T.ORGANIZATION_ID = IV.ORGANIZATION_ID(+) AND T.ENTITY_ID = :B1 AND IV.ENTID(+) = :B1 UNION ALL SELECT TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME , 0 BILL_TYPE , 0 QTY_APPLY , 0 QTY_TOTAL , 0 QTY_BILL, 0 QTY_ONHAND , 0 QTY1 , 0 QTY_BILL1 , 0 AMT1 , 0 SCALE1 , 0 QTY2 , 0 QTY_BILL2 , 0 AMT2 , 0 SCALE2, SUM(B.QTY_BILL)QTY_INV, 0 QTY_BILL_DRAFT, 0 QTY_BILL_THREE, 0 QTY_BILL_FIRST FROM CCS_IMS_ZONE_ORGNIZATION TT, CCS_IMS_INV_OUT_BILL B WHERE TT.ORGANIZATION_ID = B.ORGANIZATION_ID AND B.LAST_UPDATE_DATE >= TO_DATE(:B3 ||'00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND B.LAST_UPDATE_DATE <= TO_DATE(:B2 ||' 23:59:59', 'yyyy-mm-dd hh24:mi:ss') AND B.ENTID = :B1 AND B.BILL_TYPE = 5 GROUP BY TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME UNION ALL SELECT TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME , 0 BILL_TYPE , 0 QTY_APPLY , 0 QTY_TOTAL , 0 QTY_BILL, 0 QTY_ONHAND , 0 QTY1 , 0 QTY_BILL1 , 0 AMT1 , 0 SCALE1 , 0 QTY2 , 0 QTY_BILL2 , 0 AMT2 , 0 SCALE2, 0 QTY_INV, SUM(QTY_BILL_DRAFT) QTY_BILL_DRAFT, 0 QTY_BILL_THREE, 0 QTY_BILL_FIRST FROM CCS_IMS_ZONE_ORGNIZATION TT, CCS_IMS_INV_OUT_BILL B WHERE TT.ORGANIZATION_ID = B.ORGANIZATION_ID AND B.LAST_UPDATE_DATE >= TO_DATE(:B3 ||'00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND B.LAST_UPDATE_DATE <= TO_DATE(:B2 ||' 23:59:59', 'yyyy-mm-dd hh24:mi:ss') AND B.ENTID = :B1 AND B.BILL_TYPE IN(1, 2) GROUP BY TT. IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME UNION ALL SELECT TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME , 0 BILL_TYPE , 0 QTY_APPLY , 0 QTY_TOTAL , 0 QTY_BILL, 0 QTY_ONHAND , 0 QTY1 , 0 QTY_BILL1 , 0 AMT1 , 0 SCALE1 , 0 QTY2 , 0 QTY_BILL2 , 0 AMT2 , 0 SCALE2, 0 QTY_INV, 0 QTY_BILL_DRAFT, SUM(QTY_BILL_DRAFT)QTY_BILL_THREE, 0 QTY_BILL_FIRST FROM CCS_IMS_ZONE_ORGNIZATION TT, CCS_IMS_INV_OUT_BILL B WHERE TT.ORGANIZATION_ID = B.ORGANIZATION_ID AND TO_DATE(:B2 , 'yyyy-mm-dd') - TO_DATE(TO_CHAR(B.LAST_UPDATE_DATE, 'yyyy-mm-dd'), 'yyyy-mm-dd') <= 3 AND B.LAST_UPDATE_DATE >= TO_DATE(:B3 ||'00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND B.LAST_UPDATE_DATE <= TO_DATE(:B2 ||' 23:59:59', 'yyyy-mm-dd hh24:mi:ss') AND B.ENTID = :B1 AND B.BILL_TYPE IN(1, 2) GROUP BY TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME UNION ALL SELECT TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME , 0 BILL_TYPE , 0 QTY_APPLY , 0 QTY_TOTAL , 0 QTY_BILL, 0 QTY_ONHAND , 0 QTY1 , 0 QTY_BILL1 , 0 AMT1 , 0 SCALE1 , 0 QTY2 , 0 QTY_BILL2 , 0 AMT2 , 0 SCALE2, 0 QTY_INV, 0 QTY_BILL_DRAFT, 0 QTY_BILL_THREE, SUM(QTY_BILL_DRAFT) QTY_BILL_FIRST FROM CCS_IMS_ZONE_ORGNIZATION TT, CCS_IMS_INV_OUT_BILL B WHERE TT.ORGANIZATION_ID = B.ORGANIZATION_ID AND TO_DATE(:B2 , 'yyyy-mm- dd') - TO_DATE(TO_CHAR(B.LAST_UPDATE_DATE, 'yyyy-mm-dd'), 'yyyy-mm-dd') <= 1 AND B.LAST_UPDATE_DATE >= TO_DATE(:B3 ||'00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND B.LAST_UPDATE_DATE <= TO_DATE(:B2 ||' 23:59:59', 'yyyy-mm-dd hh24:mi:ss') AND B.ENTID = :B1 AND B.BILL_TYPE IN(1, 2) GROUP BY TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME UNION ALL SELECT T.IMS_CUST_ID , T.IMS_CUST_CODE , T.IMS_CUST_NAME , T.ORGANIZATION_ID, T.ORGANIZATION_NAME , 0 BILL_TYPE , SUM(T.QTY_TOTAL) QTY_APPLY , SUM(T.QTY_BILL) QTY_TOTAL , SUM(T.BCS_QTY_ACTUAL) QTY_BILL, SUM(T.QTY_DIFF) QTY_ONHAND , 0 QTY1 , 0 QTY_BILL1 , 0 AMT1 , 0 SCALE1 , 0 QTY2 , 0 QTY_BILL2 , 0 AMT2 , 0 SCALE2, 0 QTY_INV, 0 QTY_BILL_DRAFT, 0 QTY_BILL_THREE, 0 QTY_BILL_FIRST FROM (SELECT TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME , SUM(B.QTY_BILL) QTY_TOTAL , SUM(B.QTY_BILL) QTY_BILL , SUM(B.BCS_QTY_ACTUAL) BCS_QTY_ACTUAL, 0 QTY_DIFF FROM CCS_IMS_ZONE_ORGNIZATION TT, CCS_IMS_INV_OUT_BILL B WHERE TT.ORGANIZATION_ID = B.ORGANIZATION_ID AND NVL(B.QTY_BILL, 0) >= NVL(B.BCS_QTY_ACTUAL, 0) AND B.LAST_UPDATE_DATE >= TO_DATE(:B3 ||'00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND B.LAST_UPDATE_DATE <= TO_DATE(:B2 ||' 23:59:59', 'yyyy-mm-dd hh24:mi:ss') AND B.ENTID = :B1 AND B.BILL_TYPE = 1 GROUP BY TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME U NION ALL SELECT TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME , SUM(B.QTY_BILL) QTY_TOTAL , SUM(B.QTY_BILL) QTY_BILL , SUM(B.QTY_BILL) BCS_QTY_ACTUAL, SUM(B.BCS_QTY_ACTUAL - B.QTY_BILL) QTY_DIFF FROM CCS_IMS_ZONE_ORGNIZATION TT, CCS_IMS_INV_OUT_BILL B WHERE TT.ORGANIZATION_ID = B.ORGANIZATION_ID AND NVL(B.QTY_BILL, 0) < NVL(B.BCS_QTY_ACTUAL, 0) AND B.LAST_UPDATE_DATE >= TO_DATE(:B3 ||'00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND B.LAST_UPDATE_DATE <= TO_DATE(:B2 ||' 23:59:59', 'yyyy-mm-dd hh24:mi:ss') AND B.ENTID = :B1 AND B.BILL_TYPE = 1 GROUP BY TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME UNION ALL SELECT TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME , 0 QTY_TOTAL , SUM(B.QTY_BILL) QTY_BILL , SUM(B.BCS_QTY_ACTUAL) BCS_QTY_ACTUAL, 0 QTY_DIF FROM CCS_IMS_ZONE_ORGNIZATION TT, CCS_IMS_INV_OUT_BILL B WHERE B.ORGANIZATION_ID = TT.ORGANIZATION_ID AND NVL(B.QTY_BILL, 0) >= NVL(B.BCS_QTY_ACTUAL, 0) AND B.LAST_UPDATE_DATE >= TO_DATE(:B3 ||'00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND B.LAST_UPDATE_DATE <= TO_DATE(:B2 ||' 23:59:59', 'yyyy-mm-dd hh24:mi:ss') AND B.ENTID = :B1 AND B.BILL_TYPE = 3 GROUP BY TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME UNION ALL SELECT TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , T T.ORGANIZATION_NAME , 0 QTY_TOTAL , SUM(B.QTY_BILL) QTY_BILL , SUM(B.QTY_BILL) BCS_QTY_ACTUAL, SUM(B.BCS_QTY_ACTUAL - B.QTY_BILL) QTY_DIF FROM CCS_IMS_ZONE_ORGNIZATION TT, CCS_IMS_INV_OUT_BILL B WHERE B.ORGANIZATION_ID = TT.ORGANIZATION_ID AND NVL(B.QTY_BILL, 0) < NVL(B.BCS_QTY_ACTUAL, 0) AND B.LAST_UPDATE_DATE >= TO_DATE(:B3 ||'00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND B.LAST_UPDATE_DATE <= TO_DATE(:B2 ||' 23:59:59', 'yyyy-mm-dd hh24:mi:ss') AND B.ENTID = :B1 AND B.BILL_TYPE = 3 GROUP BY TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME UNION ALL SELECT TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME , SUM(B.QTY_BILL) QTY_TOTAL , SUM(B.QTY_BILL) QTY_BILL , SUM(B.BCS_QTY_ACTUAL) BCS_QTY_ACTUAL, 0 QTY_DIF FROM CCS_IMS_ZONE_ORGNIZATION TT, CCS_IMS_INV_OUT_BILL B WHERE B.ORGANIZATION_ID = TT.ORGANIZATION_ID AND NVL(B.QTY_BILL, 0) >= NVL(B.BCS_QTY_ACTUAL, 0) AND B.LAST_UPDATE_DATE >= TO_DATE(:B3 ||'00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND B.LAST_UPDATE_DATE <= TO_DATE(:B2 ||' 23:59:59', 'yyyy-mm-dd hh24:mi:ss') AND B.ENTID = :B1 AND B.BILL_TYPE = 2 GROUP BY TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME UNION ALL SELECT TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME , SUM(B.QTY_BILL) QTY_TOTAL , SUM(B.QTY_BILL) QTY_BILL , SUM(B.QTY_BILL) BC S_QTY_ACTUAL, SUM(B.BCS_QTY_ACTUAL - B.QTY_BILL) QTY_DIF FROM CCS_IMS_ZONE_ORGNIZATION TT, CCS_IMS_INV_OUT_BILL B WHERE B.ORGANIZATION_ID = TT.ORGANIZATION_ID AND NVL(B.QTY_BILL, 0) < NVL(B.BCS_QTY_ACTUAL, 0) AND B.LAST_UPDATE_DATE >= TO_DATE(:B3 ||'00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND B.LAST_UPDATE_DATE <= TO_DATE(:B2 ||' 23:59:59', 'yyyy-mm-dd hh24:mi:ss') AND B.ENTID = :B1 AND B.BILL_TYPE = 2 GROUP BY TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME UNION ALL SELECT TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME , 0 QTY_TOTAL , SUM(ABS(B.QTY_BILL)) QTY_BILL , SUM(B.BCS_QTY_ACTUAL) BCS_QTY_ACTUAL, 0 QTY_DIF FROM CCS_IMS_ZONE_ORGNIZATION TT, CCS_IMS_INV_OUT_BILL B WHERE B.ORGANIZATION_ID = TT.ORGANIZATION_ID AND NVL(ABS(B.QTY_BILL), 0) >= NVL(B.BCS_QTY_ACTUAL, 0) AND B.LAST_UPDATE_DATE >= TO_DATE(:B3 ||'00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND B.LAST_UPDATE_DATE <= TO_DATE(:B2 ||' 23:59:59', 'yyyy-mm-dd hh24:mi:ss') AND B.ENTID = :B1 AND B.BILL_TYPE = 4 GROUP BY TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME UNION ALL SELECT TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME , 0 QTY_TOTAL , SUM(B.QTY_BILL) QTY_BILL , SUM(B.QTY_BILL) BCS_QTY_ACTUAL, SUM(B.BCS_QTY_ACTUAL - B.QTY_BILL) QTY_DIF FROM CCS_IMS_ZONE_ORGNIZATION TT, CCS_IMS_I NV_OUT_BILL B WHERE B.ORGANIZATION_ID = TT.ORGANIZATION_ID AND NVL(ABS(B.QTY_BILL), 0) < NVL(B.BCS_QTY_ACTUAL, 0) AND B.LAST_UPDATE_DATE >= TO_DATE(:B3 ||'00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND B.LAST_UPDATE_DATE <= TO_DATE(:B2 ||' 23:59:59', 'yyyy-mm-dd hh24:mi:ss') AND B.ENTID = :B1 AND B.BILL_TYPE = 4 GROUP BY TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME ) T WHERE 1 = 1 GROUP BY T.IMS_CUST_ID , T.IMS_CUST_CODE , T.IMS_CUST_NAME , T.ORGANIZATION_ID , T.ORGANIZATION_NAME UNION ALL SELECT T.IMS_CUST_ID , T.IMS_CUST_CODE , T.IMS_CUST_NAME , T.ORGANIZATION_ID , T.ORGANIZATION_NAME , 0 BILL_TYPE , 0 QTY_APPLY , 0 QTY_TOTAL , 0 QTY_BILL , 0 QTY_ONHAND , SUM(T.QTY_TOTAL) QTY1 , SUM(T.QTY_BILL) QTY_BILL , SUM(T.BCS_QTY_ACTUAL) AMT1, SUM(T.QTY_DIFF) SCALE1 , 0 QTY2 , 0 QTY_BILL2 , 0 AMT2 , 0 SCALE2, 0 QTY_INV, 0 QTY_BILL_DRAFT, 0 QTY_BILL_THREE, 0 QTY_BILL_FIRST FROM (SELECT TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME , SUM(B.QTY_BILL) QTY_TOTAL , SUM(B.QTY_BILL) QTY_BILL , SUM(B.BCS_QTY_ACTUAL) BCS_QTY_ACTUAL, 0 QTY_DIFF FROM CCS_IMS_ZONE_ORGNIZATION TT, CCS_IMS_INV_OUT_BILL B WHERE TT.ORGANIZATION_ID = B.ORGANIZATION_ID AND TO_DATE(:B2 , 'yyyy-mm-dd') - TO_DATE(TO_CHAR(B.LAST_UPDATE_DATE, 'yyyy-mm-dd'), 'yyyy-mm-dd') <= 3 AND NVL(B.QTY_BILL, 0) >= NVL(B.BCS_QTY_ACTUAL, 0) AND B.LAST_UPDATE_DATE >= TO_DATE(:B3 ||'00: 00:00', 'yyyy-mm-dd hh24:mi:ss') AND B.LAST_UPDATE_DATE <= TO_DATE(:B2 ||' 23:59:59', 'yyyy-mm-dd hh24:mi:ss') AND B.ENTID = :B1 AND B.BILL_TYPE = 1 GROUP BY TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME UNION ALL SELECT TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME , SUM(B.QTY_BILL) QTY_TOTAL , SUM(B.QTY_BILL) QTY_BILL , SUM(B.QTY_BILL) BCS_QTY_ACTUAL, SUM(B.BCS_QTY_ACTUAL - B.QTY_BILL) QTY_DIFF FROM CCS_IMS_ZONE_ORGNIZATION TT, CCS_IMS_INV_OUT_BILL B WHERE TT.ORGANIZATION_ID = B.ORGANIZATION_ID AND TO_DATE(:B2 , 'yyyy-mm-dd') - TO_DATE(TO_CHAR(B.LAST_UPDATE_DATE, 'yyyy-mm-dd'), 'yyyy-mm-dd') <= 3 AND B.LAST_UPDATE_DATE >= TO_DATE(:B3 ||'00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND B.LAST_UPDATE_DATE <= TO_DATE(:B2 ||' 23:59:59', 'yyyy-mm-dd hh24:mi:ss') AND B.ENTID = :B1 AND NVL(B.QTY_BILL, 0) < NVL(B.BCS_QTY_ACTUAL, 0) AND B.BILL_TYPE = 1 GROUP BY TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME UNION ALL SELECT TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME , 0 QTY_TOTAL , SUM(B.QTY_BILL) QTY_BILL , SUM(B.BCS_QTY_ACTUAL) BCS_QTY_ACTUAL, 0 QTY_DIF FROM CCS_IMS_ZONE_ORGNIZATION TT, CCS_IMS_INV_OUT_BILL B WHERE B.ORGANIZATION_ID = TT.ORGANIZATION_ID AND TO_DATE(:B2 , 'yyyy-mm-dd') - TO_DATE(TO_CHAR(B.LAST_UPDATE_DATE, 'yyyy-mm-dd'), 'yyy y-mm-dd') <= 3 AND NVL(B.QTY_BILL, 0) >= NVL(B.BCS_QTY_ACTUAL, 0) AND B.LAST_UPDATE_DATE >= TO_DATE(:B3 ||'00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND B.LAST_UPDATE_DATE <= TO_DATE(:B2 ||' 23:59:59', 'yyyy-mm-dd hh24:mi:ss') AND B.ENTID = :B1 AND B.BILL_TYPE = 3 GROUP BY TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME UNION ALL SELECT TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME , 0 QTY_TOTAL , SUM(B.QTY_BILL) QTY_BILL , SUM(B.QTY_BILL) BCS_QTY_ACTUAL, SUM(B.BCS_QTY_ACTUAL - B.QTY_BILL) QTY_DIF FROM CCS_IMS_ZONE_ORGNIZATION TT, CCS_IMS_INV_OUT_BILL B WHERE B.ORGANIZATION_ID = TT.ORGANIZATION_ID AND TO_DATE(:B2 , 'yyyy-mm-dd') - TO_DATE(TO_CHAR(B.LAST_UPDATE_DATE, 'yyyy-mm-dd'), 'yyyy-mm-dd') <= 3 AND NVL(B.QTY_BILL, 0) < NVL(B.BCS_QTY_ACTUAL, 0) AND B.LAST_UPDATE_DATE >= TO_DATE(:B3 ||'00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND B.LAST_UPDATE_DATE <= TO_DATE(:B2 ||' 23:59:59', 'yyyy-mm-dd hh24:mi:ss') AND B.ENTID = :B1 AND B.BILL_TYPE = 3 GROUP BY TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME UNION ALL SELECT TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME , SUM(B.QTY_BILL) QTY_TOTAL , SUM(B.QTY_BILL) QTY_BILL , SUM(B.BCS_QTY_ACTUAL) BCS_QTY_ACTUAL, 0 QTY_DIF FROM CCS_IMS_ZONE_ORGNIZATION TT, CCS_IMS_INV_OUT_BILL B WHERE B.ORGANIZA TION_ID = TT.ORGANIZATION_ID AND TO_DATE(:B2 , 'yyyy-mm-dd') - TO_DATE(TO_CHAR(B.LAST_UPDATE_DATE, 'yyyy-mm-dd'), 'yyyy-mm-dd') <= 3 AND NVL(B.QTY_BILL, 0) >= NVL(B.BCS_QTY_ACTUAL, 0) AND B.LAST_UPDATE_DATE >= TO_DATE(:B3 ||'00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND B.LAST_UPDATE_DATE <= TO_DATE(:B2 ||' 23:59:59', 'yyyy-mm-dd hh24:mi:ss') AND B.ENTID = :B1 AND B.BILL_TYPE = 2 GROUP BY TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME UNION ALL SELECT TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME , SUM(B.QTY_BILL) QTY_TOTAL , SUM(B.QTY_BILL) QTY_BILL , SUM(B.QTY_BILL) BCS_QTY_ACTUAL, SUM(B.BCS_QTY_ACTUAL - B.QTY_BILL) QTY_DIF FROM CCS_IMS_ZONE_ORGNIZATION TT, CCS_IMS_INV_OUT_BILL B WHERE B.ORGANIZATION_ID = TT.ORGANIZATION_ID AND TO_DATE(:B2 , 'yyyy-mm-dd') - TO_DATE(TO_CHAR(B.LAST_UPDATE_DATE, 'yyyy-mm-dd'), 'yyyy-mm-dd') <= 3 AND NVL(B.QTY_BILL, 0) < NVL(B.BCS_QTY_ACTUAL, 0) AND B.LAST_UPDATE_DATE >= TO_DATE(:B3 ||'00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND B.LAST_UPDATE_DATE <= TO_DATE(:B2 ||' 23:59:59', 'yyyy-mm-dd hh24:mi:ss') AND B.ENTID = :B1 AND B.BILL_TYPE = 2 GROUP BY TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME UNION ALL SELECT TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME , 0 QTY_TOTAL , SUM(ABS(B.QTY_BILL)) QTY_BILL , SUM(B.BCS_QTY_ACTUAL) BCS_QTY_ACTUAL, 0 QTY_DIF FROM CCS_IMS_ZONE_ORGNIZATION TT, CCS_IMS_INV_OUT_BILL B WHERE B.ORGANIZATION_ID = TT.ORGANIZATION_ID AND TO_DATE(:B2 , 'yyyy-mm-dd') - TO_DATE(TO_CHAR(B.LAST_UPDATE_DATE, 'yyyy-mm-dd'), 'yyyy-mm-dd') <= 3 AND NVL(ABS(B.QTY_BILL), 0) >= NVL(B.BCS_QTY_ACTUAL, 0) AND B.LAST_UPDATE_DATE >= TO_DATE(:B3 ||'00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND B.LAST_UPDATE_DATE <= TO_DATE(:B2 ||' 23:59:59', 'yyyy-mm-dd hh24:mi:ss') AND B.ENTID = :B1 AND B.BILL_TYPE = 4 GROUP BY TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME UNION ALL SELECT TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME , 0 QTY_TOTAL , SUM(B.QTY_BILL) QTY_BILL , SUM(B.QTY_BILL) BCS_QTY_ACTUAL, SUM(B.BCS_QTY_ACTUAL - B.QTY_BILL) QTY_DIF FROM CCS_IMS_ZONE_ORGNIZATION TT, CCS_IMS_INV_OUT_BILL B WHERE B.ORGANIZATION_ID = TT.ORGANIZATION_ID AND TO_DATE(:B2 , 'yyyy-mm-dd') - TO_DATE(TO_CHAR(B.LAST_UPDATE_DATE, 'yyyy-mm-dd'), 'yyyy-mm-dd') <= 3 AND NVL(ABS(B.QTY_BILL), 0) < NVL(B.BCS_QTY_ACTUAL, 0) AND B.LAST_UPDATE_DATE >= TO_DATE(:B3 ||'00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND B.LAST_UPDATE_DATE <= TO_DATE(:B2 ||' 23:59:59', 'yyyy-mm-dd hh24:mi:ss') AND B.ENTID = :B1 AND B.BILL_TYPE = 4 GROUP BY TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME ) T WHERE 1 = 1 GROUP BY T.IMS_CUST_ID , T.IM S_CUST_CODE , T.IMS_CUST_NAME , T.ORGANIZATION_ID , T.ORGANIZATION_NAME UNION ALL SELECT T.IMS_CUST_ID , T.IMS_CUST_CODE , T.IMS_CUST_NAME , T.ORGANIZATION_ID , T.ORGANIZATION_NAME , 0 BILL_TYPE , 0 QTY_APPLY , 0 QTY_TOTAL , 0 QTY_BILL , 0 QTY_ONHAND , 0 QTY1 , 0 QTY_BILL , 0 AMT1 , 0 SCALE1 , SUM(T.QTY_TOTAL) QTY2 , SUM(T.QTY_BILL) QTY_BILL1 , SUM(T.BCS_QTY_ACTUAL) AMT2, SUM(T.QTY_DIFF) SCALE2, 0 QTY_INV, 0 QTY_BILL_DRAFT, 0 QTY_BILL_THREE, 0 QTY_BILL_FIRST FROM (SELECT TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME , SUM(B.QTY_BILL) QTY_TOTAL , SUM(B.QTY_BILL) QTY_BILL , SUM(B.BCS_QTY_ACTUAL) BCS_QTY_ACTUAL, 0 QTY_DIFF FROM CCS_IMS_ZONE_ORGNIZATION TT, CCS_IMS_INV_OUT_BILL B WHERE TT.ORGANIZATION_ID = B.ORGANIZATION_ID AND TO_DATE(:B2 , 'yyyy-mm-dd') - TO_DATE(TO_CHAR(B.LAST_UPDATE_DATE, 'yyyy-mm-dd'), 'yyyy-mm-dd') <= 1 AND NVL(B.QTY_BILL, 0) >= NVL(B.BCS_QTY_ACTUAL, 0) AND B.LAST_UPDATE_DATE >= TO_DATE(:B3 ||'00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND B.LAST_UPDATE_DATE <= TO_DATE(:B2 ||' 23:59:59', 'yyyy-mm-dd hh24:mi:ss') AND B.ENTID = :B1 AND B.BILL_TYPE = 1 GROUP BY TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME UNION ALL SELECT TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME , SUM(B.QTY_BILL) QTY_TOTAL , SUM(B.QTY_BILL) QTY_BILL , SUM(B.QTY_BILL) BCS_QTY_ACTUAL, S UM(B.BCS_QTY_ACTUAL - B.QTY_BILL) QTY_DIFF FROM CCS_IMS_ZONE_ORGNIZATION TT, CCS_IMS_INV_OUT_BILL B WHERE TT.ORGANIZATION_ID = B.ORGANIZATION_ID AND TO_DATE(:B2 , 'yyyy-mm-dd') - TO_DATE(TO_CHAR(B.LAST_UPDATE_DATE, 'yyyy-mm-dd'), 'yyyy-mm-dd') <= 1 AND NVL(B.QTY_BILL, 0) < NVL(B.BCS_QTY_ACTUAL, 0) AND B.LAST_UPDATE_DATE >= TO_DATE(:B3 ||'00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND B.LAST_UPDATE_DATE <= TO_DATE(:B2 ||' 23:59:59', 'yyyy-mm-dd hh24:mi:ss') AND B.ENTID = :B1 AND B.BILL_TYPE = 1 GROUP BY TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME UNION ALL SELECT TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME , 0 QTY_TOTAL , SUM(B.QTY_BILL) QTY_BILL , SUM(B.BCS_QTY_ACTUAL) BCS_QTY_ACTUAL, 0 QTY_DIF FROM CCS_IMS_ZONE_ORGNIZATION TT, CCS_IMS_INV_OUT_BILL B WHERE B.ORGANIZATION_ID = TT.ORGANIZATION_ID AND TO_DATE(:B2 , 'yyyy-mm-dd') - TO_DATE(TO_CHAR(B.LAST_UPDATE_DATE, 'yyyy-mm-dd'), 'yyyy-mm-dd') <= 1 AND NVL(B.QTY_BILL, 0) >= NVL(B.BCS_QTY_ACTUAL, 0) AND B.LAST_UPDATE_DATE >= TO_DATE(:B3 ||'00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND B.LAST_UPDATE_DATE <= TO_DATE(:B2 ||' 23:59:59', 'yyyy-mm-dd hh24:mi:ss') AND B.ENTID = :B1 AND B.BILL_TYPE = 3 GROUP BY TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME UNION ALL SELECT TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ ID , TT.ORGANIZATION_NAME , 0 QTY_TOTAL , SUM(B.QTY_BILL) QTY_BILL , SUM(B.QTY_BILL) BCS_QTY_ACTUAL, SUM(B.BCS_QTY_ACTUAL - B.QTY_BILL) QTY_DIF FROM CCS_IMS_ZONE_ORGNIZATION TT, CCS_IMS_INV_OUT_BILL B WHERE B.ORGANIZATION_ID = TT.ORGANIZATION_ID AND TO_DATE(:B2 , 'yyyy-mm-dd') - TO_DATE(TO_CHAR(B.LAST_UPDATE_DATE, 'yyyy-mm-dd'), 'yyyy-mm-dd') <= 1 AND NVL(B.QTY_BILL, 0) < NVL(B.BCS_QTY_ACTUAL, 0) AND B.LAST_UPDATE_DATE >= TO_DATE(:B3 ||'00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND B.LAST_UPDATE_DATE <= TO_DATE(:B2 ||' 23:59:59', 'yyyy-mm-dd hh24:mi:ss') AND B.ENTID = :B1 AND B.BILL_TYPE = 3 GROUP BY TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME UNION ALL SELECT TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME , SUM(B.QTY_BILL) QTY_TOTAL , SUM(B.QTY_BILL) QTY_BILL , SUM(B.BCS_QTY_ACTUAL) BCS_QTY_ACTUAL, 0 QTY_DIF FROM CCS_IMS_ZONE_ORGNIZATION TT, CCS_IMS_INV_OUT_BILL B WHERE B.ORGANIZATION_ID = TT.ORGANIZATION_ID AND TO_DATE(:B2 , 'yyyy-mm-dd') - TO_DATE(TO_CHAR(B.LAST_UPDATE_DATE, 'yyyy-mm-dd'), 'yyyy-mm-dd') <= 1 AND NVL(B.QTY_BILL, 0) >= NVL(B.BCS_QTY_ACTUAL, 0) AND B.LAST_UPDATE_DATE >= TO_DATE(:B3 ||'00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND B.LAST_UPDATE_DATE <= TO_DATE(:B2 ||' 23:59:59', 'yyyy-mm-dd hh24:mi:ss') AND B.ENTID = :B1 AND B.BILL_TYPE = 2 GROUP BY TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATIO N_ID , TT.ORGANIZATION_NAME UNION ALL SELECT TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME , SUM(B.QTY_BILL) QTY_TOTAL , SUM(B.QTY_BILL) QTY_BILL , SUM(B.QTY_BILL) BCS_QTY_ACTUAL, SUM(B.BCS_QTY_ACTUAL - B.QTY_BILL) QTY_DIF FROM CCS_IMS_ZONE_ORGNIZATION TT, CCS_IMS_INV_OUT_BILL B WHERE B.ORGANIZATION_ID = TT.ORGANIZATION_ID AND TO_DATE(:B2 , 'yyyy-mm-dd') - TO_DATE(TO_CHAR(B.LAST_UPDATE_DATE, 'yyyy-mm-dd'), 'yyyy-mm-dd') <= 1 AND NVL(B.QTY_BILL, 0) < NVL(B.BCS_QTY_ACTUAL, 0) AND B.LAST_UPDATE_DATE >= TO_DATE(:B3 ||'00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND B.LAST_UPDATE_DATE <= TO_DATE(:B2 ||' 23:59:59', 'yyyy-mm-dd hh24:mi:ss') AND B.ENTID = :B1 AND B.BILL_TYPE = 2 GROUP BY TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME UNION ALL SELECT TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME , 0 QTY_TOTAL , SUM(ABS(B.QTY_BILL)) QTY_BILL , SUM(B.BCS_QTY_ACTUAL) BCS_QTY_ACTUAL, 0 QTY_DIF FROM CCS_IMS_ZONE_ORGNIZATION TT, CCS_IMS_INV_OUT_BILL B WHERE B.ORGANIZATION_ID = TT.ORGANIZATION_ID AND TO_DATE(:B2 , 'yyyy-mm-dd') - TO_DATE(TO_CHAR(B.LAST_UPDATE_DATE, 'yyyy-mm-dd'), 'yyyy-mm-dd') <= 1 AND NVL(ABS(B.QTY_BILL), 0) >= NVL(B.BCS_QTY_ACTUAL, 0) AND B.LAST_UPDATE_DATE >= TO_DATE(:B3 ||'00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND B.LAST_UPDATE_DATE <= TO_DATE(:B2 ||' 23:59:59', 'yyyy-mm-dd hh 24:mi:ss') AND B.ENTID = :B1 AND B.BILL_TYPE = 4 GROUP BY TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME UNION ALL SELECT TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME , 0 QTY_TOTAL , SUM(B.QTY_BILL) QTY_BILL , SUM(B.QTY_BILL) BCS_QTY_ACTUAL, SUM(B.BCS_QTY_ACTUAL - B.QTY_BILL) QTY_DIF FROM CCS_IMS_ZONE_ORGNIZATION TT, CCS_IMS_INV_OUT_BILL B WHERE B.ORGANIZATION_ID = TT.ORGANIZATION_ID AND TO_DATE(:B2 , 'yyyy-mm-dd') - TO_DATE(TO_CHAR(B.LAST_UPDATE_DATE, 'yyyy-mm-dd'), 'yyyy-mm-dd') <= 1 AND NVL(ABS(B.QTY_BILL), 0) < NVL(B.BCS_QTY_ACTUAL, 0) AND B.LAST_UPDATE_DATE >= TO_DATE(:B3 ||'00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND B.LAST_UPDATE_DATE <= TO_DATE(:B2 ||' 23:59:59', 'yyyy-mm-dd hh24:mi:ss') AND B.ENTID = :B1 AND B.BILL_TYPE = 4 GROUP BY TT.IMS_CUST_ID , TT.IMS_CUST_CODE , TT.IMS_CUST_NAME , TT.ORGANIZATION_ID , TT.ORGANIZATION_NAME ) T WHERE 1 = 1 GROUP BY T.IMS_CUST_ID , T.IMS_CUST_CODE , T.IMS_CUST_NAME , T.ORGANIZATION_ID , T.ORGANIZATION_NAME ) TP WHERE 1 = 1 GROUP BY TP.IMS_CUST_ID , TP.IMS_CUST_CODE , TP.IMS_CUST_NAME , TP.ORGANIZATION_ID , TP.ORGANIZATION_NAME )TS )TY ORDER BY TY.IMS_CUST_ID , TY.IMS_CUST_CODE , TY.IMS_CUST_NAME , TY.ORGANIZATION_ID , TY.ORGANIZATION_NAME , ROUND(100-TY.FEN1-TY.FEN2-TY.FEN3-TY.FEN4, 4) DESC

uang520

我觉得这个SQL不算很复杂，整个SQL的逻辑是围绕mkt_terminal_sale_interface做查询的主轴。
我简化了一下SQL如下：
select
m.mkt_terminal_sale_interface_id,
iv.billtypename,
c.customer_name,
mt.terminal_id terminal_id,
(select dm.dictname
    from cpcdict dm
   where lower(dm.dictcode) like 'terminal_level%'
     and dm.dictvalue = mt.terminal_level
     and dm.entid = 2750) Terminal_Level,
(select ds.dictname
    from cpcdict ds
   where lower(ds.dictcode) like 'terminal_property%'
     and ds.dictvalue = mt.terminal_property
     and ds.entid = 2750) Terminal_Property,
eh.employee_code employee_code,
d.dept_code dept_code,
dd.attribute1 Big_District,
io.specs item_spec,
i.item_id,
(select icd.item_class_name
    from item_class icd
   where io.item_class2 = icd.item_class_id
     and icd.organization_id = 2750) Item_Class2_Name,
(select icx.item_class_name
    from item_class icx
   where io.item_class1 = icx.item_class_id
     and icx.organization_id = 2750) Item_Class1_Name,
(select dt.dictname
    from cpcdict dt
   where lower(dt.dictcode) like 'capacity%'
     and dt.dictvalue = io.capacity
     and dt.entid = 2750) Capacity,
ib1.brand_username brand_sub,
ib2.brand_username brand_series
  from mkt_terminal_sale_interface m,
       item_org                    io,
       item                        i,
       employee_header             eh,
       customer                    c,
       inv_billtype                iv,
       mkt_terminal                mt,
       dept                        d,
       mkt_ccs_ims_centre          dd,
       item_brand_attribute        ib1,
       item_brand_attribute        ib2
where m.item_id = i.item_id
   and m.employee_id = eh.employee_id
   and m.billtypecode = iv.billtypecode(+)
   and m.customer_id = c.customer_id(+)
   and io.item_id = m.item_id
   and 2 = nvl(eh.is_guider, 2)
   and m.billtypecode in ('1001', '1002', '1009')
   and 2750 = io.organization_id
   and 2750 = m.organization_id
   and m.report_time >=
       to_date('2013-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
   and m.report_time <=
       to_date('2013-12-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
   and io.item_id = i.item_id
   and io.brand_sub = ib1.brand_id(+)
   and io.brand_series = ib2.brand_id(+)
   and io.organization_id = m.organization_id
   and (2 > nvl(eh.Engage_Type, 0) or 2 < nvl(eh.Engage_Type, 0))
   and eh.crm_entid = io.crm_entid
   and m.terminal_id = mt.terminal_id(+)
   and mt.organization_id = 2750
   and mt.org_id = d.dept_id(+)
   and d.entid = 2750
   and mt.org_id = dd.dept_id(+)
   and mt.crm_entid = io.crm_entid
   and nvl(mt.applies_terminal, 0) = 2
   and eh.organization_id = 2750
   And exists (Select 1
          From Sa_Deptaccessctrl Sd
         Where Sd.Orgid = mt.org_id
           and Sd.Userid = 'kfo_???'
           and Sd.Organization_Id = 2750)
   and nvl(m.is_gift, 0) <> 2