Source: DMF/PuC/AIF/Supplier_Merch_FUSION.sql
--SUPPLIER PARENT LEVEL
--SELECT SUPPLIER_NUMBER FROM (
SELECT DISTINCT
'1' batch_id
,'CREATE' import_action
,UPPER(xref.supplier_group_name) vendor_name
,'' vendor_name_new
,TO_NUMBER(xref.supplier_group) supplier_number
,NULL vendor_name_alt
,'Corporation' organization_type_lookup_code
,'RETAIL' vendor_type_lookup_code
,NULL end_date_active
,'SPEND_AUTHORIZED' business_relationship
,NULL parent_supplier_name
,NULL alias
,NULL duns_number
,NULL one_time_supplier
,TO_NUMBER(xref.supplier_group) customer_num
,NULL tax_country_code
,NULL num_1099
,NULL federal_reportable_flag
,NULL type_1099
,NULL allow_awt_flag
,NULL awt_group_name
,NULL vat_registration_num
,NULL auto_tax_calc_override
FROM XREF_SUPS xref
WHERE XREF.ORACLE_ID <> '999998'
AND XREF.sap_merge_id = XREF.sap_id
AND xref.supplier_group = 1120
-- )
-- GROUP BY SUPPLIER_NUMBER HAVING COUNT(1) > 1
;
-- SUPPLIER (PROFILE LEVEL )
select num_1099 from (
SELECT DISTINCT
'1' batch_id
,'CREATE' import_action
,UPPER(lfa.name1) || ' - ' || TO_NUMBER(lfa.LIFNR) vendor_name
,'' vendor_name_new
,TO_NUMBER(lfa.LIFNR) supplier_number
,lfa.name3 vendor_name_alt
,'Corporation' organization_type_lookup_code
,'RETAIL' vendor_type_lookup_code
,NULL end_date_active
,'SPEND_AUTHORIZED' business_relationship
,upper(xref.supplier_group_name) parent_supplier_name
,NULL alias
,NULL duns_number
,NULL one_time_supplier
,TO_NUMBER(lfa.LIFNR) customer_num
,DECODE(lfa.stceg, ' ', lfa.land1, SUBSTR(lfa.stceg, 1, 2)) tax_country_code
,lfa.stceg num_1099
,NULL federal_reportable_flag
,NULL type_1099
,NULL allow_awt_flag
,NULL awt_group_name
,NULL vat_registration_num
,NULL auto_tax_calc_override
FROM SAP_SLT_PROD_PV1.LFA1 lfa
,(SELECT mandt, lifnr, loevm, sperm, waers, zterm, inco1, vendor_rma_req,ekorg
FROM SAP_SLT_PROD_PV1.LFM1
) lfm
,XREF_SUPS xref
WHERE lfa.ktokk IN ('LIEF', 'ZLIF','LIFB','WLIF')
AND lfa.mandt = lfm.mandt
AND lfa.lifnr = lfm.lifnr
AND lfa.lifnr = xref.sap_merge_id
AND lfm.EKORG = XREF.sap_org_unit
AND XREF.ORACLE_ID <> '999998'
AND XREF.sap_merge_id = XREF.sap_id
UNION
SELECT DISTINCT
'1' batch_id
,'CREATE' import_action
,UPPER(lfa.name1) || ' - ' || TO_NUMBER(lfa.LIFNR) vendor_name
,'' vendor_name_new
,TO_NUMBER(lfa.LIFNR) supplier_number
,lfa.name3 vendor_name_alt
,'Corporation' organization_type_lookup_code
,'RETAIL' vendor_type_lookup_code
,NULL end_date_active
,'SPEND_AUTHORIZED' business_relationship
,UPPER(supplier_group_name) parent_supplier_name
,NULL alias
,NULL duns_number
,NULL one_time_supplier
,TO_NUMBER(lfa.LIFNR) customer_num
,DECODE(lfa.stceg, ' ', lfa.land1, SUBSTR(lfa.stceg, 1, 2)) tax_country_code
,lfa.stceg num_1099
,NULL federal_reportable_flag
,NULL type_1099
,NULL allow_awt_flag
,NULL awt_group_name
,NULL vat_registration_num
,NULL auto_tax_calc_override
FROM SAP_SLT_PROD_PR1.LFA1 lfa
,(SELECT mandt, lifnr, loevm, sperm, waers, zterm, inco1, vendor_rma_req,ekorg
FROM SAP_SLT_PROD_PR1.LFM1
) lfm
,XREF_SUPS xref
WHERE lfa.ktokk IN ('LIEF', 'ZLIF','LIFB','WLIF')
AND lfa.mandt = lfm.mandt
AND lfa.lifnr = lfm.lifnr
AND lfa.lifnr = xref.sap_merge_id
AND lfm.EKORG = XREF.sap_org_unit
AND XREF.ORACLE_ID <> '999998'
AND XREF.sap_merge_id = XREF.sap_id
) group by num_1099 having count(1)>1;
-- SUPPLIER ADDRESS (PARENT)
SELECT DISTINCT
'1' batch_id
,'CREATE' import_action
,UPPER(lfa.name1) || ' - ' || TO_NUMBER(lfa.LIFNR) vendor_name
,lfa.adrnr || ' - ' || UPPER(lfa.ort01) party_site_name
,lfa.land1 country
,lfa.stras address_line_1
,lfa.ort01 city
,NULL state
,lfa.pstlz postal_code
,lfa.telf1 phone
,lfa.telfx fax
,'N' rfq_or_bidding_purpose_flag
,'Y' ordering_purpose_flag
,'Y' remit_to_purpose_flag
,adrmail.smtp_addr email_address
,NULL delivery_channel_code
,'EMAILPDF' remit_advice_delivery_method
,adrmail.smtp_addr remittance_email
,NULL remit_advice_fax
FROM SAP_SLT_PROD_PV1.LFA1 lfa
,SAP_SLT_PROD_PV1.ADRC adrc
,(SELECT addrnumber
,max(smtp_addr) keep (dense_rank first order by rownum) as smtp_addr
FROM SAP_SLT_PROD_PV1.ADR6
WHERE flgdefault = 'X'
GROUP BY addrnumber) adrmail
,XREF_SUPS xref
WHERE lfa.adrnr = adrc.addrnumber
AND adrc.addrnumber = adrmail.addrnumber (+)
AND lfa.ktokk IN ('LIEF', 'ZLIF','LIFB','WLIF')
AND lfa.lifnr = xref.sap_merge_id
AND XREF.ORACLE_ID <> '999998'
AND XREF.sap_merge_id = XREF.sap_id
--and XREF.sap_id = '0000235716'
;
select * from XREF_org_unit where environment = 'TEST';
-- SUPPLIER SITE
SELECT distinct
'1' batch_id
,'CREATE' import_action
,UPPER(lfa.name1) || ' - ' || TO_NUMBER(lfa.LIFNR) vendor_name
,bu.ORACLE_ORG_UNIT_DESC procurement_business_unit_name
,lfa.adrnr || ' - ' || UPPER(lfa.ort01) party_site_name
,substr(upper(lfa.name1),1,15) vendor_site_code --will be changed by format given by PUC
,'N' rfq_only_site_flag
,'Y' purchasing_site_flag
,'Y' pay_site_flag
,'Y' primary_pay_site_flag
,upper(lfa.name1) || ' - ' || xref.oracle_org_unit vendor_site_code_alt
,NULL tax_reporting_site_flag
,CASE
WHEN adrmail.smtp_addr IS NOT NULL
THEN 'EMAIL'
WHEN adrmail.smtp_addr IS NULL AND lfa.telfx != ' '
THEN 'FAX'
ELSE 'PRINT'
END supplier_notif_method
,TRIM('''' FROM adrmail.smtp_addr) email_address
,CASE
WHEN adrmail.smtp_addr IS NULL AND lfa.telfx != ' '
THEN lfa.telfx
ELSE NULL
END fax
,lfm.waers invoice_currency_code
,lfm.waers payment_currency_code --will probably be changed
,CASE
WHEN lfm.waers = 'EUR'
THEN 'ICCOST_EUR'
ELSE 'ICCOST_FX'
END pay_group_lookup_code
,CASE
WHEN lfm.zterm IN (SELECT xref.puc_terms FROM XREF_TERMS xref, TERMS_HEAD_CTRL th where xref.mfcs_desc = th.terms_desc)
THEN (SELECT th.external_ref_id FROM XREF_TERMS xref, TERMS_HEAD_CTRL th WHERE xref.mfcs_desc = th.terms_desc AND lfm.zterm = xref.puc_terms)
ELSE 'End of Month'
END terms_name
,'Invoice' terms_date_basis
,'DISCOUNT' pay_date_basis_lookup_code
,'Y' always_take_disc_flag
,'PUC_MANUAL_PAYMENT' payment_method_lookup_code
,NULL delivery_channel_code
,NULL payment_text_message1
,NULL payment_reason_code
,NULL payment_reason_coments
,CASE
WHEN adrmail.smtp_addr IS NOT NULL
THEN 'EMAILPDF'
WHEN adrmail.smtp_addr IS NULL AND lfa.telfx != ' '
THEN 'FAX'
ELSE 'PRINTED'
END remit_advice_delivery_method
,TRIM('''' FROM adrmail.smtp_addr) remittance_email
,CASE
WHEN adrmail.smtp_addr IS NULL AND lfa.telfx != ' '
THEN lfa.telfx
ELSE NULL
END remittance_fax
,lfa.lifnr attribute1
,'N' exclusive_payment_flag
FROM SAP_SLT_PROD_PV1.LFA1 lfa
,SAP_SLT_PROD_PV1.LFM1 lfm
,XREF_SUPS xref
,XREF_ORG_UNIT bu
,(SELECT addrnumber
,max(smtp_addr) keep (dense_rank first order by rownum) as smtp_addr
FROM SAP_SLT_PROD_PV1.ADR6
WHERE flgdefault = 'X'
GROUP BY addrnumber) adrmail
WHERE lfa.mandt = lfm.mandt
AND lfa.lifnr = lfm.lifnr
AND lfa.lifnr = xref.sap_merge_id
AND lfm.EKORG = XREF.sap_org_unit
AND lfa.adrnr = adrmail.addrnumber (+)
AND lfa.ktokk IN ('LIEF', 'ZLIF','LIFB','WLIF')
AND XREF.ORACLE_ID <> '999998'
AND lfm.ekorg = bu.SAP_ORG_UNIT_ID
AND bu.environment = 'TEST'
union
SELECT distinct
'1' batch_id
,'CREATE' import_action
,UPPER(lfa.name1) || ' - ' || TO_NUMBER(lfa.LIFNR) vendor_name
,bu.ORACLE_ORG_UNIT_DESC procurement_business_unit_name
,lfa.adrnr || ' - ' || UPPER(lfa.ort01) party_site_name
,substr(upper(lfa.name1),1,15) vendor_site_code --will be changed by format given by PUC
,'N' rfq_only_site_flag
,'Y' purchasing_site_flag
,'Y' pay_site_flag
,'Y' primary_pay_site_flag
,upper(lfa.name1) || ' - ' || xref.oracle_org_unit vendor_site_code_alt
,NULL tax_reporting_site_flag
,CASE
WHEN adrmail.smtp_addr IS NOT NULL
THEN 'EMAIL'
WHEN adrmail.smtp_addr IS NULL AND lfa.telfx != ' '
THEN 'FAX'
ELSE 'PRINT'
END supplier_notif_method
,TRIM('''' FROM adrmail.smtp_addr) email_address
,CASE
WHEN adrmail.smtp_addr IS NULL AND lfa.telfx != ' '
THEN lfa.telfx
ELSE NULL
END fax
,lfm.waers invoice_currency_code
,lfm.waers payment_currency_code --will probably be changed
,CASE
WHEN lfm.waers = 'EUR'
THEN 'ICCOST_EUR'
ELSE 'ICCOST_FX'
END pay_group_lookup_code
,CASE
WHEN lfm.zterm IN (SELECT xref.puc_terms FROM XREF_TERMS xref, TERMS_HEAD_CTRL th where xref.mfcs_desc = th.terms_desc)
THEN (SELECT th.external_ref_id FROM XREF_TERMS xref, TERMS_HEAD_CTRL th WHERE xref.mfcs_desc = th.terms_desc AND lfm.zterm = xref.puc_terms)
ELSE 'End of Month'
END terms_name
,'Invoice' terms_date_basis
,'DISCOUNT' pay_date_basis_lookup_code
,'Y' always_take_disc_flag
,'PUC_MANUAL_PAYMENT' payment_method_lookup_code
,NULL delivery_channel_code
,NULL payment_text_message1
,NULL payment_reason_code
,NULL payment_reason_coments
,CASE
WHEN adrmail.smtp_addr IS NOT NULL
THEN 'EMAILPDF'
WHEN adrmail.smtp_addr IS NULL AND lfa.telfx != ' '
THEN 'FAX'
ELSE 'PRINTED'
END remit_advice_delivery_method
,TRIM('''' FROM adrmail.smtp_addr) remittance_email
,CASE
WHEN adrmail.smtp_addr IS NULL AND lfa.telfx != ' '
THEN lfa.telfx
ELSE NULL
END remittance_fax
,lfa.lifnr attribute1
,'N' exclusive_payment_flag
FROM SAP_SLT_PROD_Pr1.LFA1 lfa
,SAP_SLT_PROD_Pr1.LFM1 lfm
,XREF_SUPS xref
,XREF_ORG_UNIT bu
,(SELECT addrnumber
,max(smtp_addr) keep (dense_rank first order by rownum) as smtp_addr
FROM SAP_SLT_PROD_PR1.ADR6
WHERE flgdefault = 'X'
GROUP BY addrnumber) adrmail
WHERE lfa.mandt = lfm.mandt
AND lfa.lifnr = lfm.lifnr
AND lfa.lifnr = xref.sap_merge_id
AND lfm.EKORG = XREF.sap_org_unit
AND lfa.adrnr = adrmail.addrnumber (+)
AND lfa.ktokk IN ('LIEF', 'ZLIF','LIFB','WLIF')
AND XREF.ORACLE_ID <> '999998'
AND XREF.sap_merge_id = XREF.sap_id
AND lfm.ekorg = bu.SAP_ORG_UNIT_ID
AND bu.environment = 'TEST'
;
-- supplier assignment
SELECT distinct
'1' batch_id
,'CREATE' import_action
,UPPER(lfa.name1) || ' - ' || TO_NUMBER(lfa.LIFNR) vendor_name
,substr(upper(lfa.name1),1,15) vendor_site_code --will be changed by format given by PUC
,bu.ORACLE_ORG_UNIT_DESC procurement_business_unit_name
,bu.ORACLE_ORG_UNIT_DESC business_unit_name
,bu.ORACLE_ORG_UNIT_DESC bill_to_bu_name
,bu.ORACLE_SHIP_TO_LOC ship_to_location_code
,bu.ORACLE_SHIP_TO_LOC bill_to_location_code
,'N' allow_awt_flag
,NULL awt_group_name
,NULL accts_pay_concat_segments
,NULL prepay_concat_segments
,NULL future_dated_concat_segments
,NULL distribution_set_name
,NULL inactive_date
FROM SAP_SLT_PROD_PV1.LFA1 lfa
,XREF_SUPS xref
,SAP_SLT_PROD_PV1.LFM1 lfm
,XREF_org_unit bu
WHERE lfa.lifnr = xref.sap_merge_id
AND lfm.EKORG = XREF.sap_org_unit
AND XREF.ORACLE_ID <> '999998'
AND XREF.sap_merge_id = XREF.sap_id
AND lfa.ktokk IN ('LIEF', 'ZLIF','LIFB','WLIF')
AND lfa.mandt = lfm.mandt
AND lfa.lifnr = lfm.lifnr
AND lfm.ekorg = bu.SAP_ORG_UNIT_ID
AND bu.environment = 'TEST'
union
SELECT distinct
'1' batch_id
,'CREATE' import_action
,UPPER(lfa.name1) || ' - ' || TO_NUMBER(lfa.LIFNR) vendor_name
,substr(upper(lfa.name1),1,15) vendor_site_code --will be changed by format given by PUC
,bu.ORACLE_ORG_UNIT_DESC procurement_business_unit_name
,bu.ORACLE_ORG_UNIT_DESC business_unit_name
,bu.ORACLE_ORG_UNIT_DESC bill_to_bu_name
,bu.ORACLE_SHIP_TO_LOC ship_to_location_code
,bu.ORACLE_SHIP_TO_LOC bill_to_location_code
,'N' allow_awt_flag
,NULL awt_group_name
,NULL accts_pay_concat_segments
,NULL prepay_concat_segments
,NULL future_dated_concat_segments
,NULL distribution_set_name
,NULL inactive_date
FROM SAP_SLT_PROD_PR1.LFA1 lfa
,XREF_SUPS xref
,SAP_SLT_PROD_PR1.LFM1 lfm
,XREF_org_unit bu
WHERE lfa.lifnr = xref.sap_merge_id
AND lfm.EKORG = XREF.sap_org_unit
AND XREF.ORACLE_ID <> '999998'
AND XREF.sap_merge_id = XREF.sap_id
AND lfa.ktokk IN ('LIEF', 'ZLIF','LIFB','WLIF')
AND lfa.mandt = lfm.mandt
AND lfa.lifnr = lfm.lifnr
AND lfm.ekorg = bu.SAP_ORG_UNIT_ID
AND bu.environment = 'TEST'
;
select * from XREF_org_unit where environment = 'TEST';