Source: DMF/PuC/AIF/RCIB.sql
--Error: Item already exists.
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.%' and m.event_status = 'H') ;
update item_master_ctrl c set ctrl_status = 'C' where exists (select 1 from rcib_mfqueue_item m where m.item = c.item and error_message like '%Item already exists.%' and ctrl_status <> 'C') ;
update rcib_mfqueue_item m set event_status = 'P' where error_message like '%Item already exists.%' and m.event_status = 'H';
commit;
-- report item status
select
decode(item_type, '12', '1.Style', '22', '2.Fashion Sku', '32', '3.Barcode', '4.Other Skus') AS ITEM_TYPE,
event_status, COUNT(1),
to_char(sysdate,'dd-mon-yyyy hh24:mi:ss')
from (select item_level || tran_level as item_type, item
from rcib_stg_item_HEADER) i
join rcib_mfqueue_item item_mf on item_mf.item = i.item
GROUP BY item_type, event_status
ORDER BY item_type, event_status;
-- check errors
select count(1), substr(error_message,1,50)
from rcib_mfqueue_item m ,
rcib_stg_item_HEADER h
where m.item = h.item
--and h.tran_level < h.item_level
--and h.item_level = 1
and m.event_status = 'H'
group by substr(error_message,1,50)
order by count(1) desc
;
select ctrl_status, count(1) from item_master_ctrl group by ctrl_status;
update item_master_ctrl c set ctrl_status = 'E' where exists (select 1 from rcib_mfqueue_item q where q.item = c.item and q.event_status = 'H');
/*
3531 Error ORA-20001: The following field is invalid. F
49 Error ORA-20001: This child/grandchild item alread
1 Invalid Class for this Department.ITEM: 9000000000
1 This item was not submitted successfully.ITEM: 007
*/
select * from rcib_mfqueue_item i where error_message LIKE '%Error ORA-20001: This differentiator ID is not par%' ;
select distinct SUBSTR(error_message, 1, INSTR(error_message, 'ITEM') - 1)
from rcib_mfqueue_item m
where error_message like '%Error ORA-20001: This differentiator ID is not par%'
and m.event_status = 'H'
order by SUBSTR(error_message, 1, INSTR(error_message, 'ITEM') - 1);
-- change status
update rcib_mfqueue_item i set event_status = 'U' where event_status = 'X' and exists (select 1 from rcib_stg_item_header r where r.item = i.item and tran_level = item_level and tran_level = 2);
update rcib_mfqueue_item i set event_status = 'U' where event_status = 'X' and exists (select 1 from rcib_stg_item_header r where r.item = i.item and tran_level < item_level and item_level = 3);
update rcib_mfqueue_item i set event_status = 'X', error_message = null where event_status = 'H';
update rcib_mfqueue_item i set event_status = 'U', error_message = null where event_status = 'W';
update rcib_mfqueue_item i set event_status = 'U' where event_status = 'H' and error_message LIKE 'Field cannot be modified.Field: SUBCLASS,%' ;
update rcib_mfqueue_item i set event_status = 'X' where event_status = 'U' and error_message LIKE 'Error ORA-20001: This differentiator ID is not par%' ;
-- release skus that the style has already been processed
update rcib_mfqueue_item i set event_status = 'U'
where event_status = 'X'
and exists (select 1
from rcib_stg_item_header r
where r.item = i.item
and tran_level = item_level
and item_level = 2
and exists (select 1
from rcib_mfqueue_item m2
where m2.item = r.item_parent
and m2.event_status = 'P'));
-- release barcodes that the sku has already been processed
update rcib_mfqueue_item i set event_status = 'H'
where event_status = 'X'
and exists (select 1
from rcib_stg_item_header r
where r.item = i.item
and tran_level < item_level
and item_level = 3
and exists (select 1
from rcib_mfqueue_item m2
where m2.item = r.item_parent
and m2.event_status = 'P'));
select count(1) from rcib_stg_item_ctrl;
select * from rcib_stg_item_header;
select * from rcib_stg_item_header_tl;
select * from rcib_stg_item_header_cfa_ext;
select * from rcib_stg_item_season;
select * from WKSP_RCIB.rcib_stg_item_image;
select * from rcib_stg_item_supplier;
select * from rcib_stg_item_supplier_cfa_ext;
select * from rcib_stg_item_supp_country;
select * from rcib_stg_item_supp_country_cfa_ext;
select * from rcib_stg_item_sc_dim;
select * from rcib_stg_item_sc_mfr;
select * from rcib_stg_item_zone_price;
INSERT INTO rcib_integration_ctrl
(activity_id,
entity_id,
process_pattern,
batch_size,
status)
VALUES
(6374,
'ITEM',
'FULL',
50000,
'STARTED');
select * from rcib_integration_ctrl where entity_id = 'ITEM' order by create_datetime desc;
SELECT * FROM WKSP_RCIB.RCIB_ACTIVITY_LOG WHERE INTERFACE_ID = 'ITEM' ORDER BY CREATED_AT DESC;
delete FROM WKSP_RCIB.RCIB_ACTIVITY_LOG_Step WHERE activity_id = 6374 ;
SELECT * FROM WKSP_RCIB.RCIB_ERROR_LOG ORDER BY CREATE_DATE DESC;
SELECT * FROM RCIB_INTEGRATION_ASYNC_JOB order by last_update_datetime desc;
/*
delete from rcib_integration_ctrl where entity_id = 'ITEM';
delete from rcib_stg_item_ctrl;
delete from rcib_integration_item_full;
delete from rcib_stg_item_header;
delete from rcib_stg_item_header_tl;
delete from rcib_stg_item_header_cfa_ext;
delete from rcib_stg_item_season;
delete from WKSP_RCIB.rcib_stg_item_image;
delete from rcib_stg_item_supplier;
delete from rcib_stg_item_supplier_cfa_ext;
delete from rcib_stg_item_supp_country;
delete from rcib_stg_item_supp_country_cfa_ext;
delete from rcib_stg_item_sc_dim;
delete from rcib_stg_item_sc_mfr;
delete from rcib_stg_item_zone_price;
delete from rcib_mfqueue_item;
*/
select * from rcib_mfqueue_regprcchg where item like '%00540%';
select * from rcib_mfqueue_clrprcchg where item like '%00540%';
select * from rcib_stg_clrprcchg where ctrl_id = 2275110;
select * from sap_slt_prod_pv1.mara where matnr = '000000196520900540';
select * from sap_slt_prod_pv1.a071 where matnr in ('000000196520900540','000000000001965209');
SELECT *
FROM sap_slt_prod_pv1.a071 a,
sap_slt_prod_pv1.konp k
WHERE 1=1
--AND a.kschl = 'YPBE' --Item Initial price PO Maybe ZBRP look chat Jochen
AND a.datbi = '99991231'
AND k.knumh = a.knumh
AND k.mandt = a.mandt
and a.matnr in ('000000000001965209')
and werks = '0428'
order by matnr
;
-----
-- mfqueue está por item/loc_type por isso necessaroio fazer join para explodir as locations
select
event_status,
COUNT(1)
from
(
select
item, hier_level, hier_Value
from
rcib_stg_itemloc_dtl
) il
INNER JOIN rcib_mfqueue_itemloc i on i.item = il.item and i.hier_level = il.hier_level
GROUP BY
event_status
ORDER BY
event_status;
create table rcib_mfqueue_item_dr2_lf as select * from rcib_mfqueue_item;
create table rcib_mfqueue_item_dr2_lf_2 as select * from rcib_mfqueue_item;
SELECT * FROM WKSP_RCIB.rcib_cfg_application;
--- PURCHASE ORDER ---
update WKSP_BJTKHVJYMHSPA.rcib_mfqueue_order set event_status = 'U', error_message = null where seq_no in (
select seq_no
from WKSP_BJTKHVJYMHSPA.rcib_mfqueue_order m,
WKSP_BJTKHVJYMHSPA.rcib_stg_order_head h
where 1=1
and error_message = 'This item is not supplied by the supplier specified for this order.'
and event_status = 'H'
and h.ctrl_id = m.ctrl_id
and h.order_no = m.order_no
--and m.order_no = 4828726235
--and supplier = 1076971502
)
;
select * from WKSP_BJTKHVJYMHSPA.rcib_mfqueue_order where order_no = 4828726235;
select m.seq_no, m.ctrl_id, m.order_no, h.supplier, d.item, m.create_Datetime, m.update_datetime
from WKSP_BJTKHVJYMHSPA.rcib_mfqueue_order m,
WKSP_BJTKHVJYMHSPA.rcib_stg_order_head h,
WKSP_BJTKHVJYMHSPA.rcib_stg_order_detail d
where 1=1
and error_message = 'This item is not supplied by the supplier specified for this order.'
and event_status = 'H'
and h.ctrl_id = m.ctrl_id
and h.ctrl_id = d.ctrl_id
and h.order_no = d.order_no
--and supplier = 2358821502
order by seq_no desc
;
select event_status, count(1) from WKSP_BJTKHVJYMHSPA.rcib_mfqueue_order m group by event_status;
update WKSP_BJTKHVJYMHSPA.rcib_mfqueue_order set event_status = 'U' where event_status = 'P';
update WKSP_BJTKHVJYMHSPA.rcib_mfqueue_order set event_status = 'U', error_message = null where seq_no = 1799877;
select * from WKSP_BJTKHVJYMHSPA.rcib_mfqueue_order where event_status = 'U' order by seq_no desc;
select * from WKSP_BJTKHVJYMHSPA.rcib_mfqueue_order where seq_no = 1799877;
select * from WKSP_BJTKHVJYMHSPA.rcib_stg_order_head where ctrl_id = 4073498;
select * from WKSP_BJTKHVJYMHSPA.rcib_stg_order_detail where ctrl_id = 4073498;
select * from dmf2.PUC_TRD_PARTY_PO_XDOC_STORE_delta where alloc_1 = 3530161105;