Source: DMF/PuC/AIF/Supplier_Merch.sql
truncate table XREF_SUPS_STG;
drop table XREF_SUPS_STG;
create table XREF_SUPS_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(10),
tax_id varchar2(20)
);
---- AFTER INSERT
update XREF_SUPS_STG s set tax_id = (select l.stceg from SAP_SLT_PROD_PV1.LFA1 l where l.lifnr = s.supplier_number);
update XREF_SUPS_STG s set supplier_name = (select l.name1 from SAP_SLT_PROD_PV1.LFA1 l where l.lifnr = s.supplier_number );
update XREF_SUPS_STG s set supplier_name = (select l.name1 from SAP_SLT_PROD_PR1.LFA1 l where l.lifnr = s.supplier_number ) where supplier_name is null;
-- delete accounting groups not necessary to be migrated
DELETE FROM XREF_SUPS_STG WHERE ACCOUNT_GROUP IN ('BEST','PERS','PERT','LIFC','KGMS','VERB','GEIC');
-- validar supplier sites duplicados
SELECT ORACLE_SUPPLIER_SITE FROM XREF_SUPS_STG GROUP BY ORACLE_SUPPLIER_SITE HAVING COUNT(1) > 1;
select * from XREF_SUPS_STG where ORACLE_SUPPLIER_SITE = 1076971004;
/*1076971004
2376011004
2376011502
1076971502
*/
DELETE FROM XREF_SUPS_STG WHERE id IN (101,102,1043,1044);
SELECT SUPPLIER_NUMBER, BURK_SITE FROM XREF_SUPS_STG GROUP BY SUPPLIER_NUMBER, BURK_SITE HAVING COUNT(1) > 1; --0
select * from XREF_SUPS_STG where SUPPLIER_NUMBER is null;
select * from XREF_SUPS_STG where SUPPLIER_NAME is null;
select * from XREF_SUPS_STG where ORACLE_SUPPLIER_SITE is null AND SUPPLIER_INVOICE = 'SAME'; -- not necessary and merged
select * from XREF_SUPS_STG where ORACLE_SUPPLIER_PARENT is null;
--validate different supplier group names
SELECT *
FROM XREF_SUPS_STG xref1
where exists (select 1 from XREF_SUPS_STG xref2 where xref1.supplier_group = xref2.supplier_group and xref1.supplier_group_name <> xref2.supplier_group_name) order by supplier_group;
/*
update XREF_SUPS_STG set supplier_group_name = 'AYTEX Textile' where supplier_group = '560';
update XREF_SUPS_STG set supplier_group_name = 'VAN LAACK' where supplier_group = '287';
update XREF_SUPS_STG set SUPPLIER_GROUP = '1287', SUPPLIER_GROUP_NAME = 'Play Hard GmbH' where supplier_number = '0000237575';
update XREF_SUPS_STG set ACCOUNT_GROUP = 'LIFB' where supplier_number = '0000199990';
*/
-- merge suppliers don't need the supplier site. it will not be migrated
UPDATE XREF_SUPS_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
SELECT *
FROM XREF_SUPS_STG xref1
where SUPPLIER_INVOICE <> 'SAME'
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 KTOKK, ZZLIEGRUPPE, NULL, L.LIFNR, NAME1, EKORG,'x','x', L.bbbnr || L.bbsnr || L.bubkz AS GLN, NULL,KUNNR,'SAME', l.*
from SAP_SLT_PROD_Pv1.LFA1 l,
SAP_SLT_PROD_Pv1.LFm1 l2
where L.LIFNR = L2.LIFNR
AND l.lifnr IN (/*'0000139998',
'0000237551',
'0000237689',
'0000237691',
'0000237771'*/
'0000236352',
'0000236381',
'0000236383',
'0000236605',
'0000236623',
'0000237391'
)
ORDER BY L.LIFNR;
-- CHECK NUMBER OF SITES
SELECT COUNT(1), 'xref_new' FROM (
select distinct TO_NUMBER(MERGE_SUPPLIER) as supplier from XREF_SUPS_STG where Oracle_supplier_site is not null order by supplier
)
UNION
SELECT COUNT(1), 'xref_final' FROM (
select distinct TO_NUMBER(ORACLE_ID) as supplier from XREF_SUPS where oracle_id is not null order by supplier -- 871
);
SELECT * FROM XREF_SUPS_STG WHERE SUPPLIER_NUMBER = '0000236976';
UPDATE XREF_SUPS_STG SET SUPPLIER_INVOICE = 205630, ORACLE_SUPPLIER_PARENT = 205630, MERGE_SUPPLIER =2056301502, SAP_MERGE_SUPPLIER = '0000205630' WHERE SUPPLIER_NUMBER = '0000236976' AND BURK_SITE = '0251';
SELECT * FROM XREF_SUPS WHERE SAP_ID = '0000236976';
UPDATE XREF_SUPS SET ORACLE_ID = 2056301502, ORACLE_PARENT_ID = 205630, SAP_MERGE_ID = '0000205630' WHERE SAP_ID = '0000236976' AND SAP_ORG_UNIT = '0251';
-- non-merch supplier
--UPDATE XREF_SUPS_STG SET SUPPLIER_GROUP = 1411, SUPPLIER_GROUP_NAME = SUPPLIER_NAME where ID = 12498;
SELECT * FROM XREF_SUPS_STG WHERE SUPPLIER_GROUP_NAME IS NULL;
UPDATE XREF_SUPS_STG SET SUPPLIER_GROUP_NAME = SUPPLIER_NAME WHERE SUPPLIER_GROUP_NAME IS NULL;
ALTER TABLE XREF_SUPS ADD ( ORACLE_ORG_UNIT NUMBER);
ALTER TABLE XREF_SUPS ADD ( SAP_MERGE_ID VARCHAR2(10));
ALTER TABLE XREF_SUPS ADD ( SUPPLIER_GROUP VARCHAR2(50));
ALTER TABLE XREF_SUPS ADD ( SUPPLIER_GROUP_NAME VARCHAR2(200));
ALTER TABLE XREF_SUPS ADD CONSTRAINT UK_SUPPLIER_SITE UNIQUE (SAP_ID, ORACLE_ID,SAP_ORG_UNIT,ORACLE_ORG_UNIT);
TRUNCATE TABLE XREF_SUPS;
insert into XREF_SUPS SELECT SUPPLIER_NUMBER, MERGE_SUPPLIER, ORACLE_SUPPLIER_PARENT, ORACLE_ORG_UNIT, sap_merge_supplier, BURK_SITE, SUPPLIER_GROUP, SUPPLIER_GROUP_NAME FROM XREF_SUPS_STG ;
SELECT * FROM XREF_SUPS_STG;
SELECT * FROM XREF_SUPS;
SELECT * FROM XREF_ORG_UNIT;
---
update XREF_SUPS x set (SUPPLIER_GROUP,SUPPLIER_GROUP_NAME) = (select SUPPLIER_GROUP, SUPPLIER_GROUP_NAME from XREF_SUPS_stg stg where x.sap_id = stg.supplier_number and x.sap_org_unit = stg.BURK_SITE);
select * from XREF_SUPS where sap_merge_id = '0000221104';
select * from XREF_SUPS_stg where upper(supplier_name) like '%CECEBA GROUP GMBH%';
select distinct currency_code from sups_ctrl;
select count(1) from (
SELECT distinct supplier_name, supplier_number FROM XREF_SUPS_STG GROUP BY supplier_name, supplier_number ) ;
SELECT COUNT(1) FROM (
select distinct TO_NUMBER(supplier_group) as supplier from XREF_SUPS_STG where Oracle_supplier_site is not null order by supplier_group -- 669
) ;
SELECT COUNT(1) FROM (
select distinct TO_NUMBER(supplier_number) as supplier, UPPER(supplier_name) from XREF_SUPS_STG where Oracle_supplier_site is not null order by supplier -- 784
);
select distinct TO_NUMBER(sap_merge_id) as supplier from XREF_SUPS where oracle_id is not null order by supplier; -- 871
SELECT COUNT(1) FROM (
select distinct TO_NUMBER(MERGE_SUPPLIER) as supplier from XREF_SUPS_STG where Oracle_supplier_site is not null order by supplier
); -- 1408
SELECT COUNT(1) FROM (
select distinct TO_NUMBER(ORACLE_ID) as supplier from XREF_SUPS where oracle_id is not null order by supplier -- 871
); -- 1408
select * from XREF_BUSINESS_UNITS where environment = 'TEST';
SELECT DISTINCT SAP_ORG_UNIT FROM XREF_SUPS;
SELECT * FROM XREF_ORG_UNIT
--- populate XREF_SUPS
select * from XREF_SUPS WHERE ORACLE_ID = 999998;
insert into XREF_SUPS
select distinct l.lifnr,'999998','1000999998',1004, l.lifnr, EKORG
from SAP_SLT_PROD_Pv1.LFA1 l,
SAP_SLT_PROD_Pv1.LFm1 l2
where L.LIFNR = L2.LIFNR
AND l.lifnr IN ('0000236538',
'0000217352',
'0000236964',
'0000236486',
'0000237074',
'0000236151',
'0000231150',
'0000236959',
'0000227544',
'0000218600',
'0000237202',
'0000237543',
'0000185736',
'0000235883',
'0000237034',
'0000179280',
'0000235679',
'0000210668',
'0000236815',
'0000237361',
'0000237356',
'0000197211',
'0000221384',
'0000236539',
'0000221503',
'0000236773',
'0000236630',
'0000216445',
'0000225304',
'0000236866',
'0000237444',
'0000237072',
'0000222518',
'0000151092',
'0000236109',
'0000236792',
'0000110795',
'0000236306',
'0000236442',
'0000236046',
'0000230898',
'0000236687',
'0000237267',
'0000237123',
'0000237471',
'0000228940',
'0000218715',
'0000174955',
'0000236837',
'0000236610',
'0000237023',
'0000236890',
'0000235579',
'0000235558',
'0000216771',
'0000235902',
'0000237486',
'0000182222',
'0000215520',
'0000216828',
'0000237036',
'0000237343',
'0000236899',
'0000237095',
'0000236749',
'0000210234',
'0000237334',
'0000237331',
'0000237350',
'0000237495',
'0000237550',
'0000235486',
'0000237248',
'0000235541',
'0000225541',
'0000236738',
'0000237464',
'0000184128',
'0000237359',
'0000237308',
'0000236095',
'0000209570',
'0000235619',
'0000236615',
'0000237458',
'0000237383',
'0000175404',
'0000235566',
'0000219142',
'0000217840',
'0000237260',
'0000223522',
'0000236761',
'0000217271',
'0000236763',
'0000105651',
'0000237093',
'0000236506',
'0000236979',
'0000236146',
'0000237053',
'0000236552',
'0000214507',
'0000237455',
'0000237463',
'0000236779',
'0000236752',
'0000236683',
'0000211311',
'0000225193',
'0000236541',
'0000236226',
'0000237295',
'0000236838',
'0000236572',
'0000235887',
'0000237265',
'0000221813',
'0000101435',
'0000235969',
'0000237557',
'0000236628',
'0000221066',
'0000237165',
'0000232963',
'0000237276',
'0000102091',
'0000237193',
'0000221872',
'0000235197',
'0000237322',
'0000205125',
'0000224758',
'0000236912',
'0000236844',
'0000237282',
'0000190187',
'0000236911',
'0000210439',
'0000147966',
'0000236903',
'0000236694',
'0000237297',
'0000236967',
'0000237122',
'0000235510',
'0000183121',
'0000236953',
'0000235589',
'0000221546',
'0000237303',
'0000182630',
'0000236926',
'0000115940',
'0000237511',
'0000236971',
'0000237292',
'0000236994',
'0000236507',
'0000204340',
'0000237385',
'0000237197',
'0000235584',
'0000236988',
'0000237040',
'0000237236',
'0000204919',
'0000236941',
'0000236645',
'0000235230',
'0000220922',
'0000237382',
'0000235907',
'0000235864',
'0000237217',
'0000223239',
'0000236814',
'0000236801',
'0000235101',
'0000236797',
'0000235514',
'0000236287',
'0000212270',
'0000237096',
'0000237568',
'0000237030',
'0000237170',
'0000237405',
'0000220710',
'0000209198',
'0000236820',
'0000236786',
'0000235640',
'0000235765',
'0000230391',
'0000237333',
'0000237077',
'0000232971',
'0000235637',
'0000237445',
'0000236172',
'0000151009',
'0000235281',
'0000236948',
'0000205060',
'0000237209',
'0000197157',
'0000235961',
'0000236918',
'0000191469',
'0000208868',
'0000236753',
'0000236960',
'0000236180',
'0000236370',
'0000220540',
'0000235665',
'0000103349',
'0000237194',
'0000237004',
'0000111007',
'0000235445',
'0000221899',
'0000184284',
'0000235585',
'0000216860',
'0000237667',
'0000220108',
'0000232670',
'0000236978',
'0000162086',
'0000236514',
'0000236846',
'0000209163',
'0000236788',
'0000236110',
'0000236660',
'0000236928',
'0000224367',
'0000235526',
'0000229695',
'0000236148',
'0000236827',
'0000229440',
'0000236548',
'0000237396',
'0000235889',
'0000237099',
'0000237357',
'0000237113',
'0000223956',
'0000237280',
'0000235769',
'0000230910',
'0000236719',
'0000237291',
'0000228109',
'0000236775',
'0000237200',
'0000236369',
'0000236901',
'0000237241',
'0000237314',
'0000208698',
'0000236725',
'0000236298',
'0000235760',
'0000216232',
'0000223131',
'0000108014',
'0000220850',
'0000237017',
'0000237009',
'0000236845',
'0000237311',
'0000237545',
'0000236366',
'0000236086',
'0000236766',
'0000236740',
'0000236236',
'0000236590',
'0000237474',
'0000233650',
'0000236139',
'0000229954',
'0000236834',
'0000222984',
'0000235447',
'0000237392',
'0000237127',
'0000221090',
'0000107093',
'0000237299',
'0000237472',
'0000236321',
'0000236811',
'0000235999',
'0000235163',
'0000237228',
'0000237108',
'0000236762',
'0000206024',
'0000235556',
'0000106160',
'0000237348',
'0000237261',
'0000237411',
'0000235996',
'0000208990',
'0000187488',
'0000124648',
'0000236391',
'0000237073',
'0000237332',
'0000237163',
'0000236816',
'0000224740',
'0000237070',
'0000237376',
'0000235617',
'0000236700',
'0000237097',
'0000237029',
'0000191698',
'0000237320',
'0000229830',
'0000236021',
'0000237114',
'0000237184',
'0000235669',
'0000236990',
'0000236341',
'0000128309',
'0000236980',
'0000237048',
'0000183717',
'0000237012',
'0000236160',
'0000223670',
'0000158968',
'0000236295',
'0000236804',
'0000219681',
'0000211001',
'0000233064',
'0000237033',
'0000236684',
'0000236904',
'0000210102',
'0000237387',
'0000190098',
'0000236833',
'0000182397',
'0000203440',
'0000236377',
'0000236789',
'0000190594',
'0000235711',
'0000224529',
'0000236747',
'0000204293',
'0000236415',
'0000237542',
'0000229431',
'0000237421',
'0000235821',
'0000235803',
'0000236803',
'0000202584',
'0000218065',
'0000235662',
'0000236876',
'0000236296',
'0000237877',
'0000236237',
'0000236477',
'0000222020',
'0000236117',
'0000186465',
'0000235896',
'0000236916',
'0000235448',
'0000235686',
'0000236077',
'0000235827',
'0000235630',
'0000235986',
'0000237342',
'0000237473',
'0000237446',
'0000236079',
'0000181544',
'0000207853',
'0000237285',
'0000228117',
'0000237337',
'0000236414',
'0000191973',
'0000235810',
'0000235246',
'0000236674',
'0000235976',
'0000237298',
'0000104060',
'0000237078',
'0000237317',
'0000236956',
'0000223891',
'0000228559',
'0000110493',
'0000236285',
'0000236292',
'0000235324',
'0000217921',
'0000237143',
'0000230464',
'0000209996',
'0000236737',
'0000236115',
'0000236880',
'0000214426',
'0000237325',
'0000228532',
'0000237477',
'0000237487',
'0000237118',
'0000235608',
'0000223298',
'0000214418',
'0000197505',
'0000222313',
'0000173444',
'0000235519',
'0000236986',
'0000236839',
'0000237246',
'0000236290',
'0000236824',
'0000206768',
'0000130460',
'0000236757',
'0000222925',
'0000187550',
'0000229121',
'0000236693',
'0000236238',
'0000237294',
'0000237247',
'0000184268',
'0000227587',
'0000236968',
'0000236288',
'0000220094',
'0000237408',
'0000237080',
'0000224006',
'0000175056',
'0000235635',
'0000237062',
'0000217433',
'0000220329',
'0000235356',
'0000210382',
'0000236093',
'0000231096',
'0000237211',
'0000237587',
'0000205583',
'0000236921',
'0000237329',
'0000237065',
'0000236136',
'0000237483',
'0000229369'
)
ORDER BY L.LIFNR;
----------------
TRUNCATE TABLE sups_ctrl;
TRUNCATE TABLE addr_ctrl;
TRUNCATE TABLE SUPS_STG;
TRUNCATE TABLE ADDR_STG;
select count(1) from sups_ctrl where ctrl_status <> 'A';
select count(1) from addr_ctrl where ctrl_status <> 'A';
select count(1) from SUPS_STG;
select count(1) from ADDR_STG;
select * from sups_ctrl where supplier_parent in (1000999998);
select * from sups_ctrl where supplier in (2361631004);
select * from addr_ctrl where key_value_1 in (2361631004,2356661502);
select * from SUPS_STG;
select * from ADDR_STG;
update sups_STG set ctrl_status = 'N';
update sups_ctrl set ctrl_status = 'A' where ctrl_status = 'C';
update addr_ctrl set ctrl_status = 'A' where ctrl_status = 'C';
update sups_ctrl set contact_phone = '0' where contact_phone is null;
SELECT * FROM MIGR_ERROR ORDER BY ERROR_ID DESC;
SELECT * FROM MIGR_RULES WHERE RULE_ID = 1975;
select * from migr_rule_control where table_name = 'ADDR' AND error_ind = 'Y';
--------------------
truncate table PARTNER_ORG_UNIT_STG;
truncate table PARTNER_ORG_UNIT_ctrl;
select * from PARTNER_ORG_UNIT_STG;
select * from PARTNER_ORG_UNIT_ctrl;
SELECT distinct
xref.oracle_id partner
,xref.oracle_org_unit org_unit_id
,'U' partner_type
,NULL primary_pay_site
,'DMF' create_id
,SYSDATE create_datetime
,'N' ctrl_status
FROM XREF_SUPS xref
UNION
SELECT distinct
999999 partner
,1004 org_unit_id
,'U' partner_type
,NULL primary_pay_site
,'DMF' create_id
,SYSDATE create_datetime
,'N' ctrl_status
FROM dual;
select * from sap_slt_prod_pr1.ZSTA_VENDOR_CERT;
select * from sap_slt_prod_pv1.ZSTA_VENDOR_CER2;
select WSTAW, M.* from sap_slt_prod_pr1.MAW1 M;
select WSTAW, M.* from sap_slt_prod_pv1.MAW1 M;