TAMS Renumber

Just find and replace the renumber_id (?) with the actual renumber_id in dbo_renumbers table 

-- 1. Copy intro date

UPDATE DBO_CLASSIFICATIONDATA.part_master pmn

SET intro_date = (SELECT pm.intro_date

                    FROM DBO_CLASSIFICATIONDATA.part_master pm,

                         DBO_CLASSIFICATIONDATA.renumber_part rp

                    WHERE rp.OLD_HQ_ABBR = pm.HQ_ABBR

                    AND rp.OLD_PART_NUMBER = pm.PART_NUMBER

                    AND rp.RENUMBER_ID = ?

                    AND rp.NEW_PART_NUMBER = pmn.PART_NUMBER

                    AND rp.NEW_HQ_ABBR = pmn.HQ_ABBR)                               

WHERE part_number IN (SELECT rp.NEW_PART_NUMBER FROM DBO_CLASSIFICATIONDATA.renumber_part rp

                              WHERE rp.RENUMBER_ID = ? AND rp.NEW_HQ_ABBR = pmn.HQ_ABBR);

  

-- 2a. Copy classes

UPDATE DBO_CLASSIFICATIONDATA.class_master cmn

SET (curr, prev, hist) = (SELECT MAX(cm.CURR), MAX(cm.PREV), MAX(cm.HIST)

                  FROM DBO_CLASSIFICATIONDATA.class_master cm,

                  DBO_CLASSIFICATIONDATA.part_master pm,

                  DBO_CLASSIFICATIONDATA.part_master pmn,

                  DBO_CLASSIFICATIONDATA.renumber_part rp

                  WHERE rp.OLD_HQ_ABBR = pm.HQ_ABBR

                  AND rp.OLD_PART_NUMBER = pm.PART_NUMBER

                  AND pm.part_Id = cm.PART_ID

                  AND cm.DC_NUMBER = cmn.DC_NUMBER

                  AND rp.RENUMBER_ID = ?

                  AND rp.NEW_PART_NUMBER = pmn.PART_NUMBER

                  AND rp.NEW_HQ_ABBR = pmn.HQ_ABBR

                  AND pmn.part_id = cmn.part_id)                               

WHERE part_id IN (SELECT pmn.part_id

                  FROM DBO_CLASSIFICATIONDATA.renumber_part rp,

                  DBO_CLASSIFICATIONDATA.part_master pm,

                  DBO_CLASSIFICATIONDATA.part_master pmn,

                  DBO_CLASSIFICATIONDATA.class_master cm,

                  DBO_CLASSIFICATIONDATA.class c,

                  DBO_CLASSIFICATIONDATA.class cn 

                  WHERE rp.RENUMBER_ID = ?

                  AND rp.NEW_HQ_ABBR = pmn.HQ_ABBR

                  AND rp.NEW_PART_NUMBER = pmn.PART_NUMBER

                  AND c.class = cm.curr

                  AND cn.class = cmn.curr

                  AND rp.OLD_HQ_ABBR = pm.HQ_ABBR

                  AND rp.OLD_PART_NUMBER = pm.PART_NUMBER

                  AND cm.part_id = pm.PART_ID

                  AND c.class_order < cn.class_order

                  AND cm.DC_NUMBER = cmn.DC_NUMBER

                  AND cm.curr NOT IN ('S','O','NL')

AND cm.dc_number IN (SELECT dc_number FROM DBO_CLASSIFICATIONDATA.renumber_dc WHERE renumber_id = ?);

 

--2b. Optional class changes

UPDATE CLASS_MASTER

SET curr = REPLACE(curr,'X','')

where PART_ID IN (

    SELECT part_id FROM part_master pm, RENUMBER_PART rp

    WHERE pm.HQ_ABBR = rp.NEW_HQ_ABBR AND pm.part_number = rp.NEW_PART_NUMBER and renumber_id = ?)

AND curr != REPLACE(curr,'X','');

 

UPDATE CLASS_MASTER

SET prev = REPLACE(prev,'X','')

where PART_ID IN (

    SELECT part_id FROM part_master pm, RENUMBER_PART rp

    WHERE pm.HQ_ABBR = rp.NEW_HQ_ABBR AND pm.part_number = rp.NEW_PART_NUMBER and renumber_id = ?)

AND prev != REPLACE(prev,'X','');

 

UPDATE CLASS_MASTER

SET hist = REPLACE(hist,'X','')

where PART_ID IN (

    SELECT part_id FROM part_master pm, RENUMBER_PART rp

    WHERE pm.HQ_ABBR = rp.NEW_HQ_ABBR AND pm.part_number = rp.NEW_PART_NUMBER and renumber_id = ?)

AND hist != REPLACE(hist,'X','');

 

 

-- 3. DC sales

--Updatesales

CREATE TABLE temp_sales AS

SELECT part_id, dc_number, month_id, sales FROM sales

WHERE PART_ID IN (

      SELECT part_id from Part_master pm, renumber_Part rp WHERE pm.HQ_ABBR = rp.OLD_HQ_ABBR AND pm.part_number = rp.OLD_PART_NUMBER and renumber_id =?

      UNION

      SELECT part_id from Part_master pm, renumber_Part rp WHERE pm.HQ_ABBR = rp.NEW_HQ_ABBR AND pm.part_number = rp.NEW_PART_NUMBER and renumber_id =?)

      AND dc_number IN (SELECT DC_NUMBER FROM renumber_dc WHERE renumber_id = ?);

 

DELETE FROM SALES

WHERE PART_ID IN (

      SELECT part_id from Part_master pm, renumber_Part rp WHERE pm.HQ_ABBR = rp.OLD_HQ_ABBR AND pm.part_number = rp.OLD_PART_NUMBER and renumber_id =?

      UNION

      SELECT part_id from Part_master pm, renumber_Part rp WHERE pm.HQ_ABBR = rp.NEW_HQ_ABBR AND pm.part_number = rp.NEW_PART_NUMBER and renumber_id =?)

      AND dc_number IN (SELECT DC_NUMBER FROM renumber_dc WHERE renumber_id = ?);

 

INSERT INTO DBO_CLASSIFICATIONDATA.sales (part_id, dc_number, month_id, sales)

(SELECT NVL(pmn.part_id, pm.part_id), dc_number, month_id, sum(sales)

FROM temp_sales ts

INNER JOIN part_master pm ON ts.part_id = pm.part_id

LEFT JOIN RENUMBER_PART rp ON rp.OLD_HQ_ABBR = pm.HQ_ABBR AND rp.OLD_PART_NUMBER = pm.PART_NUMBER        and renumber_id =?

LEFT JOIN part_master pmn ON pmn.hq_abbr = rp.NEW_HQ_ABBR AND pmn.part_number = rp.NEW_PART_NUMBER

GROUP BY NVL(pmn.part_id, pm.part_id), dc_number, month_id);

 

DROP TABLE temp_sales;

 

--Update sales history yearly

DELETE FROM SALES_HISTORY_YEARLY

WHERE part_id IN ( SELECT part_id from Part_master pm, renumber_Part rp WHERE pm.HQ_ABBR = rp.NEW_HQ_ABBR AND pm.part_number = rp.NEW_PART_NUMBER and renumber_id =?)

AND dc_number IN (SELECT DC_NUMBER FROM renumber_dc WHERE renumber_id = ?);

 

INSERT INTO DBO_CLASSIFICATIONDATA.sales_history_yearly (part_id, dc_number, sales_year_1, sales_year_2, sales_year_3,sales_year_4)

            (SELECT part_id, dc_number,

            SUM(CASE WHEN sales_month<13 THEN sales ELSE 0 END),

            SUM(CASE WHEN sales_month BETWEEN 13 AND 24 THEN sales ELSE 0 END),

            SUM(CASE WHEN sales_month BETWEEN 25 AND 36 THEN sales ELSE 0 END),

            SUM(CASE WHEN sales_month BETWEEN 37 AND 48 THEN sales ELSE 0 END)

            FROM DBO_CLASSIFICATIONDATA.sales_history_v

            where part_id IN ( SELECT part_id from Part_master pm, renumber_Part rp WHERE pm.HQ_ABBR = rp.NEW_HQ_ABBR AND pm.part_number = rp.NEW_PART_NUMBER and renumber_id =?)

            AND dc_number IN (SELECT DC_NUMBER FROM renumber_dc WHERE renumber_id = ?)

           GROUP BY part_id, dc_number);

           

--Update JDE sales detail

UPDATE DBO_CLASSIFICATIONDATA.jde_sales_detail jsd

SET part_id = (SELECT pmn.part_id from Part_master pm, renumber_Part rp, part_master pmn

              WHERE pm.HQ_ABBR = rp.OLD_HQ_ABBR AND pm.part_number = rp.OLD_PART_NUMBER

              and renumber_id =?

              AND pmn.hq_abbr = rp.new_hq_abbr AND pmn.part_number = rp.new_part_number

              and pm.part_id = jsd.part_id)

WHERE part_id IN (SELECT part_id from Part_master pm, renumber_Part rp WHERE pm.HQ_ABBR = rp.OLD_HQ_ABBR AND pm.part_number = rp.OLD_PART_NUMBER and renumber_id =?)

AND dc_number IN (SELECT DC_NUMBER FROM renumber_dc WHERE renumber_id = ?);

 

 

DELETE FROM DBO_CLASSIFICATIONDATA.JDE_SALES_SUMMARY

WHERE part_id IN (      SELECT part_id from Part_master pm, renumber_Part rp WHERE pm.HQ_ABBR = rp.OLD_HQ_ABBR AND pm.part_number = rp.OLD_PART_NUMBER and renumber_id =?

      UNION

      SELECT part_id from Part_master pm, renumber_Part rp WHERE pm.HQ_ABBR = rp.NEW_HQ_ABBR AND pm.part_number = rp.NEW_PART_NUMBER and renumber_id =?);

 

 

INSERT INTO DBO_CLASSIFICATIONDATA.JDE_SALES_SUMMARY

SELECT part_id, order_type,  TRUNC(order_date, 'MM') as month_date, sum(qty_shipped) as shipped

FROM DBO_CLASSIFICATIONDATA.jde_sales_detail jsd 

WHERE  order_date >= add_months(TRUNC(sysdate, 'MM') , -14)

AND part_id IN (      SELECT part_id from Part_master pm, renumber_Part rp WHERE pm.HQ_ABBR = rp.OLD_HQ_ABBR AND pm.part_number = rp.OLD_PART_NUMBER and renumber_id =?

      UNION

      SELECT part_id from Part_master pm, renumber_Part rp WHERE pm.HQ_ABBR = rp.NEW_HQ_ABBR AND pm.part_number = rp.NEW_PART_NUMBER and renumber_id =?)

GROUP BY part_id, order_type, TRUNC(order_date, 'MM');

 

--4. DC inventory, it should fix itself with the next daily refresh

--5. Store inventory, we will just wait till the 17th for this to be refreshed

--6. Store sales

 

--Individual months need to be renumbered

DECLARE

CURSOR THREE_YEARS_MONTHS IS

SELECT table_name from (SELECT table_name

FROM dbo_store_invoice.pos_month

ORDER BY month_date DESC) WHERE rownum<=36;

 

BEGIN

FOR MONTHS IN THREE_YEARS_MONTHS

LOOP

    EXECUTE IMMEDIATE 'UPDATE dbo_store_invoice.'|| MONTHS.table_name|| ' i

        SET (HQ_ABBR, PART_NUMBER) = (SELECT NEW_HQ_ABBR, NEW_PART_NUMBER

                                      FROM RENUMBER_PART rp

                                      WHERE rp.renumber_id = ?

                                      AND old_hq_abbr = hq_abbr

                                      AND old_part_Number = part_number)

        WHERE DC_ABBR IN(SELECT DC_ABBR FROM DC_MASTER dc, RENUMBER_DC rd WHERE rd.dc_number = dc.dc_number AND RENUMBER_ID = ?)

        AND PART_NUMBER IN (SELECT OLD_PART_NUMBER FROM RENUMBER_PART WHERE OLD_HQ_ABBR = HQ_ABBR AND RENUMBER_ID = ?)';

END LOOP;

END;

 

--Defects

CREATE TABLE temp_defects AS

SELECT part_id, dc_number, month_id, sales, store_type FROM dbo_store_invoice.store_defects

WHERE PART_ID IN (

      SELECT part_id from Part_master pm, renumber_Part rp WHERE pm.HQ_ABBR = rp.OLD_HQ_ABBR AND pm.part_number = rp.OLD_PART_NUMBER and renumber_id =?

      UNION

      SELECT part_id from Part_master pm, renumber_Part rp WHERE pm.HQ_ABBR = rp.NEW_HQ_ABBR AND pm.part_number = rp.NEW_PART_NUMBER and renumber_id =?)

      AND dc_number IN (SELECT DC_NUMBER FROM renumber_dc WHERE renumber_id = ?);

 

DELETE FROM dbo_store_invoice.store_defects

WHERE PART_ID IN (

      SELECT part_id from Part_master pm, renumber_Part rp WHERE pm.HQ_ABBR = rp.OLD_HQ_ABBR AND pm.part_number = rp.OLD_PART_NUMBER and renumber_id =?

      UNION

      SELECT part_id from Part_master pm, renumber_Part rp WHERE pm.HQ_ABBR = rp.NEW_HQ_ABBR AND pm.part_number = rp.NEW_PART_NUMBER and renumber_id =?)

      AND dc_number IN (SELECT DC_NUMBER FROM renumber_dc WHERE renumber_id = ?);

 

INSERT INTO dbo_store_invoice.store_defects (month_id, part_id, dc_number, store_type, sales)

(SELECT month_id,  NVL(pmn.part_id, pm.part_id), dc_number, store_type, sum(sales) FROM temp_defects ts

INNER JOIN part_master pm ON ts.part_id = pm.part_id

LEFT JOIN RENUMBER_PART rp ON rp.OLD_HQ_ABBR = pm.HQ_ABBR AND rp.OLD_PART_NUMBER = pm.PART_NUMBER 

and renumber_id =?

LEFT JOIN part_master pmn ON pmn.hq_abbr = rp.NEW_HQ_ABBR AND pmn.part_number = rp.NEW_PART_NUMBER

GROUP BY  NVL(pmn.part_id, pm.part_id), dc_number, month_id, store_type);

 

DROP TABLE temp_defects;

 

--Sales detail

CREATE TABLE temp_store_detail AS

SELECT part_id, dc_number, month_id, sales, store_type, sale_dollars FROM dbo_store_invoice.store_sales_detail

WHERE PART_ID IN (

      SELECT part_id from Part_master pm, renumber_Part rp WHERE pm.HQ_ABBR = rp.OLD_HQ_ABBR AND pm.part_number = rp.OLD_PART_NUMBER and renumber_id =?

      UNION

      SELECT part_id from Part_master pm, renumber_Part rp WHERE pm.HQ_ABBR = rp.NEW_HQ_ABBR AND pm.part_number = rp.NEW_PART_NUMBER and renumber_id =?)

      AND dc_number IN (SELECT DC_NUMBER FROM renumber_dc WHERE renumber_id = ?);

 

DELETE FROM dbo_store_invoice.store_sales_detail

WHERE PART_ID IN (

      SELECT part_id from Part_master pm, renumber_Part rp WHERE pm.HQ_ABBR = rp.OLD_HQ_ABBR AND pm.part_number = rp.OLD_PART_NUMBER and renumber_id =?

      UNION

      SELECT part_id from Part_master pm, renumber_Part rp WHERE pm.HQ_ABBR = rp.NEW_HQ_ABBR AND pm.part_number = rp.NEW_PART_NUMBER and renumber_id =?)

      AND dc_number IN (SELECT DC_NUMBER FROM renumber_dc WHERE renumber_id = ?);

 

INSERT INTO dbo_store_invoice.store_sales_detail (month_id, part_id, dc_number, store_type, sales, sale_dollars)

(SELECT month_id,  NVL(pmn.part_id, pm.part_id), dc_number, store_type, sum(sales), sum(sale_dollars) FROM temp_store_detail ts

INNER JOIN part_master pm ON ts.part_id = pm.part_id

LEFT JOIN RENUMBER_PART rp ON rp.OLD_HQ_ABBR = pm.HQ_ABBR AND rp.OLD_PART_NUMBER = pm.PART_NUMBER

and renumber_id =?

LEFT JOIN part_master pmn ON pmn.hq_abbr = rp.NEW_HQ_ABBR AND pmn.part_number = rp.NEW_PART_NUMBER

    GROUP BY month_id,  NVL(pmn.part_id, pm.part_id), dc_number, store_type);

 

DROP TABLE temp_store_detail;

 

--Sales summary

  DELETE FROM DBO_STORE_INVOICE.STORES_SALES_SUMMARY  WHERE part_id IN

  (SELECT part_id from DBO_CLASSIFICATIONDATA.Part_master pm, DBO_CLASSIFICATIONDATA.renumber_part rp

    WHERE rp.new_hq_abbr = pm.hq_abbr AND rp.new_part_number = pm.part_number and renumber_id = ?

UNION  SELECT part_id from DBO_CLASSIFICATIONDATA.Part_master pm, DBO_CLASSIFICATIONDATA.renumber_part rp

    WHERE rp.old_hq_abbr = pm.hq_abbr AND rp.old_part_number = pm.part_number and renumber_id = ?);

 

  INSERT INTO DBO_STORE_INVOICE.STORES_SALES_SUMMARY (PART_ID, SALES_MONTH, sales)

SELECT part_id, sales_month, sum(sales) as sales

FROM dbo_store_invoice.store_sales_detail sd, dbo_classificationData.sales_month sm

WHERE sd.MONTH_ID = sm.MONTH_ID

AND sales_month<=36 AND part_id IN  (SELECT part_id from DBO_CLASSIFICATIONDATA.Part_master pm, DBO_CLASSIFICATIONDATA.renumber_part rp

    WHERE rp.new_hq_abbr = pm.hq_abbr AND rp.new_part_number = pm.part_number and renumber_id = ?

UNION  SELECT part_id from DBO_CLASSIFICATIONDATA.Part_master pm, DBO_CLASSIFICATIONDATA.renumber_part rp

    WHERE rp.old_hq_abbr = pm.hq_abbr AND rp.old_part_number = pm.part_number and renumber_id = ?)

GROUP BY part_id, sales_month;

 -- 7.   DC_PART_STOCKING_EVENT

 -- 9. Log the state of the new numbers

DECLARE V_GROUP_ID INT;

BEGIN

  --Update assigned_to and title

INSERT INTO DBO_class_transaction.transaction_group(release_date, status_id , assigned_to, title, transaction_type)

values(sysdate, 3, 7, 'DEW Single Part Renumber (New Number)',9);

 

SELECT max(group_id) INTO V_GROUP_ID 

FROm DBO_class_transaction.transaction_group WHERE assigned_to = 7  AND transaction_type = 9;

 

--Update renumber id

INSERT INTO DBO_class_transaction.transaction_key (hq_abbr, part_number, group_id)

SELECT distinct hq_abbr, part_number, V_GROUP_ID

FROM DBO_CLASSIFICATIONDATA.renumber_part rp, DBO_CLASSIFICATIONDATA.part_master pm

WHERE pm.hq_abbr = new_hq_abbr

AND pm.part_number = new_part_number

AND renumber_id  IN  (?);

 

DBO_class_transaction.create_complete_log(V_GROUP_ID);

 

DELETE FROm DBO_class_transaction.transaction_key where group_id = V_GROUP_ID;

DELETE FROm DBO_class_transaction.transaction_group where group_id = V_GROUP_ID;

 

END;

 * CHECK PARTVIEWER & LOGS for New Part Numbers

• FOUND SOME OPTIONAL CLASSES FOR AVB Renumber, Id=82, AVB PART AND NOT ON OLD PART, SO DID THE REVERSE OF ADDING OPTIONAL

--10. Query used for delete bulletin of old

SELECT old_hq_abbr, old_part_number FROM renumber_part where renumber_id = ?

Manually create Delete Bulletin in PUP