Source: DMF/PuC/AIF/Supplier_NonMerch.sql
select COUNT(1) from XREF_SUPS_NONMERCH_STG;
truncate table XREF_SUPS_NONMERCH_STG;
drop table XREF_SUPS_NONMERCH_STG;
create table XREF_SUPS_NONMERCH_STG (
id NUMBER,
OWNER_LIST VARCHAR2(50),
ACCOUNT_GROUP VARCHAR2(50),
SUPPLIER_GROUP VARCHAR2(50),
SUPPLIER_GROUP_NAME VARCHAR2(200),
SUPPLIER_NUMBER VARCHAR2(20),
SUPPLIER_NAME VARCHAR2(200),
BURK_SITE VARCHAR2(4),
PARENT_FLAG VARCHAR2(20),
SITE_FLAG VARCHAR2(20),
GLN VARCHAR2(20),
ADDRESS VARCHAR2(20),
RETURN_DEBITOR VARCHAR2(20),
SUPPLIER_INVOICE VARCHAR2(20),
ORACLE_SUPPLIER_PARENT NUMBER,
ORACLE_ORG_UNIT NUMBER,
ORACLE_SUPPLIER_SITE NUMBER,
MERGE_SUPPLIER NUMBER,
SAP_MERGE_SUPPLIER VARCHAR2(11),
tax_id varchar2(20),
status varchar2(10)
);
ALTER TABLE XREF_SUPS_NONMERCH_STG
ADD STATUS_ERROR_DESC VARCHAR2(1000);
ALTER TABLE XREF_SUPS_NONMERCH_STG
ADD ORACLE_TYPE VARCHAR2(15);
CREATE INDEX XREF_SUPS_NONMERCH_STG_IDX01 ON XREF_SUPS_NONMERCH_STG (supplier_number, BURK_SITE);
CREATE INDEX XREF_SUPS_NONMERCH_STG_IDX02 ON XREF_SUPS_NONMERCH_STG (SUPPLIER_NUMBER, ORACLE_SUPPLIER_SITE);
CREATE INDEX XREF_SUPS_NONMERCH_STG_IDX03 ON XREF_SUPS_STG (SUPPLIER_NUMBER, ORACLE_SUPPLIER_SITE);
---- AFTER INSERT
select l.stceg from SAP_SLT_PROD_P01.LFA1 l where l.lifnr IN (SELECT supplier_number FROM XREF_SUPS_NONMERCH_STG);
update XREF_SUPS_NONMERCH_STG s set tax_id = (select l.stceg from SAP_SLT_PROD_P01.LFA1 l where l.lifnr = s.supplier_number);
update XREF_SUPS_NONMERCH_STG s set supplier_name = (select l.name1 from SAP_SLT_PROD_P01.LFA1 l where l.lifnr = s.supplier_number );
update XREF_SUPS_NONMERCH_STG s set supplier_name = (select l.name1 from SAP_SLT_PROD_P01.LFA1 l where l.lifnr = s.supplier_number ) where supplier_name is null;
select * from XREF_SUPS_NONMERCH_STG;
-- delete accounting groups not necessary to be migrated
DELETE FROM XREF_SUPS_NONMERCH_STG WHERE ACCOUNT_GROUP IN ('BEST','PERS','PERT','LIFC','KGMS','VERB','GEIC');
-- delete suppliers without ekorg
DELETE FROM XREF_SUPS_NONMERCH_STG WHERE burk_site is null;
-- validar supplier sites duplicados
SELECT ORACLE_SUPPLIER_SITE, COUNT(1) FROM XREF_SUPS_NONMERCH_STG GROUP BY ORACLE_SUPPLIER_SITE HAVING COUNT(1) > 1;
SELECT SUPPLIER_NUMBER, BURK_SITE FROM XREF_SUPS_NONMERCH_STG GROUP BY SUPPLIER_NUMBER, BURK_SITE HAVING COUNT(1) > 1; --0
select * from XREF_SUPS_NONMERCH_STG where SUPPLIER_NUMBER is null;
select * from XREF_SUPS_NONMERCH_STG where SUPPLIER_NAME is null;
select * from XREF_SUPS_NONMERCH_STG where ORACLE_SUPPLIER_SITE is null AND SUPPLIER_INVOICE = 'SAME'; -- not necessary and merged
select * from XREF_SUPS_NONMERCH_STG where ORACLE_SUPPLIER_PARENT is null;
-- merge suppliers don't need the supplier site. it will not be migrated
UPDATE XREF_SUPS_NONMERCH_STG SET ORACLE_SUPPLIER_SITE = null WHERE SUPPLIER_INVOICE <> 'SAME' and ORACLE_SUPPLIER_SITE is not null; -- 0 (in case excel is wrong)
-- check merge supplier is valid as merch or non merch
SELECT *
FROM XREF_SUPS_NONMERCH_STG xref1
where SUPPLIER_INVOICE IS NOT NULL
AND NOT exists (select 1 from XREF_SUPS_NONMERCH_STG xref2 where xref1.SUPPLIER_INVOICE = TO_NUMBER(xref2.SUPPLIER_NUMBER) and xref2.ORACLE_SUPPLIER_SITE IS NOT NULL)
AND NOT exists (select 1 from XREF_SUPS_STG xref2 where xref1.SUPPLIER_INVOICE = TO_NUMBER(xref2.SUPPLIER_NUMBER) and xref2.ORACLE_SUPPLIER_SITE IS NOT NULL) order by merge_supplier
;
SELECT * FROM XREF_SUPS_STG xref1 WHERE SUPPLIER_NUMBER LIKE '%0000588904%' ;
SELECT * FROM XREF_SUPS_NONMERCH_STG xref1 WHERE SUPPLIER_NUMBER LIKE '%0000588904%' ;
-- set as error missing merge suppliers
UPDATE XREF_SUPS_NONMERCH_STG x
SET status = 'E',
status_error_desc = 'Merge Supplier not migrated as supplier'
WHERE EXISTS (
SELECT 1
FROM XREF_SUPS_NONMERCH_STG xref1
WHERE x.supplier_number = xref1.supplier_number
AND x.burk_site = xref1.burk_site
AND xref1.supplier_invoice IS NOT NULL
AND NOT EXISTS (
SELECT 1
FROM XREF_SUPS_NONMERCH_STG xref2
WHERE xref1.supplier_invoice = TO_NUMBER(xref2.supplier_number)
AND xref2.oracle_supplier_site IS NOT NULL
)
AND NOT EXISTS (
SELECT 1
FROM XREF_SUPS_STG xref2
WHERE xref1.supplier_invoice = TO_NUMBER(xref2.supplier_number)
AND xref2.oracle_supplier_site IS NOT NULL
)
);
CREATE TABLE XREF_SUPS_FUSION AS SELECT * FROM XREF_SUPS;
ALTER TABLE XREF_SUPS_FUSION ADD (SAP_TYPE VARCHAR2(4));
ALTER TABLE XREF_SUPS_FUSION ADD (ORACLE_TYPE VARCHAR2(15));
TRUNCATE TABLE XREF_SUPS_FUSION;
insert into XREF_SUPS_FUSION
SELECT SUPPLIER_NUMBER, MERGE_SUPPLIER, ORACLE_SUPPLIER_PARENT, ORACLE_ORG_UNIT,
sap_merge_supplier, BURK_SITE, SUPPLIER_GROUP, SUPPLIER_GROUP_NAME, ACCOUNT_GROUP, ORACLE_TYPE
FROM XREF_SUPS_NONMERCH_STG ;
SELECT * FROM XREF_SUPS_NONMERCH_STG;
SELECT * FROM XREF_SUPS_FUSION;
SELECT * FROM XREF_ORG_UNIT;
---
select count(1) from (
SELECT distinct supplier_name, supplier_number FROM XREF_SUPS_NONMERCH_STG GROUP BY supplier_name, supplier_number
); -- 7810
SELECT COUNT(1) FROM (
select distinct TO_NUMBER(supplier_number) as supplier, UPPER(supplier_name) from XREF_SUPS_NONMERCH_STG where Oracle_supplier_site is not null order by supplier
-- 7362
);
SELECT COUNT(1) FROM (
select distinct TO_NUMBER(sap_merge_id), sap_org_unit as supplier from XREF_SUPS_FUSION where oracle_id is not null order by supplier
); -- 11460
SELECT COUNT(1) FROM (
select distinct TO_NUMBER(MERGE_SUPPLIER) as supplier from XREF_SUPS_NONMERCH_STG where Oracle_supplier_site is not null order by supplier
); -- 11030
SELECT COUNT(1) FROM (
select distinct TO_NUMBER(ORACLE_ID) as supplier from XREF_SUPS_FUSION where oracle_id is not null order by supplier -- 871
); -- 11394
SELECT * FROM XREF_SUPS_FUSION;
select *
FROM SAP_SLT_PROD_P01.LFA1 lfa
,(SELECT mandt, lifnr, loevm, sperm, waers, zterm, inco1, vendor_rma_req,ekorg
FROM SAP_SLT_PROD_P01.LFM1
) lfm
where 1=1
AND lfa.mandt = lfm.mandt
AND lfa.lifnr = lfm.lifnr
and lfa.lifnr = '0000576930';
select distinct ekorg from SAP_SLT_PROD_P01.LFM1;
select * from SAP_SLT_PROD_Pv1.ZBES_POM_EH_UEF;