Source: DMF/PuC/AIF/Supplier_NonMerch_Fusion.sql
-- SUPPLIER (PROFILE LEVEL )
select * from (
SELECT DISTINCT
'2' 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
,xref.oracle_type 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(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_P01.LFA1 lfa
/*,(SELECT mandt, lifnr, loevm, sperm, waers, zterm, inco1, vendor_rma_req,ekorg
FROM SAP_SLT_PROD_Pv1.LFM1
) lfm*/
,XREF_SUPS_fusion xref
WHERE 1=1
--AND lfa.mandt = lfm.mandt
--AND lfa.lifnr = lfm.lifnr
AND lfa.ktokk = xref.sap_type
AND lfa.lifnr = xref.sap_merge_id
--AND lfm.EKORG = XREF.sap_org_unit
AND XREF.sap_merge_id = XREF.sap_id
)
--group by num_1099 having count(1) > 1
where tax_country_code = 'EL'
;
-- SUPPLIER ADDRESS (PARENT)
SELECT DISTINCT
'2' 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_P01.LFA1 lfa
,SAP_SLT_PROD_P01.ADRC adrc
,(SELECT addrnumber
,max(smtp_addr) keep (dense_rank first order by rownum) as smtp_addr
FROM SAP_SLT_PROD_P01.ADR6
WHERE flgdefault = 'X'
GROUP BY addrnumber) adrmail
,XREF_SUPS_fusion xref
WHERE lfa.adrnr = adrc.addrnumber
AND adrc.addrnumber = adrmail.addrnumber (+)
AND lfa.ktokk = xref.sap_type
AND lfa.lifnr = xref.sap_merge_id
AND XREF.sap_merge_id = XREF.sap_id
;
-- SUPPLIER SITE
SELECT distinct
'2' 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
,case
when lfm.waers is null then 'EUR'
when lfm.waers = ' ' then 'EUR'
else lfm.waers end invoice_currency_code
,case
when lfm.waers is null then 'EUR'
when lfm.waers = ' ' then 'EUR'
else lfm.waers end 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_P01.LFA1 lfa
--,SAP_SLT_PROD_P01.LFM1 lfm
,(
SELECT mandt, lifnr, waers, zterm
FROM (
SELECT lfm.*,
ROW_NUMBER() OVER (
PARTITION BY lifnr
ORDER BY ekorg
) rn
FROM SAP_SLT_PROD_P01.LFM1 lfm
)
WHERE rn = 1
) lfm
,XREF_SUPS_fusion xref
,XREF_ORG_UNIT bu
,(SELECT addrnumber
,max(smtp_addr) keep (dense_rank first order by rownum) as smtp_addr
FROM SAP_SLT_PROD_P01.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 = xref.sap_type
AND XREF.sap_org_unit = bu.SAP_ORG_UNIT_ID
AND bu.environment = 'TEST'
;
-- supplier assignment
SELECT distinct
'2' 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_P01.LFA1 lfa
,XREF_SUPS_fusion 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.sap_merge_id = XREF.sap_id
AND lfa.ktokk = xref.sap_type
--AND lfa.mandt = lfm.mandt
--AND lfa.lifnr = lfm.lifnr
AND XREF.sap_org_unit = bu.SAP_ORG_UNIT_ID
AND bu.environment = 'TEST'
;
CREATE OR REPLACE VIEW V_SUPPLIER_BANK AS
SELECT
*
FROM
(
--p01
select
distinct UPPER(lfa.name1) || ' - ' || TO_NUMBER(lfa.LIFNR) vendor_name,
TO_NUMBER(lfa.LIFNR) supplier_number,
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,
LFBK.BANKS Bank_Country,
BNKA.BANKA Bank_Name,
BNKA.BNKLZ Bank_number,
LFBK.BANKL Bank_Key,
BNKA.BRNCH Bank_Branch,
BNKA.SWIFT SWIFT_BIC,
LFBK.BANKN Bank_Account,
BNKA.PROVZ Region,
BNKA.STRAS Street,
BNKA.ORT01 City,
BNKA.BGRUP Bank_Group,
TIBAN.IBAN IBAN
from
SAP_SLT_PROD_P01.LFBK
join SAP_SLT_PROD_P01.BNKA on LFBK.BANKS = BNKA.BANKS
and LFBK.BANKL = BNKA.BANKL
AND SWIFT <> ' '
join SAP_SLT_PROD_P01.TIBAN on LFBK.BANKS = TIBAN.BANKS
and LFBK.BANKL = TIBAN.BANKL
and LFBK.BANKN = TIBAN.BANKN,
SAP_SLT_PROD_P01.LFA1 lfa,
XREF_SUPS_fusion xref,
XREF_ORG_UNIT bu
WHERE
1 = 1
AND lfa.lifnr = LFBK.lifnr
AND lfa.lifnr = xref.sap_merge_id
AND lfa.ktokk = xref.sap_type
AND bu.environment = 'TEST'
UNION
--pv1
select
distinct UPPER(lfa.name1) || ' - ' || TO_NUMBER(lfa.LIFNR) vendor_name,
TO_NUMBER(lfa.LIFNR) supplier_number,
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,
LFBK.BANKS Bank_Country,
BNKA.BANKA Bank_Name,
BNKA.BNKLZ Bank_number,
LFBK.BANKL Bank_Key,
BNKA.BRNCH Bank_Branch,
BNKA.SWIFT SWIFT_BIC,
LFBK.BANKN Bank_Account,
BNKA.PROVZ Region,
BNKA.STRAS Street,
BNKA.ORT01 City,
BNKA.BGRUP Bank_Group,
TIBAN.IBAN IBAN
from
SAP_SLT_PROD_P01.LFBK
join SAP_SLT_PROD_P01.BNKA on LFBK.BANKS = BNKA.BANKS
and LFBK.BANKL = BNKA.BANKL
AND SWIFT <> ' '
join SAP_SLT_PROD_P01.TIBAN on LFBK.BANKS = TIBAN.BANKS
and LFBK.BANKL = TIBAN.BANKL
and LFBK.BANKN = TIBAN.BANKN,
SAP_SLT_PROD_PV1.LFA1 lfa,
XREF_SUPS xref,
XREF_ORG_UNIT bu
WHERE
1 = 1
AND lfa.lifnr = LFBK.lifnr
AND lfa.lifnr = xref.sap_merge_id
AND lfa.ktokk IN ('LIEF', 'ZLIF', 'LIFB', 'WLIF')
AND XREF.ORACLE_ID <> '999998'
-- DUMMY SUPPLIER
AND bu.environment = 'TEST'
)
order by
vendor_name,
procurement_business_unit_name,
party_site_name;