sorgu

gistfile1.txt

SELECT G.STORE_CODE,
       G.BARCODE,
       G.SKU,
       CAMPAIGN,
       VANILLA,
       SELF_BUY,
       NAME         as name,
       SEGMENT_NAME as segmentName,
       HAS_IMEI     as hasImei,
       TAX_RATIO    as taxRatio,
       TAX_TYPE     as taxType
FROM (SELECT Q.STORE_CODE as STORE_CODE,
             Q.BARCODE       BARCODE,
             Q.SKU           SKU,
             SUM(CAMPAIGN)   CAMPAIGN,
             SUM(VANILLA)    VANILLA,
             SUM(SELF_BUY)   SELF_BUY
      FROM (SELECT T.STORE_CODE                                          STORE_CODE,
                   S.OFFER_TYPE,
                   S.SKU                                                 SKU,
                   S.BARCODE                                             BARCODE,
                   CASE WHEN (OFFER_TYPE = 'CAMPAIGN') THEN 1 ELSE 0 END CAMPAIGN,
                   CASE WHEN (OFFER_TYPE = 'VANILLA') THEN 1 ELSE 0 END  VANILLA,
                   CASE WHEN (OFFER_TYPE = 'SELF_BUY') THEN 1 ELSE 0 END SELF_BUY
            FROM V2_INVENTORY_TRANSACTION T
                     INNER JOIN V2_INVENTORY_TRANSACTION_SUB_ITEM S ON T.ID = S.TRANSACTION_ID
                     INNER JOIN V2_INVENTORY_TRANSACTION_IMEI I ON S.ID = I.TRANSACTION_ITEM_ID
                     INNER JOIN (SELECT IMEI, STORE_CODE, BARCODE
                                 FROM (SELECT I.IMEI,
                                              T.STORE_CODE,
                                              S.BARCODE,
                                              NVL(SUM(CASE WHEN (IO_TYPE = 'IN') THEN 1 END), 0) +
                                              NVL(SUM(CASE WHEN (IO_TYPE = 'OUT') THEN -1 END), 0) DIFFERENCE
                                       FROM V2_INVENTORY_TRANSACTION T
                                                INNER JOIN V2_INVENTORY_TRANSACTION_SUB_ITEM S ON T.ID = S.TRANSACTION_ID
                                                INNER JOIN V2_INVENTORY_TRANSACTION_IMEI I ON S.ID = I.TRANSACTION_ITEM_ID
                                       WHERE T.IS_ACTIVE = 1
                                         AND ACTION_TYPE != 'CANCEL'
                                         AND T.STATE = 'DONE'
                                         AND S.BARCODE = '6941059602200'
                                         AND T.STORE_CODE IN ('S264800')
                                       GROUP BY I.IMEI, T.STORE_CODE, S.BARCODE)
                                 WHERE DIFFERENCE > 0) THE_STORE_HAS_IMEI ON THE_STORE_HAS_IMEI.IMEI = I.IMEI AND
                                                                             THE_STORE_HAS_IMEI.STORE_CODE =
                                                                             T.STORE_CODE AND
                                                                             THE_STORE_HAS_IMEI.BARCODE =
                                                                             S.BARCODE AND T.IS_ACTIVE = 1
            GROUP BY T.STORE_CODE, I.IMEI, SKU, S.BARCODE, OFFER_TYPE, QUANTITY) Q
      GROUP BY Q.STORE_CODE, Q.BARCODE, Q.SKU) G
         INNER JOIN V2_INVENTORY_PRODUCT P ON P.SKU = G.SKU AND P.BARCODE = G.BARCODE AND P.IS_ACTIVE = 1;