SELECT --ITH.CONTRACT, --ITH.PART_NO,
             ITH.LOCATION_NO, W.WAREHOUSE_ID,
             W.LOCATION_GROUP, -- !!! used location_group from WAREHOUSE_BAY_BIN_TAB because in inventory_transaction_hist_tab.location_group is null for transaction from IFS 2003
             SUM(DECODE(ITH.DIRECTION, '+', ITH.QUANTITY, '-', -ITH.QUANTITY, 0)) QTY,
             SUM(SUM(DECODE(ITH.DIRECTION, '+', ITH.QUANTITY, '-', -ITH.QUANTITY, 0))) OVER() TOTAL_QTY,
             --SUM(DECODE(ITH.DIRECTION, '+', ITH.QUANTITY, '-', -ITH.QUANTITY, 0)) /
             --SUM(SUM(DECODE(ITH.DIRECTION, '+', ITH.QUANTITY, '-', -ITH.QUANTITY, 0))) OVER() WAVG,
             ROW_NUMBER() OVER (ORDER BY ITH.location_no) rn,
             COUNT(1) OVER() CNT
        FROM INVENTORY_TRANSACTION_HIST_TAB ITH, WAREHOUSE_BAY_BIN_TAB W
       WHERE ITH.CONTRACT = 'BO' AND ITH.PART_NO = '30.E1486-173' AND ITH.CONFIGURATION_ID = '*'
         AND ITH.TRANSACTION_ID < 15030521
         AND W.LOCATION_GROUP = 'TR02'
         AND ITH.LOCATION_NO IS NOT NULL
         AND ITH.LOCATION_NO = W.LOCATION_NO (+)
         AND ITH.CONTRACT = W.CONTRACT (+)
       --GROUP BY ROLLUP(ITH.LOCATION_NO)
       GROUP BY ITH.LOCATION_NO, W.WAREHOUSE_ID, W.LOCATION_GROUP


       SELECT ITH.DIRECTION,ITH.QUANTITY
       FROM INVENTORY_TRANSACTION_HIST_TAB ITH, WAREHOUSE_BAY_BIN_TAB W
       WHERE ITH.CONTRACT = 'BO' AND ITH.PART_NO = '30.E1486-173' AND ITH.CONFIGURATION_ID = '*'
         AND ITH.TRANSACTION_ID < 15030521
         AND W.LOCATION_GROUP = 'TR02'
         AND ITH.LOCATION_NO IS NOT NULL
         AND ITH.LOCATION_NO = W.LOCATION_NO (+)
         AND ITH.CONTRACT = W.CONTRACT (+)