cursor her store u gez takipli icin summary ve serial number tablolarini karsilastir

gistfile1.txt

DECLARE

    HAS_ERROR     INTEGER;
    IMEI_NAME     VARCHAR2(500);
    IMEI_COUNT    VARCHAR2(500);
    SUMMARY_NAME  VARCHAR2(500);
    SUMMARY_COUNT VARCHAR2(500);

BEGIN

    DBMS_OUTPUT.ENABLE(1000000);
    --DELETE FROM MIGRATION_CATALOG WHERE 1 = 1;
    FOR rec IN (

        SELECT STORE_CODE FROM STOCK_SUMMARY GROUP BY STORE_CODE

        )
        LOOP


            SELECT COUNT(1)
            INTO HAS_ERROR
            FROM (SELECT *
                  FROM (SELECT B.NAME as name, count(1) as count
                        FROM STOCK_CARD_SERIAL_NUMBER S
                                 INNER JOIN STOCK_CARD B ON S.BARCODE = B.BARCODE
                        WHERE 1 = 1
                          AND S.STORE_CODE = rec.STORE_CODE
                          AND B.IS_ACTIVE = 1
                          AND S.IS_ACTIVE <> 0
                          AND S.IS_SOLD = 0
                          AND S.INSERT_TIME >= date '2021-01-01'
                        GROUP BY B.NAME
                        ORDER BY B.NAME) A1

                           INNER JOIN

                       (SELECT S1.PRODUCT_NAME as name, TOTAL_QUANTITY as count
                        FROM STOCK_SUMMARY S1
                                 INNER JOIN STOCK_CARD S2 ON S1.SKU = S2.SKU
                        WHERE 1 = 1
                          AND S1.SKU = S2.SKU
                          AND S1.STORE_CODE = rec.STORE_CODE
                          AND S2.IS_ACTIVE = 1
                          AND S2.IS_TRACKING = 1
                          AND S1.INSERT_TIME >= date '2021-11-01'
                          AND S1.TOTAL_QUANTITY > 0
                        ORDER BY S1.PRODUCT_NAME) A2
                       ON A1.NAME = A2.NAME
                  WHERE A1.COUNT != A2.COUNT);

            IF (HAS_ERROR > 0) THEN
                SELECT A1.NAME
                INTO IMEI_NAME
                FROM (SELECT B.NAME as name, count(1) as count
                      FROM STOCK_CARD_SERIAL_NUMBER S
                               INNER JOIN STOCK_CARD B ON S.BARCODE = B.BARCODE
                      WHERE 1 = 1
                        AND S.STORE_CODE = rec.STORE_CODE
                        AND B.IS_ACTIVE = 1
                        AND S.IS_ACTIVE <> 0
                        AND S.IS_SOLD = 0
                        AND S.INSERT_TIME >= date '2021-01-01'
                      GROUP BY B.NAME
                      ORDER BY B.NAME) A1

                         INNER JOIN

                     (SELECT S1.PRODUCT_NAME as name, TOTAL_QUANTITY as count
                      FROM STOCK_SUMMARY S1
                               INNER JOIN STOCK_CARD S2 ON S1.SKU = S2.SKU
                      WHERE 1 = 1
                        AND S1.SKU = S2.SKU
                        AND S1.STORE_CODE = rec.STORE_CODE
                        AND S2.IS_ACTIVE = 1
                        AND S2.IS_TRACKING = 1
                        AND S1.INSERT_TIME >= date '2021-01-01'
                        AND S1.TOTAL_QUANTITY > 0
                      ORDER BY S1.PRODUCT_NAME) A2
                     ON A1.NAME = A2.NAME
                WHERE A1.COUNT != A2.COUNT;

                INSERT INTO DIFFERENCE_TABLE (STORE_CODE, COUNT)
                VALUES (rec.STORE_CODE, HAS_ERROR);

            end if;

            DBMS_OUTPUT.PUT_LINE('store code: ' || rec.STORE_CODE || ' HAS ERROR: ' || HAS_ERROR);


        END LOOP;

    -- DBMS_OUTPUT.PUT_LINE('FINISHED');
END;

--commit