Source: DMF/PuC/AIF/SupplierPR1.sql
INSERT INTO SUPS_STG (supplier,
sup_name,
sup_name_secondary,
supplier_parent,
contact_name,
contact_phone,
contact_fax,
contact_pager,
sup_status,
qc_ind,
qc_pct,
qc_freq,
vc_ind,
vc_pct,
vc_freq,
currency_code,
lang,
terms,
freight_terms,
ret_allow_ind,
ret_auth_req,
ret_min_dol_amt,
ret_courier,
handling_pct,
edi_po_ind,
edi_po_chg,
edi_po_confirm,
edi_asn,
edi_sales_rpt_freq,
edi_supp_available_ind,
edi_contract_ind,
edi_invc_ind,
edi_channel_id,
cost_chg_pct_var,
cost_chg_amt_var,
replen_approval_ind,
ship_method,
payment_method,
contact_telex,
contact_email,
settlement_code,
pre_mark_ind,
auto_appr_invc_ind,
dbt_memo_code,
freight_charge_ind,
auto_appr_dbt_memo_ind,
prepay_invc_ind,
backorder_ind,
vat_region,
inv_mgmt_lvl,
service_perf_req_ind,
invc_pay_loc,
invc_receive_loc,
addinvc_gross_net,
delivery_policy,
comment_desc,
default_item_lead_time,
duns_number,
duns_loc,
bracket_costing_ind,
vmi_order_status,
dsd_ind,
scale_aip_orders,
final_dest_ind,
sup_qty_level,
external_ref_id,
status_upd_by_rms,
deal_upload_status,
tax_id,
default_pricing_control,
gen_consign_invoice,
gen_consign_order)
SELECT distinct xref.oracle_parent_id supplier
,lfa.name1 sup_name
,lfa.name3 sup_name_secondary
,NULL supplier_parent
,'0' contact_name
,lfa.telf1 contact_phone
,NULL contact_fax
,NULL contact_pager
,CASE
WHEN lfm.sperm = 'X'
THEN 'I'
ELSE 'A'
END sup_status
,'N' qc_ind
,NULL qc_pct
,NULL qc_freq
,'N' vc_ind
,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
,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
,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
,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
,NULL vmi_order_status
,'N' dsd_ind
,'N' scale_aip_orders
,'N' final_dest_ind
,'EA' sup_qty_level
,xref.oracle_parent_id 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
FROM SAP_SLT_PROD_PR1.LFM1
where ekorg = '0506') lfm
,XREF_SUPS xref
WHERE lfa.ktokk IN ('LIEF', 'ZLIF')
AND lfa.mandt = lfm.mandt
AND lfa.lifnr = lfm.lifnr
AND lfa.lifnr = xref.sap_id
AND NOT EXISTS (SELECT 1
FROM sups_ctrl s
WHERE s.supplier = xref.oracle_id)
/*and lfm.lifnr in (
'0000237399',
'0000236704',
'0000236116',
'0000236705',
'0000236723',
'0000236998',
'0000236882',
'0000236526',
'0000236744',
'0000236995',
'0000236578',
'0000237251',
'0000236627',
'0000237801',
'0000236565',
'0000237119',
'0000237028',
'0000236879',
'0000237739',
'0000236092',
'0000236516',
'0000236251',
'0000236714',
'0000237234',
'0000236887',
'0000236308',
'0000236153',
'0000236654',
'0000237798',
'0000236612',
'0000236094',
'0000236510',
'0000236132')*/
;
INSERT INTO ADDR_STG(module,
key_value_1,
key_value_2,
addr_type,
primary_addr_ind,
add_1,
add_2,
add_3,
city,
state,
country_id,
post,
contact_name,
contact_phone,
contact_telex,
contact_fax,
contact_email,
oracle_vendor_site_id,
edi_addr_chg,
county,
publish_ind,
jurisdiction_code,
external_ref_id)
SELECT 'SUPP' module
,xref.oracle_parent_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
--,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
,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
--,SAP_SLT_PROD_PV1.T005S ts
,XREF_SUPS xref
WHERE lfa.adrnr = adrc.addrnumber
AND lfa.ktokk IN ('LIEF', 'ZLIF')
--AND lfa.regio = ts.bland (+)
--AND lfa.land1 = ts.land1
AND lfa.lifnr = xref.sap_id
AND xref.ORACLE_PARENT_ID in
('1000237850',
'1000237801',
'1000237028',
'1000236996',
'1000236765',
'1000236704',
'1000236627',
'1000236510',
'1000236318',
'1000236082',
'1000236092',
'1000237849',
'1000237870',
'1000237853',
'1000237399',
'1000237117',
'1000236386',
'1000236784',
'1000237884',
'1000236706',
'1000236251',
'1000236654',
'1000236516',
'1000236310',
'1000237858',
'1000237790',
'1000237823',
'1000236995',
'1000236103',
'1000236111',
'1000236470',
'1000236120',
'1000236316',
'1000236319',
'1000237852',
'1000237861',
'1000237868',
'1000237802',
'1000237831',
'1000236879',
'1000236705',
'1000236526',
'1000236578',
'1000236284',
'1000236336',
'1000237869',
'1000237867',
'1000236883',
'1000236116',
'1000236473',
'1000236565',
'1000236230',
'1000236277',
'1000237851',
'1000237251',
'1000237579',
'1000237876',
'1000237090',
'1000236998',
'1000236997',
'1000236744',
'1000236232',
'1000236153',
'1000237835',
'1000237859',
'1000237866',
'1000237797',
'1000237827',
'1000237234',
'1000236938',
'1000237027',
'1000237166',
'1000236882',
'1000236315',
'1000236714',
'1000236723',
'1000236612',
'1000236597',
'1000236400',
'1000236138',
'1000236305',
'1000236307',
'1000236308',
'1000236094',
'1000237886',
'1000237739',
'1000237798',
'1000237836',
'1000237119',
'1000236887',
'1000236808',
'1000236155',
'1000236494',
'1000236564',
'1000236132',
'1000236304',
'1000236317')
;
INSERT INTO SUPS_STG (supplier,
sup_name,
sup_name_secondary,
supplier_parent,
contact_name,
contact_phone,
contact_fax,
contact_pager,
sup_status,
qc_ind,
qc_pct,
qc_freq,
vc_ind,
vc_pct,
vc_freq,
currency_code,
lang,
terms,
freight_terms,
ret_allow_ind,
ret_auth_req,
ret_min_dol_amt,
ret_courier,
handling_pct,
edi_po_ind,
edi_po_chg,
edi_po_confirm,
edi_asn,
edi_sales_rpt_freq,
edi_supp_available_ind,
edi_contract_ind,
edi_invc_ind,
edi_channel_id,
cost_chg_pct_var,
cost_chg_amt_var,
replen_approval_ind,
ship_method,
payment_method,
contact_telex,
contact_email,
settlement_code,
pre_mark_ind,
auto_appr_invc_ind,
dbt_memo_code,
freight_charge_ind,
auto_appr_dbt_memo_ind,
prepay_invc_ind,
backorder_ind,
vat_region,
inv_mgmt_lvl,
service_perf_req_ind,
invc_pay_loc,
invc_receive_loc,
addinvc_gross_net,
delivery_policy,
comment_desc,
default_item_lead_time,
duns_number,
duns_loc,
bracket_costing_ind,
vmi_order_status,
dsd_ind,
scale_aip_orders,
final_dest_ind,
sup_qty_level,
external_ref_id,
status_upd_by_rms,
deal_upload_status,
tax_id,
default_pricing_control,
gen_consign_invoice,
gen_consign_order)
SELECT xref.oracle_id supplier
,lfa.name1 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
,'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!
,NULL 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
,xref.oracle_id 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
FROM SAP_SLT_PROD_PR1.LFM1
where ekorg = '0506') lfm
,XREF_SUPS xref
WHERE lfa.ktokk IN ('LIEF', 'ZLIF')
AND lfa.mandt = lfm.mandt
AND lfa.lifnr = lfm.lifnr
AND lfa.lifnr = xref.sap_id
AND NOT EXISTS (SELECT 1
FROM sups_ctrl s
WHERE s.supplier = xref.oracle_id);
INSERT INTO ADDR_STG(module,
key_value_1,
key_value_2,
addr_type,
primary_addr_ind,
add_1,
add_2,
add_3,
city,
state,
country_id,
post,
contact_name,
contact_phone,
contact_telex,
contact_fax,
contact_email,
oracle_vendor_site_id,
edi_addr_chg,
county,
publish_ind,
jurisdiction_code,
external_ref_id)
SELECT '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
--,SAP_SLT_PROD_PV1.T005S ts
,XREF_SUPS xref
WHERE lfa.adrnr = adrc.addrnumber
AND lfa.ktokk IN ('LIEF', 'ZLIF')
--AND lfa.regio = ts.bland (+)
--AND lfa.land1 = ts.land1
AND lfa.lifnr = xref.sap_id
AND xref.ORACLE_PARENT_ID in
('1000237850',
'1000237801',
'1000237028',
'1000236996',
'1000236765',
'1000236704',
'1000236627',
'1000236510',
'1000236318',
'1000236082',
'1000236092',
'1000237849',
'1000237870',
'1000237853',
'1000237399',
'1000237117',
'1000236386',
'1000236784',
'1000237884',
'1000236706',
'1000236251',
'1000236654',
'1000236516',
'1000236310',
'1000237858',
'1000237790',
'1000237823',
'1000236995',
'1000236103',
'1000236111',
'1000236470',
'1000236120',
'1000236316',
'1000236319',
'1000237852',
'1000237861',
'1000237868',
'1000237802',
'1000237831',
'1000236879',
'1000236705',
'1000236526',
'1000236578',
'1000236284',
'1000236336',
'1000237869',
'1000237867',
'1000236883',
'1000236116',
'1000236473',
'1000236565',
'1000236230',
'1000236277',
'1000237851',
'1000237251',
'1000237579',
'1000237876',
'1000237090',
'1000236998',
'1000236997',
'1000236744',
'1000236232',
'1000236153',
'1000237835',
'1000237859',
'1000237866',
'1000237797',
'1000237827',
'1000237234',
'1000236938',
'1000237027',
'1000237166',
'1000236882',
'1000236315',
'1000236714',
'1000236723',
'1000236612',
'1000236597',
'1000236400',
'1000236138',
'1000236305',
'1000236307',
'1000236308',
'1000236094',
'1000237886',
'1000237739',
'1000237798',
'1000237836',
'1000237119',
'1000236887',
'1000236808',
'1000236155',
'1000236494',
'1000236564',
'1000236132',
'1000236304',
'1000236317')
;
select mar.attyp
, mar.brand_id
, mar.matnr
, mar.satnr
, eid.lifnr
, relif
, mar.*
, eid.*
from sap_slt_prod_pv1.mara mar
JOIN sap_slt_prod_pv1.eina eid
ON eid.mandt = mar.mandt
AND eid.matnr = mar.satnr
where mar.matnr = '000000182594110020';
select * From XREF_SUPS where sap_id in ('0000236582','0000230200','0000237338','0000221570');
SAP SAP MERGE ID
0000230200 --> 0000223808 -->
0000236582 --> 0000210455]
select lifnr, ktokk, name1, name3, SORTL, ZZLIEFETIKETTKZ, ZZTAGE_FRMDETI, ZZLIEFGROESONDKZ
from sap_slt_prod_pv1.lfa1 where ZZTAGE_FRMDETI <> '00000' and ktokk IN ('LIEF', 'ZLIF','LIFB','WLIF') ;
select ZZLIEFETIKETTKZ, count(1)
from sap_slt_prod_pv1.lfa1 where ktokk IN ('LIEF', 'ZLIF','LIFB','WLIF') group by ZZLIEFETIKETTKZ ;
-- A, B, 9 and blank is the same
-- 3 - LABELLED AND PRINTED BY MYSELF
-- 4 - LABELLED, PRINTED BY PRINTSHOP
select ZZLIEFGROESONDKZ, count(1)
from sap_slt_prod_pv1.lfa1 where ktokk IN ('LIEF', 'ZLIF','LIFB','WLIF') group by ZZLIEFGROESONDKZ ;
-- null and zero no extra charge
-- 1 10%
-- 2 10% or 15%
-- there is a field with the %