Source: DMF/PuC/AIF/Supplier_Merch_MFCS.sql
-- SUPPLIER PARENT
SELECT distinct
TO_NUMBER(lfa.LIFNR) supplier
,UPPER(lfa.name1) || ' - ' || TO_NUMBER(lfa.LIFNR) sup_name
,lfa.name3 sup_name_secondary
,NULL supplier_parent
,'0' contact_name
,null contact_phone
,NULL contact_fax
,NULL contact_pager
,'A' sup_status
,'N' qc_ind
,NULL qc_pct
,NULL qc_freq
,'N' vc_ind
,NULL vc_pct
,NULL vc_freq
,lfm.waers currency_code
,2 lang
,null terms
,NULL freight_terms
,'Y' ret_allow_ind
,'Y' ret_auth_req
,NULL ret_min_dol_amt
,NULL ret_courier
,NULL handling_pct
,'Y' edi_po_ind
,'N' edi_po_chg
,'Y' edi_po_confirm
,'Y' edi_asn
,NULL edi_sales_rpt_freq
,'N' edi_supp_available_ind
,'N' edi_contract_id
,'Y' edi_invc_id
,NULL edi_channel_id
,NULL cost_chg_pct_var
,NULL cost_chg_amt_var
,'N' replen_approval_ind
,NULL ship_method
,NULL payment_method
,NULL contact_telex
,'' contact_email
,'N' settlement_code
,'N' pre_mark_ind
,'N' auto_appr_invc_ind
,'N' dbt_memo_code
,'N' freight_charge_ind
,'N' auto_appr_dbt_memo_ind
,'N' prepay_invc_ind
,'N' backorder_ind
,NULL vat_region
,'S' inv_mgmt_lvl
,'N' service_perf_req_ind
,NULL invc_pay_loc
,NULL invc_receive_loc
,NULL addinvc_gross_net
,'NEXT' delivery_policy
,NULL comment_desc
,NULL default_item_lead_time
,null duns_number
,null duns_loc
,'N' bracket_costing_ind
,NULL vmi_order_status
,'N' dsd_ind
,'N' scale_aip_orders
,'N' final_dest_ind
,'EA' sup_qty_level
,TO_NUMBER(lfa.LIFNR) external_ref_id
,NULL status_upd_by_rms
,NULL deal_upload_status
,null tax_id
,NULL default_pricing_control
,NULL gen_consign_order
,NULL gen_consign_invoice
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' -- DUMMY SUPPLIER
union
-- SUPPLIER PARENT
SELECT distinct
TO_NUMBER(lfa.LIFNR) supplier
,UPPER(lfa.name1) || ' - ' || TO_NUMBER(lfa.LIFNR) sup_name
,lfa.name3 sup_name_secondary
,NULL supplier_parent
,'0' contact_name
,null contact_phone
,NULL contact_fax
,NULL contact_pager
,'A' sup_status
,'N' qc_ind
,NULL qc_pct
,NULL qc_freq
,'N' vc_ind
,NULL vc_pct
,NULL vc_freq
,lfm.waers currency_code
,2 lang
,null terms
,NULL freight_terms
,'Y' ret_allow_ind
,'Y' ret_auth_req
,NULL ret_min_dol_amt
,NULL ret_courier
,NULL handling_pct
,'Y' edi_po_ind
,'N' edi_po_chg
,'Y' edi_po_confirm
,'Y' edi_asn
,NULL edi_sales_rpt_freq
,'N' edi_supp_available_ind
,'N' edi_contract_id
,'Y' edi_invc_id
,NULL edi_channel_id
,NULL cost_chg_pct_var
,NULL cost_chg_amt_var
,'N' replen_approval_ind
,NULL ship_method
,NULL payment_method
,NULL contact_telex
,'' contact_email
,'N' settlement_code
,'N' pre_mark_ind
,'N' auto_appr_invc_ind
,'N' dbt_memo_code
,'N' freight_charge_ind
,'N' auto_appr_dbt_memo_ind
,'N' prepay_invc_ind
,'N' backorder_ind
,NULL vat_region
,'S' inv_mgmt_lvl
,'N' service_perf_req_ind
,NULL invc_pay_loc
,NULL invc_receive_loc
,NULL addinvc_gross_net
,'NEXT' delivery_policy
,NULL comment_desc
,NULL default_item_lead_time
,null duns_number
,null duns_loc
,'N' bracket_costing_ind
,NULL vmi_order_status
,'N' dsd_ind
,'N' scale_aip_orders
,'N' final_dest_ind
,'EA' sup_qty_level
,TO_NUMBER(lfa.LIFNR) external_ref_id
,NULL status_upd_by_rms
,NULL deal_upload_status
,null tax_id
,NULL default_pricing_control
,NULL gen_consign_order
,NULL gen_consign_invoice
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' -- DUMMY SUPPLIER
AND XREF.sap_merge_id = XREF.sap_id
;
-- SUPPLIER PARENT ADDRESS
SELECT distinct
'SUPP' module
,TO_NUMBER(lfa.LIFNR) key_value_1
,NULL key_value_2
,sadr.addr_type addr_type
,'Y' primary_addr_ind
,lfa.stras add_1
,NULL add_2
,NULL add_3
,lfa.ort01 city
,NULL state
--,ts.bland state --To be created in Oracle later
,lfa.land1 country_id
,lfa.pstlz post
,NULL contact_name --Tentative value
,lfa.telf1 contact_phone
,NULL contact_telex
,lfa.telfx contact_fax
,NULL contact_email --Tentative value
,NULL oracle_vendor_site_id
,NULL edi_addr_chg
,NULL county
,'N' publish_ind
,NULL jurisdiction_code
,'0' external_ref_id --To be agreed upon with financials system
FROM SAP_SLT_PROD_PV1.LFA1 lfa
,SAP_SLT_PROD_PV1.ADRC adrc
,SUP_ADDR_TYPES sadr
,XREF_SUPS xref
WHERE lfa.adrnr = adrc.addrnumber
AND lfa.ktokk IN ('LIEF', 'ZLIF','LIFB','WLIF')
AND lfa.lifnr = xref.sap_merge_id
AND XREF.ORACLE_ID <> '999998' -- DUMMY SUPPLIER
AND XREF.sap_merge_id = XREF.sap_id
;
-- SUPPLIER SITE
SELECT distinct
xref.oracle_id supplier
,upper(lfa.name1) || ' - ' || xref.oracle_org_unit sup_name
,lfa.name3 sup_name_secondary
,xref.oracle_parent_id supplier_parent
,'0' contact_name --breaks rule not null!
,lfa.telf1 contact_phone
,NULL contact_fax
,NULL contact_pager
,'A' sup_status
,'N' qc_ind --breaks rule not null!
,NULL qc_pct
,NULL qc_freq
,'N' vc_ind --breaks rule not null!
,NULL vc_pct
,NULL vc_freq
,lfm.waers currency_code
,NVL((SELECT lang_id FROM lang WHERE iso_code = lfa.land1)
,2) lang
,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.terms 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
,CASE
WHEN lfm.inco1 = ' '
THEN 'NONE'
ELSE lfm.inco1
END freight_terms
,CASE
WHEN lfa.land1 IN ('AT', 'BE', 'BG', 'HR', 'CY', 'CZ', 'DK', 'EE', 'FI', 'FR', 'DE', 'GR', 'HU', 'IE', 'IT', 'LV', 'LT', 'LU', 'MT', 'NL', 'PL', 'PT', 'RO', 'SK', 'SI', 'ES', 'SE')
THEN 'Y'
ELSE 'N'
END ret_allow_ind
,CASE
WHEN lfm.vendor_rma_req = 'X'
THEN 'Y'
ELSE 'N'
END ret_auth_req
,NULL ret_min_dol_amt
,NULL ret_courier
,NULL handling_pct
,CASE
WHEN lfa.zzliefkommkz2 = '1'
THEN 'Y'
ELSE 'N'
END edi_po_ind
,'N' edi_po_chg
,CASE
WHEN EXISTS (SELECT 1
FROM SAP_SLT_PROD_PV1.EDP21 edp
WHERE edp.sndprt = 'LI'
AND edp.mestyp = 'ORDRSP')
THEN 'Y'
ELSE 'N'
END edi_po_confirm
,CASE
WHEN EXISTS (SELECT 1
FROM SAP_SLT_PROD_PV1.EDP21 edp
WHERE edp.sndprt = 'LI'
AND edp.mestyp = 'ZLOG_DESADV_EH')
THEN 'Y'
ELSE 'N'
END edi_asn
,NULL edi_sales_rpt_freq
,'N' edi_supp_available_ind
,'N' edi_contract_id --breaks rule not null!
,CASE
WHEN EXISTS (SELECT 1
FROM SAP_SLT_PROD_PV1.EDP21 edp
WHERE edp.sndprt = 'LI'
AND edp.mestyp = 'INVOIC')
THEN 'Y'
ELSE 'N'
END edi_invc_id
,NULL edi_channel_id
,NULL cost_chg_pct_var
,NULL cost_chg_amt_var
,'N' replen_approval_ind --breaks rule not null!
,NULL ship_method
,NULL payment_method
,NULL contact_telex
,'' contact_email
,'N' settlement_code --breaks rule not null!
,'N' pre_mark_ind --breaks rule not null!
,'N' auto_appr_invc_ind --breaks rule not null!
,'N' dbt_memo_code
,'N' freight_charge_ind --breaks rule not null!
,'N' auto_appr_dbt_memo_ind --breaks rule not null!
,'N' prepay_invc_ind --breaks rule not null!
,'N' backorder_ind --breaks rule not null!
,CASE
WHEN lfa.land1 = 'CH' THEN 1500
WHEN lfa.land1 = 'RS' THEN 1501
WHEN lfa.land1 = 'DE' THEN 1010
WHEN lfa.land1 = 'LV' THEN 1060
WHEN lfa.land1 = 'IT' THEN 1070
WHEN lfa.land1 = 'NL' THEN 1080
WHEN lfa.land1 = 'CZ' THEN 1090
WHEN lfa.land1 = 'PL' and EKORG = '0251' THEN 1031
WHEN lfa.land1 = 'PL' THEN 1030
WHEN lfa.land1 = 'SL' THEN 1100
WHEN lfa.land1 = 'BE' THEN 1110
WHEN lfa.land1 = 'BG' THEN 1120
WHEN lfa.land1 = 'RO' and EKORG = '0251'THEN 1041
WHEN lfa.land1 = 'RO' THEN 1040
WHEN lfa.land1 = 'EE' THEN 1130
WHEN lfa.land1 = 'LT' THEN 1140
WHEN lfa.land1 = 'DK' THEN 1150
WHEN lfa.land1 = 'SK' and EKORG = '0251'THEN 1051
WHEN lfa.land1 = 'SK' THEN 1050
WHEN lfa.land1 = 'LU' THEN 1160
WHEN lfa.land1 = 'HU' THEN 1170
WHEN lfa.land1 = 'HR' THEN 1180
WHEN lfa.land1 = 'AT' and EKORG = '0251'THEN 1021
WHEN lfa.land1 = 'AT' THEN 1020
WHEN lfa.land1 IN ('FR','ES','PT','FI','SE','IE','GR','CY','MT') THEN 1000
ELSE 2000
END vat_region
,'S' inv_mgmt_lvl
,'N' service_perf_req_ind --breaks rule not null!
,NULL invc_pay_loc
,NULL invc_receive_loc
,NULL addinvc_gross_net
,'NEXT' delivery_policy --breaks rule not null!
,NULL comment_desc
,NULL default_item_lead_time
,SUBSTR(lfa.bbbnr || lfa.bbsnr || lfa.bubkz, 1, 9) duns_number
,SUBSTR(lfa.bbbnr || lfa.bbsnr || lfa.bubkz, -4, 4) duns_loc
,'N' bracket_costing_ind --breaks rule not null!
,NULL vmi_order_status
,'N' dsd_ind --breaks rule not null!
,'N' scale_aip_orders --'N' since it is the default value?
,'N' final_dest_ind --breaks rule not null!
,'EA' sup_qty_level
,NULL external_ref_id
,NULL status_upd_by_rms
,NULL deal_upload_status
,lfa.stceg tax_id
,NULL default_pricing_control
,NULL gen_consign_order
,NULL gen_consign_invoice
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' -- DUMMY SUPPLIER
AND XREF.sap_merge_id = XREF.sap_id
union
SELECT distinct
xref.oracle_id supplier
,upper(lfa.name1) || ' - ' || xref.oracle_org_unit sup_name
,lfa.name3 sup_name_secondary
,xref.oracle_parent_id supplier_parent
,'0' contact_name --breaks rule not null!
,lfa.telf1 contact_phone
,NULL contact_fax
,NULL contact_pager
/*,CASE
WHEN lfm.sperm = 'X'
THEN 'I'
ELSE 'A'
END sup_status*/
,'A' sup_status
,'N' qc_ind --breaks rule not null!
,NULL qc_pct
,NULL qc_freq
,'N' vc_ind --breaks rule not null!
,NULL vc_pct
,NULL vc_freq
,lfm.waers currency_code
,NVL((SELECT lang_id FROM lang WHERE iso_code = lfa.land1)
,2) lang
,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.terms 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
,CASE
WHEN lfm.inco1 = ' '
THEN 'NONE'
ELSE lfm.inco1
END freight_terms
,CASE
WHEN lfa.land1 IN ('AT', 'BE', 'BG', 'HR', 'CY', 'CZ', 'DK', 'EE', 'FI', 'FR', 'DE', 'GR', 'HU', 'IE', 'IT', 'LV', 'LT', 'LU', 'MT', 'NL', 'PL', 'PT', 'RO', 'SK', 'SI', 'ES', 'SE')
THEN 'Y'
ELSE 'N'
END ret_allow_ind
,CASE
WHEN lfm.vendor_rma_req = 'X'
THEN 'Y'
ELSE 'N'
END ret_auth_req
,NULL ret_min_dol_amt
,NULL ret_courier
,NULL handling_pct
/*,CASE
WHEN lfa.zzliefkommkz2 = '1'
THEN 'Y'
ELSE 'N'
END*/
,'Y' edi_po_ind
,'N' edi_po_chg
,CASE
WHEN EXISTS (SELECT 1
FROM SAP_SLT_PROD_PV1.EDP21 edp
WHERE edp.sndprt = 'LI'
AND edp.mestyp = 'ORDRSP')
THEN 'Y'
ELSE 'N'
END edi_po_confirm
,CASE
WHEN EXISTS (SELECT 1
FROM SAP_SLT_PROD_PV1.EDP21 edp
WHERE edp.sndprt = 'LI'
AND edp.mestyp = 'ZLOG_DESADV_EH')
THEN 'Y'
ELSE 'N'
END edi_asn
,NULL edi_sales_rpt_freq
,'N' edi_supp_available_ind
,'N' edi_contract_id --breaks rule not null!
,CASE
WHEN EXISTS (SELECT 1
FROM SAP_SLT_PROD_PV1.EDP21 edp
WHERE edp.sndprt = 'LI'
AND edp.mestyp = 'INVOIC')
THEN 'Y'
ELSE 'N'
END edi_invc_id
,NULL edi_channel_id
,NULL cost_chg_pct_var
,NULL cost_chg_amt_var
,'N' replen_approval_ind --breaks rule not null!
,NULL ship_method
,NULL payment_method
,NULL contact_telex
,'' contact_email
,'N' settlement_code --breaks rule not null!
,'N' pre_mark_ind --breaks rule not null!
,'N' auto_appr_invc_ind --breaks rule not null!
,'N' dbt_memo_code
,'N' freight_charge_ind --breaks rule not null!
,'N' auto_appr_dbt_memo_ind --breaks rule not null!
,'N' prepay_invc_ind --breaks rule not null!
,'N' backorder_ind --breaks rule not null!
,CASE
WHEN lfa.land1 = 'CH' THEN 1500
WHEN lfa.land1 = 'RS' THEN 1501
WHEN lfa.land1 = 'DE' THEN 1010
WHEN lfa.land1 = 'LV' THEN 1060
WHEN lfa.land1 = 'IT' THEN 1070
WHEN lfa.land1 = 'NL' THEN 1080
WHEN lfa.land1 = 'CZ' THEN 1090
WHEN lfa.land1 = 'PL' and EKORG = '0251' THEN 1031
WHEN lfa.land1 = 'PL' THEN 1030
WHEN lfa.land1 = 'SL' THEN 1100
WHEN lfa.land1 = 'BE' THEN 1110
WHEN lfa.land1 = 'BG' THEN 1120
WHEN lfa.land1 = 'RO' and EKORG = '0251'THEN 1041
WHEN lfa.land1 = 'RO' THEN 1040
WHEN lfa.land1 = 'EE' THEN 1130
WHEN lfa.land1 = 'LT' THEN 1140
WHEN lfa.land1 = 'DK' THEN 1150
WHEN lfa.land1 = 'SK' and EKORG = '0251'THEN 1051
WHEN lfa.land1 = 'SK' THEN 1050
WHEN lfa.land1 = 'LU' THEN 1160
WHEN lfa.land1 = 'HU' THEN 1170
WHEN lfa.land1 = 'HR' THEN 1180
WHEN lfa.land1 = 'AT' and EKORG = '0251'THEN 1021
WHEN lfa.land1 = 'AT' THEN 1020
WHEN lfa.land1 IN ('FR','ES','PT','FI','SE','IE','GR','CY','MT') THEN 1000
ELSE 2000
END vat_region
,'S' inv_mgmt_lvl
,'N' service_perf_req_ind --breaks rule not null!
,NULL invc_pay_loc
,NULL invc_receive_loc
,NULL addinvc_gross_net
,'NEXT' delivery_policy --breaks rule not null!
,NULL comment_desc
,NULL default_item_lead_time
,SUBSTR(lfa.bbbnr || lfa.bbsnr || lfa.bubkz, 1, 9) duns_number
,SUBSTR(lfa.bbbnr || lfa.bbsnr || lfa.bubkz, -4, 4) duns_loc
,'N' bracket_costing_ind --breaks rule not null!
,NULL vmi_order_status
,'N' dsd_ind --breaks rule not null!
,'N' scale_aip_orders --'N' since it is the default value?
,'N' final_dest_ind --breaks rule not null!
,'EA' sup_qty_level
,NULL external_ref_id
,NULL status_upd_by_rms
,NULL deal_upload_status
,lfa.stceg tax_id
,NULL default_pricing_control
,NULL gen_consign_order
,NULL gen_consign_invoice
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' -- DUMMY SUPPLIER
AND XREF.sap_merge_id = XREF.sap_id
;
-- supplier site address
SELECT distinct
'SUPP' module
,xref.oracle_id key_value_1
,NULL key_value_2
--,sadr.addr_type addr_type
,'Y' primary_addr_ind
,lfa.stras add_1
,NULL add_2
,NULL add_3
,lfa.ort01 city
,NULL state --Will be changed to the Oracle code
,lfa.land1 country_id
,lfa.pstlz post
,NVL(TRIM(adrc.name1 || adrc.name2), '0') contact_name --Tentative value
,lfa.telf1 contact_phone
,NULL contact_telex
,NULL contact_fax
,NULL contact_email --Tentative value
,NULL oracle_vendor_site_id
,NULL edi_addr_chg
,NULL county
,'N' publish_ind
,NULL jurisdiction_code
,'0' external_ref_id --To be agreed upon with financials system
FROM SAP_SLT_PROD_PV1.LFA1 lfa
,SAP_SLT_PROD_PV1.ADRC adrc
--,SUP_ADDR_TYPES sadr
,XREF_SUPS xref
WHERE lfa.adrnr = adrc.addrnumber
AND lfa.ktokk IN ('LIEF', 'ZLIF','LIFB','WLIF')
AND lfa.lifnr = xref.sap_merge_id
AND XREF.ORACLE_ID <> '999998' -- DUMMY SUPPLIER
AND XREF.sap_merge_id = XREF.sap_id
union
SELECT distinct 'SUPP' module
,xref.oracle_id key_value_1
,NULL key_value_2
--,sadr.addr_type addr_type
,'Y' primary_addr_ind
,lfa.stras add_1
,NULL add_2
,NULL add_3
,lfa.ort01 city
,NULL state --Will be changed to the Oracle code
,lfa.land1 country_id
,lfa.pstlz post
,NVL(TRIM(adrc.name1 || adrc.name2), '0') contact_name --Tentative value
,lfa.telf1 contact_phone
,NULL contact_telex
,NULL contact_fax
,NULL contact_email --Tentative value
,NULL oracle_vendor_site_id
,NULL edi_addr_chg
,NULL county
,'N' publish_ind
,NULL jurisdiction_code
,'0' external_ref_id --To be agreed upon with financials system
FROM SAP_SLT_PROD_PR1.LFA1 lfa
,SAP_SLT_PROD_PR1.ADRC adrc
--,SUP_ADDR_TYPES sadr
,XREF_SUPS xref
WHERE lfa.adrnr = adrc.addrnumber
AND lfa.ktokk IN ('LIEF', 'ZLIF','LIFB','WLIF')
AND lfa.lifnr = xref.sap_merge_id
AND XREF.ORACLE_ID <> '999998' -- DUMMY SUPPLIER
AND XREF.sap_merge_id = XREF.sap_id
;
--remover depois do insert as linhas de endereço duplicadas para os fornecedores(2138961005,1999901005)
select * from ADDR_STG where key_value_1 in (2138961005,1999901005);
delete from ADDR_STG where key_value_1 in (1999901005) and add_1 = ' ';
delete from ADDR_STG where key_value_1 in (2138961005) and city = 'Hoerstel';
select count(1), key_value_1 from ADDR_STG group by key_value_1 having count(1) < 5 or count(1) > 5;
-- validar se todos os suppliers da XREF estão na tabela de stage
select * from xref_sups x where not exists (select 1 from sups_stg s where s.supplier = x.oracle_parent_id) and oracle_id <> '999998';
SELECT * FROM SUPS_STG WHERE SUPPLIER_PARENT IS NULL;