Source: DMF/PuC/AIF/DR2/DR2_item_fix.sql
-- item master
-- skus with different dept from style
-- check also the barcodes
/*
'000000192062510040',
'000000192062510050',
'000000192064710020',
'000000200124200400',
'000000202071810020',
'000000202071810050',
'000000202071810060',
'000000202061810040',
'000000202061810060',
'000000202062010050',
'000000202062010060',
'000000202065210050',
'000000202065210060',
'000000202065510020',
'000000202065510050',
'000000202065610030',
'000000202065610040',
'000000202065610060',
'000000202065710010',
'000000202067200340',
'000000202067200380',
'000000202067200420',
'000000205804510010',
'000000205804510020',
'000000205804510030',
'000000205804510050',
'000000202065710060',
'000000202065710050',
'000000192062710020',
'000000192062710060',
'000000205804510060',
'000000200124500400'
*/
SELECT * FROM ITEM_MASTER_CTRL C WHERE EXISTS (SELECT 1 FROM ITEM_MASTER_CTRL C2 WHERE C.ITEM_PARENT = C2.ITEM AND C.DEPT <> C2.DEPT);
SELECT * FROM rcib_stg_item_HEADER C WHERE EXISTS (SELECT 1 FROM rcib_stg_item_HEADER C2 WHERE C.ITEM_PARENT = C2.ITEM AND C.DEPT <> C2.DEPT);
UPDATE ITEM_MASTER_CTRL c
SET c.dept = (
SELECT c2.dept
FROM ITEM_MASTER_CTRL c2
WHERE c2.item = c.item_parent
)
WHERE EXISTS (
SELECT 1
FROM ITEM_MASTER_CTRL c2
WHERE c.item_parent = c2.item
AND c.dept <> c2.dept
);
UPDATE rcib_stg_item_HEADER c
SET c.dept = (
SELECT c2.dept
FROM rcib_stg_item_HEADER c2
WHERE c2.item = c.item_parent
)
WHERE EXISTS (
SELECT 1
FROM rcib_stg_item_HEADER c2
WHERE c.item_parent = c2.item
AND c.dept <> c2.dept
);
--Field cannot be modified.Field: SUBCLASS, ITEM: 000000001945310030 returned by program unit CORESVC_ITEM.PROCESS_IM.
SELECT * FROM ITEM_MASTER_CTRL C WHERE EXISTS (SELECT 1 FROM ITEM_MASTER_CTRL C2 WHERE C.ITEM_PARENT = C2.ITEM AND C.dept = C2.dept AND C.class = C2.class AND C.subclass <> C2.subclass);
UPDATE ITEM_MASTER_CTRL c
SET c.subclass = (
SELECT c2.subclass
FROM ITEM_MASTER_CTRL c2
WHERE c2.item = c.item_parent
)
WHERE EXISTS (
SELECT 1
FROM ITEM_MASTER_CTRL c2
WHERE c.item_parent = c2.item
AND c.dept = c2.dept
AND c.class = c2.class
AND C.subclass <> C2.subclass
);
UPDATE rcib_stg_item_HEADER c
SET c.subclass = (
SELECT c2.subclass
FROM rcib_stg_item_HEADER c2
WHERE c2.item = c.item_parent
)
WHERE EXISTS (
SELECT 1
FROM rcib_stg_item_HEADER c2
WHERE c.item_parent = c2.item
AND c.dept = c2.dept
AND c.class = c2.class
AND C.subclass <> C2.subclass
);
-- diff size on sku not match to the group values from styles
--Error ORA-20001: This differentiator ID is not part of the parent's differentiator group. Parent Diff: KG Diff: 00362ITEM: 000000102249100362 returned by program unit CORESVC_ITEM.CHECK_DIFFS.ITEM: 000000102249100362:...
select * from diff_group_head_ctrl;
select * from diff_group_detail_ctrl where diff_group_id = 'GUERTEL';
update rcib_mfqueue_item i set event_status = 'X' where event_status = 'U' and item in (
SELECT distinct
sku.item--,
--sku.item_parent,
--sku.diff_2,
--parent.diff_2 as diff_group_id
FROM item_master_ctrl sku
JOIN item_master_ctrl parent
ON sku.item_parent = parent.item
WHERE sku.item_level = sku.tran_level
AND sku.diff_2 IS NOT NULL
AND NOT EXISTS (
SELECT 1
FROM diff_group_detail_ctrl dgd,
diff_group_head_ctrl dh
WHERE dh.diff_group_id = dgd.diff_group_id
and dh.diff_type = 'S'
and dgd.diff_group_id = parent.diff_2
AND dgd.diff_id = sku.diff_2
) --order by diff_group_id
)
;
-- item season
select * from ITEM_SEASONS_CTRL where CTRL_STATUS <> 'A';
/*
;2280;2281;2302;
WHERE A.SEASON_ID IS NOT NULL AND (2=2) AND NOT EXISTS (SELECT 1 FROM DMF.PHASES_CTRL B WHERE B.SEASON_ID = A.SEASON_ID )
WHERE A.SEASON_ID IS NOT NULL AND (2=2) AND NOT EXISTS (SELECT 1 FROM DMF.SEASONS_CTRL B WHERE B.SEASON_ID = A.SEASON_ID )
WHERE A.PHASE_ID IS NOT NULL AND (2=2) AND NOT EXISTS (SELECT 1 FROM DMF.PHASES_CTRL B WHERE B.PHASE_ID = A.PHASE_ID )
*/
update ITEM_SEASONS_CTRL s
set (season_id, phase_id) = (select to_number(SUBSTR(TO_CHAR(SAISO),-1) || SUBSTR(TO_CHAR(SAISJ),-2)),
to_number(SUBSTR(TO_CHAR(SAISO),-1) || SUBSTR(TO_CHAR(SAISJ),-2))
from sap_slt_prod_pv1.mara m
where m.matnr = s.item),
ctrl_status = 'I',
ctrl_error_msg = null
where
item in (
'000000659949810040', '000000792689701150',
'000000411999510020', '000000636667603234',
'000000670999100360', '000000670999100000',
'000000020999510050', '000000670999100440',
'000000713999500400', '000000662999100000',
'000000710999500000', '000000107999500000',
'000000020999500000', '000000125999500000',
'000000099999510030', '000000995999700000',
'000000168999500000', '000000091143700460'
);
-- item supplier
-- possivelmente o processo duplica os dados ao passar pela XREF de fornecedor e tem vpns distintos pois está herdando o vpn original
- -EXISTS (SELECT 1 FROM (SELECT * FROM ITEM_SUPPLIER_CTRL) B WHERE B.ITEM = A.ITEM AND B.SUPPLIER = A.SUPPLIER HAVING COUNT(*) > 1 GROUP BY B.ITEM, B.SUPPLIER);
update item_supplier_ctrl set vpn = null where PRIMARY_SUPP_IND = 'N';
delete from item_supplier_ctrl A
WHERE PRIMARY_SUPP_IND = 'N'
and exists (SELECT 1
FROM item_supplier_ctrl B
WHERE A.ITEM = B.ITEM
and a.supplier = b.supplier
--and a.vpn <> b.vpn
and b.PRIMARY_SUPP_IND = 'Y');
--validation
select * from ITEM_SUPPLIER_ctrl a WHERE EXISTS (SELECT 1 FROM (SELECT * FROM ITEM_SUPPLIER_CTRL) B WHERE B.ITEM = A.ITEM AND B.SUPPLIER = A.SUPPLIER HAVING COUNT(*) > 1 GROUP BY B.ITEM, B.SUPPLIER);
-- sugestao deixar criar tudo explodido, insert distinct e depois fazer um update do primary ind e do vpn
-- missing primary supplier
--WHERE NOT EXISTS (SELECT 1 FROM ITEM_SUPPLIER_CTRL B WHERE A.ITEM = B.ITEM AND B.PRIMARY_SUPP_IND = 'Y')
update item_supplier_ctrl i
set PRIMARY_SUPP_IND = 'Y'
where
ctrl_error_msg like '%2746%'
and (item, supplier) in (select matnr, oracle_id
from (select ATTYP,SATNR,m.MATNR,lifnr, idnlf, relif, oracle_id, sap_merge_id, sap_org_unit, RANK() OVER (PARTITION BY m.MATNR ORDER BY relif desc, sap_org_unit DESC) AS r
from sap_slt_prod_pv1.mara m,
sap_slt_prod_pv1.eina e,
xref_sups x
where 1 = 1
and m.satnr = e.matnr
and x.sap_id = e.lifnr
)
where r = 1
and i.item = matnr
and i.supplier = oracle_id
);
--validation
select * from ITEM_SUPPLIER_ctrl a WHERE NOT EXISTS (SELECT 1 FROM ITEM_SUPPLIER_CTRL B WHERE A.ITEM = B.ITEM AND B.PRIMARY_SUPP_IND = 'Y');
-- fix one style that kept wrong
update item_supplier_ctrl set PRIMARY_SUPP_IND = 'Y' where item = '007577294' and supplier = 2375581004;
update item_supplier_ctrl set ctrl_status = 'I', ctrl_error_msg = null where ctrl_status = 'E';
--The New Line, Pipe, Linefeed and Semicolon characters are not allowed in this field.ITEM: 000000202734610040, SUPPLIER: 2350311004 returned by program unit CORESVC_ITEM.PROCESS_IS.
select * from rcib_mfqueue_item m where error_message like '%The New Line, Pipe, Linefeed and Semicolon charact%';
SELECT *
FROM ITEM_SUPPLIER_CTRL
WHERE REGEXP_LIKE(
vpn,
'(\||;|' || CHR(10) || '|' || CHR(13) || ')'
);
UPDATE ITEM_SUPPLIER_CTRL
SET VPN = REPLACE(VPN, '|', '')
WHERE VPN LIKE '%|%';
UPDATE rcib_stg_item_supplier
SET VPN = REPLACE(VPN, '|', '')
WHERE VPN LIKE '%|%';
-- item supplier country
-- same fix from item supplier
--WHERE EXISTS (SELECT 1 FROM (SELECT * FROM ITEM_SUPP_COUNTRY_CTRL) B WHERE B.ITEM = A.ITEM AND B.SUPPLIER = A.SUPPLIER AND B.ORIGIN_COUNTRY_ID = A.ORIGIN_COUNTRY_ID HAVING COUNT(*) > 1 GROUP BY B.ITEM, B.SUPPLIER, B.ORIGIN_COUNTRY_ID);
delete from ITEM_SUPP_COUNTRY_CTRL A
WHERE a.PRIMARY_SUPP_IND = 'N'
and exists (SELECT 1
FROM ITEM_SUPP_COUNTRY_CTRL B
WHERE A.ITEM = B.ITEM
and a.supplier = b.supplier
and B.ORIGIN_COUNTRY_ID = A.ORIGIN_COUNTRY_ID
--and a.unit_cost <> b.unit_cost
and b.PRIMARY_SUPP_IND = 'Y');
delete from ITEM_SUPP_COUNTRY_CTRL A
WHERE a.PRIMARY_SUPP_IND = 'N'
and exists (SELECT 1
FROM ITEM_SUPP_COUNTRY_CTRL B
WHERE A.ITEM = B.ITEM
and a.supplier = b.supplier
and B.ORIGIN_COUNTRY_ID = A.ORIGIN_COUNTRY_ID
--and a.unit_cost <> b.unit_cost
and b.PRIMARY_SUPP_IND = 'N');
select * from ITEM_SUPP_COUNTRY_CTRL a WHERE EXISTS (SELECT 1 FROM (SELECT * FROM ITEM_SUPP_COUNTRY_CTRL) B WHERE B.ITEM = A.ITEM AND B.SUPPLIER = A.SUPPLIER AND B.ORIGIN_COUNTRY_ID = A.ORIGIN_COUNTRY_ID HAVING COUNT(*) > 1 GROUP BY B.ITEM, B.SUPPLIER, B.ORIGIN_COUNTRY_ID);
-- multiples countries as primary for style items
select item, supplier from ITEM_SUPP_COUNTRY_ctrl where primary_country_ind = 'Y' group by item, supplier having count(1) > 1;
UPDATE ITEM_SUPP_COUNTRY_CTRL t
SET PRIMARY_COUNTRY_IND = 'N'
WHERE ROWID IN (
SELECT rid
FROM (
SELECT ROWID rid,
ROW_NUMBER() OVER (
PARTITION BY item, supplier
ORDER BY ROWID
) rn
FROM ITEM_SUPP_COUNTRY_CTRL
WHERE PRIMARY_COUNTRY_IND = 'Y'
)
WHERE rn > 1
);
update ITEM_SUPP_COUNTRY_CTRL set ctrl_status = 'I' where ctrl_status = 'E';
/* para validar */
-- pq tem item com o mesmo fornecedor com 2 pais de origem? por causa do fornecedor original?
-- ex: select * from ITEM_SUPP_COUNTRY_ctrl where item = 9000000000000000000946300;
--- item suppp manu ctry
-- multiples suppliers que não estão na item_supplier nem na item_supp_ctry
DELETE from ITEM_SUPP_MANU_COUNTRY_ctrl A
WHERE NOT exists (SELECT 1
FROM ITEM_SUPP_COUNTRY_ctrl B
WHERE A.ITEM = B.ITEM
and a.supplier = b.supplier);
-- multiples countries as primary for style items
select item, supplier from ITEM_SUPP_manu_COUNTRY_ctrl where primary_manu_ctry_ind = 'Y' group by item, supplier having count(1) > 1;
-- keep consistency with ITEM_SUPP_COUNTRY_CTRL
UPDATE ITEM_SUPP_MANU_COUNTRY_CTRL m
SET PRIMARY_MANU_CTRY_IND =
(
CASE
WHEN EXISTS (
SELECT 1
FROM ITEM_SUPP_COUNTRY_CTRL c
WHERE c.ITEM = m.ITEM
AND c.SUPPLIER = m.SUPPLIER
AND c.origin_country_id = m.manu_country_id
AND c.PRIMARY_COUNTRY_IND = 'Y'
)
THEN 'Y'
ELSE 'N'
END
);
update ITEM_SUPP_MANU_COUNTRY_ctrl set ctrl_status = 'I', ctrl_error_msg = null where ctrl_status = 'E';
--This item was not submitted successfully.ITEM: 007577294 returned by program unit CORESVC_ITEM.SUBMIT_APPROVE Country Of Manufacture is required for this item. There is no item-supplier-origin-country information for some of the suppli...
select * from item_supplier_ctrl where ITEM = 007577294;
select * from ITEM_SUPP_MANU_COUNTRY_ctrl where ITEM = 007577294; -- missing
select * from ITEM_SUPP_COUNTRY_ctrl where ITEM = 007577294; --missing
-- barcodes
-- no validation in DMF
-- Barcodes with less than 13 digitis must be ITEM_NUMBER_TYPE = MANL
update item_master_ctrl set ITEM_NUMBER_TYPE = 'MANL' where item_level = 3 and length(item) <> 13 and ITEM_NUMBER_TYPE <> 'MANL' ;
update rcib_stg_item_HEADER set ITEM_NUMBER_TYPE = 'MANL' where item_level = 3 and length(item) <> 13 and ITEM_NUMBER_TYPE <> 'MANL' ;
--Error: Item already exists.
-- check if there is multiples items with the same barcode or it is just reprocessing
select * from item_master_ctrl c where exists (select 1 from rcib_mfqueue_item m where m.item = c.item and error_message like '%Item already exists.%') ;
-- item loc
--DMF RULE 2857: Local Item Description must not have special characteres like New Line, Pipe, Linefeed and Semicolon characters
SELECT *
FROM ITEM_LOC_CTRL
WHERE REGEXP_LIKE(
local_item_desc,
'(\||;|' || CHR(10) || '|' || CHR(13) || ')'
);
UPDATE ITEM_LOC_CTRL
SET local_item_desc =
REPLACE(
REPLACE(
REPLACE(
REPLACE(local_item_desc, CHR(10), ' '),
CHR(13), ' '),
'|', ' '),
';', ' ')
WHERE REGEXP_LIKE(
local_item_desc,
'(\||;|' || CHR(10) || '|' || CHR(13) || ')'
);
-- BKP
create table item_master_ctrl_aps as select * from item_master_ctrl;
create table item_supplier_ctrl_aps as select * from item_supplier_ctrl;
create table ITEM_SUPP_COUNTRY_ctrl_aps as select * from ITEM_SUPP_COUNTRY_ctrl;
create table ITEM_SUPP_MANU_COUNTRY_ctrl_aps as select * from ITEM_SUPP_MANU_COUNTRY_ctrl;
select count(1) from item_supplier_ctrl;
select count(1) from item_supplier_ctrl_aps;
select count(1) from ITEM_SUPP_COUNTRY_ctrl;
select count(1) from ITEM_SUPP_COUNTRY_ctrl_aps;
select count(1) from ITEM_SUPP_MANU_COUNTRY_ctrl;
select count(1) from ITEM_SUPP_MANU_COUNTRY_ctrl_aps;
update ITEM_MASTER_ctrl set ctrl_status = 'I', ctrl_error_msg = null; --where ctrl_status = 'E';
update ITEM_SUPPLIER_ctrl set ctrl_status = 'I', ctrl_error_msg = null; --where ctrl_status = 'E';
update ITEM_SUPP_COUNTRY_ctrl set ctrl_status = 'I', ctrl_error_msg = null;-- where ctrl_status = 'E';
update ITEM_SUPP_MANU_COUNTRY_ctrl set ctrl_status = 'I', ctrl_error_msg = null;-- where ctrl_status = 'E';
SELECT * FROM item_supplier_ctrl_aps WHERE ITEM = '000000203102201020';
SELECT * FROM item_supplier_ctrl WHERE ITEM = '000000203102201020';
SELECT item_type,
treated_error_msg,
count(*)
FROM (SELECT mf.item,
decode(i.item_type, '12', '1.Style', '22', '2.Fashion Sku', '32', '3.UPC', '4.Other Skus') AS item_type,
CASE
WHEN mf.error_message LIKE '%The following field is invalid. Field name: diff_2, value: SIZEGROUP.%'
THEN 'The following field is invalid. Field name: diff_2, value: SIZEGROUP.'
WHEN mf.error_message like '%Item already exists.%'
THEN 'Item already exists.'
WHEN mf.error_message like '%DB error : RMSSUB_XITEM.CONSUME_COLLECTION_DESC aborted with error : ORA-00600: internal error code, arguments: [KGL-heap-size-exceeded]%'
THEN 'DB error : RMSSUB_XITEM.CONSUME_COLLECTION_DESC aborted with error : ORA-00600: internal error code, arguments: [KGL-heap-size-exceeded]'
WHEN mf.error_message like '%Invalid item.ITEM: % returned by program unit CORESVC_ITEM.PROCESS_CHUNK.%'
then '%Invalid item.ITEM: % returned by program unit CORESVC_ITEM.PROCESS_CHUNK.%'
WHEN mf.error_message like '%Item supplier does not exist.ITEM: %, SUPPLIER: % returned by program unit CORESVC_ITEM.PROCESS_CHUNK.%'
then '%Item supplier does not exist.ITEM: %, SUPPLIER: % returned by program unit CORESVC_ITEM.PROCESS_CHUNK.%'
WHEN mf.error_message like '%This differentiator ID is not part of the parent''s differentiator group.%'
THEN 'This differentiator ID is not part of the parent''s differentiator group.'
WHEN mf.error_message like '%Field cannot be modified.Field: DEPT%'
THEN 'Field cannot be modified.Field: DEPT'
ELSE mf.error_message END AS treated_error_msg
FROM rcib_mfqueue_item mf,
(SELECT item_level || tran_level as item_type,
item,
ctrl_id
FROM rcib_stg_item_header) i
WHERE mf.event_status = 'H'
AND mf.ctrl_id = i.ctrl_id )
GROUP BY item_type,
treated_error_msg
ORDER BY
item_type,
treated_error_msg;
select count(1), substr(mf.error_message,1,38) from rcib_mfqueue_item mf where event_status = 'H' group by substr(mf.error_message,1,38) order by count(1) desc ;
select * from rcib_mfqueue_item mf where event_status = 'H' and mf.error_message like '%This item was not submitted successful%';
select * from item_master_ctrl where item = '9000000000000000000768611';
select * from item_master_ctrl where item = '007577294';
select * from rcib_mfqueue_item where item = '007577294';
select *from ITEM_SUPP_MANU_COUNTRY_ctrl_aps where item = '007577294';
select *from ITEM_SUPP_MANU_COUNTRY_ctrl where item = '007577294';
select * from item_master_ctrl_aps where ctrl_status = 'E';
select * from item_supplier_ctrl_aps where ctrl_status = 'E';
select * from ITEM_SUPP_COUNTRY_ctrl_aps where ctrl_status = 'E';
select * from ITEM_SUPP_MANU_COUNTRY_ctrl_aps where ctrl_status = 'E';