Skip to content

Instantly share code, notes, and snippets.

@byaussy

byaussy/full_maps.sql

Last active August 27, 2020 02:20
Show Gist options
  • Save byaussy/b926d09fdb855816fb08f3693e2e7577 to your computer and use it in GitHub Desktop.
Save byaussy/b926d09fdb855816fb08f3693e2e7577 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
full_maps.sql
-- CLIENTS FULL MAP
with client_all as
(
select distinct client.office, client.externalcode, client.client
from dentsu_customer_client_unique client
join base_customer_unique cust
on client.office = cust.office and
client.externalcode = cust.externalcode
where cust.mdm_id is not null
),
nat_hmx_media AS (
SELECT DISTINCT nat.client, nat.accofficenumber AS office, hmt.mdm_id AS mdm_media, msmt.dan_media_type_name
FROM dentsu_national_client_unique nat
JOIN map_sub_media_type msmt ON nat.media = msmt.dan_media_type_code
AND msmt.dan_toolkit_code = 'NATIONAL'
JOIN base_sub_media_type hsmt ON msmt.fk_base_sub_media_type_id = hsmt.id
JOIN base_media_type hmt on hsmt.fk_base_media_type_id = hmt.id
),
spot_hmx_media AS (
SELECT DISTINCT spot.client, spot.acccodeofficecodeagy AS office, hmt.mdm_id AS mdm_media, msmt.dan_media_type_name
FROM dentsu_spot_client_unique spot
JOIN map_sub_media_type msmt ON spot.media = msmt.dan_media_type_code
AND msmt.dan_toolkit_code = 'SPOT'
JOIN base_sub_media_type hsmt ON msmt.fk_base_sub_media_type_id = hsmt.id
JOIN base_media_type hmt on hsmt.fk_base_media_type_id = hmt.id
),
print_hmx_media AS (
SELECT DISTINCT print.client, print.accofficecode AS office, hmt.mdm_id AS mdm_media, msmt.dan_media_type_name
FROM dentsu_print_client_unique print
JOIN map_sub_media_type msmt ON print.media = msmt.dan_media_type_code
AND msmt.dan_toolkit_code = 'PRINT'
JOIN base_sub_media_type hsmt ON msmt.fk_base_sub_media_type_id = hsmt.id
JOIN base_media_type hmt on hsmt.fk_base_media_type_id = hmt.id
),
all_media AS (
SELECT client, office, string_agg(dan_media_type_name::TEXT, ', ') AS medias FROM nat_hmx_media
GROUP BY client, office
UNION
SELECT client, office, string_agg(dan_media_type_name::TEXT, ', ') AS medias FROM spot_hmx_media
GROUP BY client, office
UNION
SELECT client, office, string_agg(dan_media_type_name::TEXT, ', ') AS medias FROM print_hmx_media
GROUP BY client, office),
client_mdm_media AS(SELECT b.client_code, b.office, string_agg(am.medias, ', ') AS media_type_id
FROM base_client_unique b
JOIN all_media am ON am.client = b.client_code AND am.office = b.office
GROUP BY b.client_code, b.office
),
client_aaa_products as
(
select distinct c.client, c.clientname, c.media, 'BILLING' as address_type, p.billtoname,
p.addressline1, p.addressline2, p.addressline3, '' as addressline4
from dentsu_national_product_unique p
join dentsu_national_client_unique c on c.client = p.client and c.media = p.media
where p.product='AAA'
union distinct
select distinct c.client, c.clientname, c.media, 'BILLING' as address_type, p.billreceiptnameline1 as billtoname,
p.billreceiptnameline2 as addressline1, p.addressline1 as addressline2, p.addressline2 as addressline3, '' as addressline4
from dentsu_print_product_unique p
join dentsu_print_client_unique c on c.client = p.client and c.media = p.media
where p.product='AAA'
union distinct
select distinct c.client, c.clientname, c.media, 'BILLING' as address_type, p.billtoname,
p.addressline1, p.addressline2, p.addressline3, '' as addressline4
from dentsu_spot_product_unique p
join dentsu_spot_client_unique c on c.client = p.client and c.media = p.media
where p.product='AAA'
)
select distinct
ba.name as agency,
ba.code as agency_code,
bao.name as accounting_office_name,
bao.code as accounting_office_code,
fc.office as office_code,
cust.d365customeraccount as d365customeraccount,
cust.externalcode as ddscustomerid,
cust.customername as customer_name,
fc.clientaccountcode as client_code,
fc.clientaccountname as client_name,
CASE WHEN cmm.client_code is not null and cmm.office is not null and cmm.media_type_id is not null
THEN cmm.media_type_id
END as media_type_ids,
CASE WHEN fc.intercompanymarker = 'I' THEN 'INTCO' ELSE 'TRADE' END AS client_type,
fc.billingaddressline1 as fin_cli_bill_addr1,
fc.billingaddressline2 as fin_cli_bill_addr2,
fc.billingaddressline3 as fin_cli_bill_addr3,
fc.billingaddressline4 as fin_cli_bill_addr4,
fc.line5 as fin_cli_bill_addr5,
CASE WHEN aaa.address_type is not null
THEN jsonb_agg(json_build_object(
'address_type',aaa.address_type,
'billtoname',aaa.billtoname,
'addressline1',aaa.addressline1,
'addressline2',aaa.addressline2,
'addressline3',aaa.addressline3,
'addressline4',aaa.addressline4))
over (partition by call.office, call.externalcode, call.client)
END as other_addresses
from client_all call
right join dentsu_financial_client_unique fc
on fc.clientaccountcode = call.client and
fc.office = call.office and
concat('SR',fc.receivableaccountcode) = call.externalcode
join dentsu_customer_unique cust
on (cust.office = call.office or concat('0',cust.office) = call.office) and
cust.externalcode = call.externalcode
join map_agency_accounting_office mao
on mao.code = call.office
join base_agency_accounting_office bao
on bao.id = mao.fk_base_agency_accounting_office_id
join base_agency ba on bao.fk_base_agency_id = ba.id
left join client_aaa_products aaa on aaa.client = fc.clientaccountcode and aaa.clientname = fc.clientaccountname
left join client_mdm_media cmm on cmm.office = fc.office and cmm.client_code = fc.clientaccountcode
where call.externalcode is not null
;
with client_aaa_products as (
select c.client, c.clientname, c.media,
p.billtoname, p.addressline1, p.addressline2, p.addressline3
from dentsu_national_product_unique p
join dentsu_national_client_unique c on c.client = p.client and c.media = p.media
where p.product='AAA'
union all
select c.client, c.clientname, c.media,
p.billreceiptnameline1 as billtoname, p.billreceiptnameline2 as addressline2, p.addressline2, '' as addressline3
from dentsu_print_product_unique p
join dentsu_print_client_unique c on c.client = p.client and c.media = p.media
where p.product='AAA'
union all
select c.client, c.clientname, c.media,
p.billtoname, p.addressline1, p.addressline2, p.addressline3
from dentsu_spot_product_unique p
join dentsu_spot_client_unique c on c.client = p.client and c.media = p.media
where p.product='AAA'
)
-- PRODUCTS FULL MAP
SELECT DISTINCT
ba.name as agency,
ba.code as agency_code,
bao.name as accounting_office_name,
bao.code as accounting_office_code,
p.office as office_code,
cust.externalcode as ddscustomerid,
cust.d365customeraccount as d365customeraccount,
cust.customername as customer_name,
fc.clientaccountcode as client_code,
fc.clientaccountname as client_name,
fin.productcode as product_code,
fin.productname as product_name,
fin.intercompanymarker as intercompany_marker,
fin.billingaddressline1 as addr1,
fin.billingaddressline2 as addr2,
fin.billingaddressline3 as addr3,
fin.billingaddressline4 as addr4,
fin.line as addr5_aka_line
FROM base_product_unique p
join base_client_unique c
on p.client_code = c.client_code and
p.customer_code = c.customer_code and
p.office = c.office and
c.mdm_id is not null
join dentsu_financial_product_unique fin
on fin.office = p.office and
fin.clientaccountcode = p.client_code and
fin.productname = p.product_name and
fin.productcode = p.product_code and
fin.receivableaccountproduct = ''
join dentsu_financial_client_unique fc
on fc.clientaccountcode = p.client_code and
fc.office = p.office and
concat('SR',fc.receivableaccountcode) = c.customer_code
join dentsu_customer_unique cust
on (cust.office = p.office or concat('0',cust.office) = p.office) and
cust.externalcode = c.customer_code
join map_agency_accounting_office mao
on mao.code = p.office
join base_agency_accounting_office bao
on bao.id = mao.fk_base_agency_accounting_office_id
join base_agency ba on bao.fk_base_agency_id = ba.id
order by cust.customername, fin.productcode, fin.productname
;
SELECT DISTINCT agency, buyid, media, client, product, estimate, market, station, line, origmarket, daypart, seconds, rotationdays,
rotationtime, purpose, programname, specialrep, adjacencycode, ratetype, daypartcode, creationdate, lastactivitydate,
pid, contract_buyid, spotdate, weekstart
FROM dentsu_spot_buydata;
select distinct startdate from dentsu_national_estimate_unique;
-- ESTIMATES FULL MAP
with all_estimates as
(
(
select distinct e.media,
e.medianame,
e.client as client_code,
cpe.clientname as client_name,
e.product as product_code,
cpe.productname as product_name,
e.estimate as code,
'PRINT' as billformtype,
case when e.billbasis <> 'NULL' then e.billbasis else '' end as billform1,
case when e.commpct <> 'NULL' then e.commpct else '' end as billform2,
case when e.commbasis <> 'NULL' then e.commbasis else '' end as billform3,
case when e.startdate <> 'NULL' then e.startdate else '' end as billform4,
'' as billform5,
hmt.mdm_id as hmx_media_type_id,
hmt.code as hmx_media_type_code,
hmt.name as hmx_media_type_name,
hsmt.code as hmx_sub_media_type_code,
hsmt.name as hmx_sub_media_type_name,
msmt.dan_media_type_code as sub_media_type_id,
msmt.dan_media_type_name as sub_media_type_name,
e.description as name,
'Draft' as media_type_status,
e.status,
e.startdate as start_date,
e.enddate as end_date,
c.accofficenumber as office,
cpe.ddscustomerid,
cpe.aracctcode,
'NATIONAL' as media_pak
from dentsu_national_estimate_unique e
join dentsu_national_product_unique p
on e.media = p.media and
e.client = p.client and
e.product = p.product
join map_client_product_entity_unique cpe
on cpe.cli = e.client and
cpe.productcode = e.product and
cpe.national = cpe.cli and
e.media = 'N'
join dentsu_national_client c
on c.client = cpe.cli
and c.clientname = cpe.clientname
and c.accofficenumber like split_part(cpe.office, ' ', 1)
JOIN map_sub_media_type msmt on e.media = msmt.dan_media_type_code AND
dan_toolkit_code = 'NATIONAL'
JOIN base_sub_media_type hsmt ON hsmt.id = msmt.fk_base_sub_media_type_id
JOIN base_media_type hmt ON hmt.id = hsmt.fk_base_media_type_id
)
union all
(
select distinct e.media,
e.medianame,
e.client as client_code,
cpe.clientname as client_name,
e.product as product_code,
cpe.productname as product_name,
e.estimate as code,
'PRINT' as billformtype,
case when e.billbasis <> 'NULL' then e.billbasis else '' end as billform1,
case when e.commpct <> 'NULL' then e.commpct else '' end as billform2,
case when e.commbasis <> 'NULL' then e.commbasis else '' end as billform3,
'' as billform4,
'' as billform5,
hmt.mdm_id as hmx_media_type_id,
hmt.code as hmx_media_type_code,
hmt.name as hmx_media_type_name,
hsmt.code as hmx_sub_media_type_code,
hsmt.name as hmx_sub_media_type_name,
msmt.dan_media_type_code as sub_media_type_id,
msmt.dan_media_type_name as sub_media_type_name,
e.description as name,
'Draft' as media_type_status,
e.status,
e.startdate as start_date,
e.enddate as end_date,
c.acccodeofficecodeagy as office,
cpe.ddscustomerid,
cpe.aracctcode,
'SPOT' as media_pak
from dentsu_spot_estimate_unique e
join dentsu_spot_product_unique p
on e.media = p.media and
e.client = p.client and
e.product = p.product
join map_client_product_entity_unique cpe
on cpe.cli = e.client and
cpe.productcode = e.product and
((cpe.radio = cpe.cli and e.media = 'R') or
(cpe.localtv = cpe.cli and e.media = 'T') or
(cpe.networkradio = cpe.cli and e.media = 'X'))
join dentsu_spot_client c
on c.client = cpe.cli
and c.clientname = cpe.clientname
and c.acccodeofficecodeagy like split_part(cpe.office, ' ', 1)
JOIN map_sub_media_type msmt on e.media = msmt.dan_media_type_code AND
dan_toolkit_code = 'SPOT'
JOIN base_sub_media_type hsmt ON hsmt.id = msmt.fk_base_sub_media_type_id
JOIN base_media_type hmt ON hmt.id = hsmt.fk_base_media_type_id
)
union all
(
select distinct e.media,
e.medianame,
e.clientcode as client_code,
cpe.clientname as client_name,
e.productcode as product_code,
cpe.productname as product_name,
e.estimatecode as code,
'PRINT' as billformtype,
case when e.billformulabase <> 'NULL' then e.billformulabase else '' end as billform1,
case when e.ofcode <> 'NULL' then e.ofcode else '' end as billform2,
case when e.pctadj <> 'NULL' then e.pctadj else '' end as billform3,
case when e.pctof <> 'NULL' then e.pctof else '' end as billform4,
case when e.effdate <> 'NULL' then e.effdate else '' end as billform5,
hmt.mdm_id as hmx_media_type_id,
hmt.code as hmx_media_type_code,
hmt.name as hmx_media_type_name,
hsmt.code as hmx_sub_media_type_code,
hsmt.name as hmx_sub_media_type_name,
msmt.dan_media_type_code as sub_media_type_id,
msmt.dan_media_type_name as sub_media_type_name,
case
when e.estimatename <> e.estimatenameline1 then
case
when e.estimatenameline2 <> '' then
concat(e.estimatename, ', ', e.estimatenameline1, ', ',
estimatenameline2)
else
concat(e.estimatename, ', ', e.estimatenameline1)
end
else
case
when e.estimatenameline2 <> '' then
concat(e.estimatename, ', ', estimatenameline2)
else
e.estimatename
end
end
as name,
'Draft' as media_type_status,
e.status,
e.estimatestartdate as start_date,
e.estimateenddate as end_date,
c.accofficecode as office,
cpe.ddscustomerid,
cpe.aracctcode,
'PRINT' as media_pak
from dentsu_print_estimate_unique e
join dentsu_print_product_unique p
on e.media = p.media and
e.clientcode = p.client and
e.productcode = p.product
join map_client_product_entity_unique cpe
on cpe.cli = e.clientcode and
cpe.productcode = e.productcode and
((cpe.social = cpe.cli and e.media = 'L') or
(cpe.outdoor = cpe.cli and e.media = 'O') or
(cpe.magazine = cpe.cli and e.media = 'M') or
(cpe.internet = cpe.cli and e.media = 'I') or
(cpe.newspaper = cpe.cli and e.media = 'N') or
(cpe.trademag = cpe.cli and e.media = 'T') or
(cpe.search = cpe.cli and e.media = 'S'))
join dentsu_print_client c
on c.client = cpe.cli
and c.clientname = cpe.clientname
and c.accofficecode like split_part(cpe.office, ' ', 1)
JOIN map_sub_media_type msmt on e.media = msmt.dan_media_type_code AND
dan_toolkit_code = 'PRINT'
JOIN base_sub_media_type hsmt ON hsmt.id = msmt.fk_base_sub_media_type_id
JOIN base_media_type hmt ON hmt.id = hsmt.fk_base_media_type_id
)
)
select distinct
ba.name as agency,
ba.code as agency_code,
bao.name as accounting_office_name,
bao.code as accounting_office_code,
ae.office as office_code,
cust.externalcode as ddscustomerid,
cust.d365customeraccount as d365customeraccount,
cust.customername as customer_name,
ae.client_code,
ae.client_name,
ae.product_code,
ae.product_name,
ae.code as estimate_code,
ae.billformtype,
ae.billform1,
ae.billform2,
ae.billform3,
ae.billform4,
ae.billform5,
ae.name as estimate_name,
to_date(ae.start_date, 'MONDD/YY') as estimate_start_date,
to_date(ae.end_date, 'MONDD/YY') as estimate_end_date,
ae.media_pak,
ae.sub_media_type_id as dan_media_code,
ae.sub_media_type_name as dan_media_name,
ae.hmx_media_type_code as hmx_media_code,
ae.hmx_media_type_name as hmx_media_name,
ae.hmx_sub_media_type_code as hmx_sub_media_code,
ae.hmx_sub_media_type_name as hmx_sub_media_name,
ae.status as estimate_status,
ae.status <> 'STEW' as billable,
ae.status = 'LOCK' as media_supervisor_lock,
ae.status <> 'STEW' as payable
from all_estimates ae
join base_product_unique p
on p.product_code = ae.product_code and
p.product_name = ae.product_name and
p.client_code = ae.client_code and
p.customer_code = ae.ddscustomerid and
p.office like split_part(ae.office, ' ', 1) and
p.mdm_id is not null
join dentsu_customer_unique cust
on (cust.office = ae.office or concat('0',cust.office) = ae.office) and
cust.externalcode = ae.ddscustomerid
join map_agency_accounting_office mao
on mao.code = ae.office
join base_agency_accounting_office bao
on bao.id = mao.fk_base_agency_accounting_office_id
join base_agency ba on bao.fk_base_agency_id = ba.id
order by billform2 desc, billform3 desc, billform4 desc
;
-- VENDORS FULL MAP
with all_vendors as
(
(
SELECT DISTINCT
dvu.grp,
dvu.d365vendoraccount as erp_vendor_id,
stationname as addr1,
stationaddress as addr2,
'' as addr3,
city as city,
st as state,
zip as zip,
v.stationname AS vendor_name,
bmt.mdm_id as media_type_id,
bsmt.mdm_id as sub_media_type_id,
msmt.dan_media_type_name as dan_media_type,
v.accvendorcode AS accvendorcode,
ba.name AS agency_name,
ba.mdm_id AS agency_id,
case when activestatus='Active' then 1 else 0 end AS active,
v.sourceid AS source_id,
v.batchid AS batch_id
from dentsu_national_vendor_unique v
join map_sub_media_type msmt
on lower(msmt.dan_media_type_code) = lower(v.media) and
msmt.dan_toolkit_code = 'NATIONAL'
join base_sub_media_type bsmt
on msmt.fk_base_sub_media_type_id = bsmt.id
join base_media_type bmt
on bmt.id = bsmt.fk_base_media_type_id
join dentsu_vendor_unique dvu
on dvu.externalcode = v.accvendorcode
join base_agency ba on lower(ba.code) = lower(dvu.company)
where length(stationname) > 1
)
union all
(
SELECT DISTINCT
dvu.grp,
dvu.d365vendoraccount as erp_vendor_id,
address as addr1,
address2 as addr2,
address3 as addr3,
'' as city,
'' as state,
'' as zip,
v.payname AS vendor_name,
bmt.mdm_id as media_type_id,
bsmt.mdm_id as sub_media_type_id,
msmt.dan_media_type_name as dan_media_type,
v.accvendorcode AS accvendorcode,
ba.name AS agency_name,
ba.mdm_id AS agency_id,
case when activestatus='Active' then 1 else 0 end AS active,
v.sourceid AS source_id,
v.batchid AS batch_id
from dentsu_print_vendor_unique v
join map_sub_media_type msmt
ON lower(msmt.dan_media_type_name) = lower(v.media) AND
msmt.dan_toolkit_code = 'PRINT'
join base_sub_media_type bsmt
on msmt.fk_base_sub_media_type_id = bsmt.id
join base_media_type bmt
on bmt.id = bsmt.fk_base_media_type_id
join dentsu_vendor_unique dvu
on dvu.externalcode = v.accvendorcode
join base_agency ba on lower(ba.code) = lower(dvu.company)
where length(payname) > 1 and payname <> '..'
)
union all
(
SELECT DISTINCT
dvu.grp,
dvu.d365vendoraccount as erp_vendor_id,
case when clt <> '' then
concat(stationname,' (CLT=',clt,')')
else
stationname
end as addr1,
stationaddress as addr2,
'' as addr3,
city as city,
st as state,
zip as zip,
v.stationname AS vendor_name,
bmt.mdm_id as media_type_id,
bsmt.mdm_id as sub_media_type_id,
msmt.dan_media_type_name as dan_media_type,
v.accvendorcode AS accvendorcode,
ba.name AS agency_name,
ba.mdm_id AS agency_id,
case when activestatus='Active' then 1 else 0 end AS active,
v.sourceid AS source_id,
v.batchid AS batch_id
FROM dentsu_spot_vendor_unique v
JOIN map_sub_media_type msmt
ON lower(msmt.dan_media_type_name) = lower(v.media) AND
msmt.dan_toolkit_code = 'SPOT'
join base_sub_media_type bsmt
on msmt.fk_base_sub_media_type_id = bsmt.id
join base_media_type bmt
on bmt.id = bsmt.fk_base_media_type_id
join dentsu_vendor_unique dvu
on dvu.externalcode = v.accvendorcode
join base_agency ba on lower(ba.code) = lower(dvu.company)
WHERE length(stationname) > 1
)
)
select distinct
case when av.agency_name is not null then
jsonb_agg(json_build_object('agency',av.agency_name,'erp_vendor_id',av.erp_vendor_id))
over (partition by av.accvendorcode)
end as agency_associations,
av.accvendorcode as accvendorcode,
case when av.vendor_name is not null then
string_agg(av.vendor_name, ', ') over (partition by av.accvendorcode)
end as vendor_name,
av.grp as vendor_group_type,
case when av.addr1 is not null then
jsonb_agg(json_build_object(
'addr1', av.addr1,
'addr2', av.addr2,
'addr3', av.addr3,
'city', av.city,
'state', av.state,
'zip', av.zip)) over (partition by av.accvendorcode)
end as addresses,
case when av.dan_media_type is not null then
string_agg(av.dan_media_type, ', ')
over (partition by av.accvendorcode)
end as media_types,
case when av.accvendorcode is not null then
sum(av.active) over (partition by av.accvendorcode) > 0
end as active
from base_vendor_unique base
join all_vendors av
on av.accvendorcode = base.externalcode;
-- VENDORS FULL MAP EXPANDED
with all_vendors as
(
(
SELECT DISTINCT
dvu.grp,
dvu.d365vendoraccount as erp_vendor_id,
stationname as addr1,
stationaddress as addr2,
'' as addr3,
city as city,
st as state,
zip as zip,
v.stationname AS vendor_name,
bmt.mdm_id as media_type_id,
bsmt.mdm_id as sub_media_type_id,
msmt.dan_media_type_name as dan_media_type,
v.accvendorcode AS accvendorcode,
ba.name AS agency_name,
ba.mdm_id AS agency_id,
case when activestatus='Active' then 1 else 0 end AS active,
v.sourceid AS source_id,
v.batchid AS batch_id
from dentsu_national_vendor_unique v
join map_sub_media_type msmt
on lower(msmt.dan_media_type_code) = lower(v.media) and
msmt.dan_toolkit_code = 'NATIONAL'
join base_sub_media_type bsmt
on msmt.fk_base_sub_media_type_id = bsmt.id
join base_media_type bmt
on bmt.id = bsmt.fk_base_media_type_id
join dentsu_vendor_unique dvu
on dvu.externalcode = v.accvendorcode
join base_agency ba on lower(ba.code) = lower(dvu.company)
where length(stationname) > 1
)
union all
(
SELECT DISTINCT
dvu.grp,
dvu.d365vendoraccount as erp_vendor_id,
address as addr1,
address2 as addr2,
address3 as addr3,
'' as city,
'' as state,
'' as zip,
v.payname AS vendor_name,
bmt.mdm_id as media_type_id,
bsmt.mdm_id as sub_media_type_id,
msmt.dan_media_type_name as dan_media_type,
v.accvendorcode AS accvendorcode,
ba.name AS agency_name,
ba.mdm_id AS agency_id,
case when activestatus='Active' then 1 else 0 end AS active,
v.sourceid AS source_id,
v.batchid AS batch_id
from dentsu_print_vendor_unique v
join map_sub_media_type msmt
ON lower(msmt.dan_media_type_name) = lower(v.media) AND
msmt.dan_toolkit_code = 'PRINT'
join base_sub_media_type bsmt
on msmt.fk_base_sub_media_type_id = bsmt.id
join base_media_type bmt
on bmt.id = bsmt.fk_base_media_type_id
join dentsu_vendor_unique dvu
on dvu.externalcode = v.accvendorcode
join base_agency ba on lower(ba.code) = lower(dvu.company)
where length(payname) > 1 and payname <> '..'
)
union all
(
SELECT DISTINCT
dvu.grp,
dvu.d365vendoraccount as erp_vendor_id,
case when clt <> '' then
concat(stationname,' (CLT=',clt,')')
else
stationname
end as addr1,
stationaddress as addr2,
'' as addr3,
city as city,
st as state,
zip as zip,
v.stationname AS vendor_name,
bmt.mdm_id as media_type_id,
bsmt.mdm_id as sub_media_type_id,
msmt.dan_media_type_name as dan_media_type,
v.accvendorcode AS accvendorcode,
ba.name AS agency_name,
ba.mdm_id AS agency_id,
case when activestatus='Active' then 1 else 0 end AS active,
v.sourceid AS source_id,
v.batchid AS batch_id
FROM dentsu_spot_vendor_unique v
JOIN map_sub_media_type msmt
ON lower(msmt.dan_media_type_name) = lower(v.media) AND
msmt.dan_toolkit_code = 'SPOT'
join base_sub_media_type bsmt
on msmt.fk_base_sub_media_type_id = bsmt.id
join base_media_type bmt
on bmt.id = bsmt.fk_base_media_type_id
join dentsu_vendor_unique dvu
on dvu.externalcode = v.accvendorcode
join base_agency ba on lower(ba.code) = lower(dvu.company)
WHERE length(stationname) > 1
)
)
select distinct
av.agency_name,
av.erp_vendor_id,
av.accvendorcode as accvendorcode,
av.vendor_name,
av.grp as vendor_group_type,
av.addr1,
av.addr2,
av.addr3,
av.city,
av.state,
av.zip,
av.dan_media_type,
av.active
from base_vendor_unique base
join all_vendors av
on av.accvendorcode = base.externalcode;
select count(*), billformulabase from dentsu_print_estimate_unique group by billformulabase order by count(*) desc;
select dan_media_type_code, bsmt.name as sub_media_type, bmt.name as metia_type from map_sub_media_type msmt
join base_sub_media_type bsmt on msmt.fk_base_sub_media_type_id = bsmt.id
join base_media_type bmt on bsmt.fk_base_media_type_id = bmt.id
-- NATIONAL
-- 606 unique suppliers
select distinct accvendorcode from dentsu_national_supplier_unique;
-- 704 unique vendors
select distinct accvendorcode from dentsu_national_vendor_unique;
-- 494 unique matched together
select distinct s.accvendorcode from dentsu_national_supplier_unique s
join dentsu_national_vendor v on s.accvendorcode = v.accvendorcode;
-- PRINT
-- 25859 unique suppliers
select distinct accvendorcode from dentsu_print_supplier_unique;
-- 17261 unique vendors
select distinct accvendorcode from dentsu_print_vendor_unique;
-- 15304 unique matched together
select distinct s.accvendorcode from dentsu_print_supplier_unique s
join dentsu_print_vendor v on s.accvendorcode = v.accvendorcode;
-- SPOT
-- 24187 unique suppliers
select distinct accvendorcode from dentsu_spot_supplier_unique;
-- 16263 unique vendors
select distinct accvendorcode from dentsu_spot_vendor_unique;
-- 15648 unique matched together
select distinct s.accvendorcode from dentsu_spot_supplier_unique s
join dentsu_spot_vendor v on s.accvendorcode = v.accvendorcode;
-- 50652 (total unique raw suppliers)
-- 34228 (total unique raw vendors)
-- 31446 (total vendor matches to suppliers raw)
-- 31446 total suppliers matched to vendors unioned
-- 4860 total supplier/vendors with transactional records in D365
WITH all_suppliers as (
SELECT distinct s.accvendorcode, bsmt.name as dan_sub_media_type
FROM dentsu_national_supplier_unique s
join dentsu_national_vendor v on s.accvendorcode = v.accvendorcode
JOIN map_supplier_sub_media_type msmt ON msmt.dan_sub_media_type_code = s.media
join base_sub_media_type bsmt on msmt.fk_base_sub_media_type_id = bsmt.id
UNION all
SELECT distinct s.accvendorcode, bsmt.name as dan_sub_media_type
FROM dentsu_print_supplier_unique s
join dentsu_print_vendor v on s.accvendorcode = v.accvendorcode
JOIN map_sub_media_type msmt ON upper(msmt.dan_media_type_name) = upper(s.media)
join base_sub_media_type bsmt on msmt.fk_base_sub_media_type_id = bsmt.id
UNION all
SELECT distinct s.accvendorcode, bsmt.name as dan_sub_media_type
FROM dentsu_spot_supplier_unique s
join dentsu_spot_vendor v on s.accvendorcode = v.accvendorcode
JOIN map_sub_media_type msmt ON upper(msmt.dan_media_type_name) = upper(s.media)
join base_sub_media_type bsmt on msmt.fk_base_sub_media_type_id = bsmt.id
)
select s.accvendorcode, s.dan_sub_media_type from all_suppliers s
join base_vendor_unique v on s.accvendorcode = v.externalcode;
-- SUPPLIERS FULL MAP
WITH sup_media AS (
SELECT accvendorcode, fk_base_sub_media_type_id
FROM dentsu_national_supplier_unique n
JOIN map_supplier_sub_media_type mssm ON mssm.dan_sub_media_type_code = n.media
UNION
SELECT accvendorcode, fk_base_sub_media_type_id
FROM dentsu_print_supplier_unique p
JOIN map_sub_media_type msmt ON upper(msmt.dan_media_type_name) = upper(p.media)
UNION
SELECT accvendorcode, fk_base_sub_media_type_id
FROM dentsu_spot_supplier_unique s
JOIN map_sub_media_type msmt ON upper(msmt.dan_media_type_name) = upper(s.media)),
sup_media_grouped AS (
SELECT accvendorcode, array_agg(bsmt.mdm_id) AS sub_media_type_ids
FROM sup_media sm
JOIN base_sub_media_type bsmt ON sm.fk_base_sub_media_type_id = bsmt.id
GROUP BY accvendorcode),
sup_address_union AS (
SELECT accvendorcode,
stationname AS name,
city AS address_city,
'' AS address_country,
TRUE AS address_active,
TRUE AS address_primary,
'MAIN' AS address_type,
zip AS address_postal_code,
st AS address_state,
stationaddress AS address_street1,
'' AS address_street2,
'' AS address_street3
FROM dentsu_national_supplier_unique
WHERE stationname NOT LIKE '%NO ADDRESS RECORD%'
UNION ALL
SELECT accvendorcode,
pubname AS name,
NULL AS address_city,
NULL AS address_country,
TRUE AS address_active,
TRUE AS address_primary,
'MAIN' AS address_type,
NULL AS address_postal_code,
NULL AS address_state,
address AS address_street1,
address2 AS address_street2,
address3 AS address_street3
FROM dentsu_print_supplier_unique
WHERE address <> '.'
AND address <> '0'
UNION ALL
SELECT accvendorcode,
stationname AS name,
city AS address_city,
'' AS address_country,
TRUE AS address_active,
TRUE AS address_primary,
'MAIN' AS address_type,
zip AS address_postal_code,
st AS address_state,
stationaddress AS address_street1,
'' AS address_street2,
'' AS address_street3
FROM dentsu_spot_supplier_unique
WHERE stationname NOT LIKE '%NO ADDRESS RECORD%'
AND stationname <> ''),
sup_address_partitioned AS (
SELECT *, row_number() OVER (PARTITION BY accvendorcode) AS row_num
FROM sup_address_union),
sup_address_by_code AS (
SELECT accvendorcode,
name,
address_city,
address_country,
address_active,
address_primary,
address_type,
address_postal_code,
address_state,
address_street1,
address_street2,
address_street3
FROM sup_address_partitioned
WHERE row_num = 1),
sup_status AS (
SELECT base.vendorcode,
nat.activestatus = 'Active'
OR print.activestatus = 'Active'
OR spot.activestatus = 'Active' AS active,
row_number() OVER (PARTITION BY base.vendorcode
ORDER BY CASE
WHEN (nat.activestatus = 'Active'
OR print.activestatus = 'Active'
OR spot.activestatus = 'Active') THEN 0
ELSE 1 END) AS row_num
FROM base_supplier_unique base
LEFT JOIN dentsu_national_supplier_unique nat ON base.vendorcode = nat.accvendorcode
LEFT JOIN dentsu_print_supplier_unique print ON base.vendorcode = print.accvendorcode
LEFT JOIN dentsu_spot_supplier_unique spot ON base.vendorcode = spot.accvendorcode),
sup_status_by_code AS (
SELECT vendorcode, active
FROM sup_status
WHERE row_num = 1)
SELECT su.id AS id,
addr.name,
su.vendorcode AS code,
COALESCE(addr.address_city, '') AS address_city,
COALESCE(addr.address_country, '') AS address_country,
addr.address_active,
addr.address_primary,
addr.address_type,
COALESCE(addr.address_postal_code, '') AS address_postal_code,
COALESCE(addr.address_state, '') AS address_state,
COALESCE(addr.address_street1, '') AS address_street1,
COALESCE(addr.address_street2, '') AS address_street2,
COALESCE(addr.address_street3, '') AS address_street3,
stat.active,
smg.sub_media_type_ids,
'' AS localized_name,
NULL AS logo,
v.mdm_id AS vendor,
su.sourceid AS source_id,
su.batchid AS batch_id
FROM base_supplier_unique su
JOIN sup_media_grouped smg ON su.vendorcode = smg.accvendorcode
JOIN base_vendor_unique v ON su.vendorcode = v.externalcode
JOIN sup_address_by_code addr ON su.vendorcode = addr.accvendorcode
JOIN sup_status_by_code stat USING (vendorcode)
-- WHERE su.mdm_id IS NULL and
-- v.mdm_id is not null
;
-- VENDORS SUPPLIERS_FULL MAP
with all_vendors as
(
(
SELECT DISTINCT
dvu.grp,
dvu.d365vendoraccount as erp_vendor_id,
stationname as addr1,
stationaddress as addr2,
'' as addr3,
city as city,
st as state,
zip as zip,
v.stationname AS vendor_name,
bmt.mdm_id as media_type_id,
bsmt.mdm_id as sub_media_type_id,
msmt.dan_media_type_name as dan_media_type,
v.accvendorcode AS accvendorcode,
ba.name AS agency_name,
ba.mdm_id AS agency_id,
case when activestatus='Active' then 1 else 0 end AS active,
v.sourceid AS source_id,
v.batchid AS batch_id
from dentsu_national_vendor_unique v
join map_sub_media_type msmt
on lower(msmt.dan_media_type_code) = lower(v.media) and
msmt.dan_toolkit_code = 'NATIONAL'
join base_sub_media_type bsmt
on msmt.fk_base_sub_media_type_id = bsmt.id
join base_media_type bmt
on bmt.id = bsmt.fk_base_media_type_id
join dentsu_vendor_unique dvu
on dvu.externalcode = v.accvendorcode
join base_agency ba on lower(ba.code) = lower(dvu.company)
where length(stationname) > 1
)
union all
(
SELECT DISTINCT
dvu.grp,
dvu.d365vendoraccount as erp_vendor_id,
address as addr1,
address2 as addr2,
address3 as addr3,
'' as city,
'' as state,
'' as zip,
v.payname AS vendor_name,
bmt.mdm_id as media_type_id,
bsmt.mdm_id as sub_media_type_id,
msmt.dan_media_type_name as dan_media_type,
v.accvendorcode AS accvendorcode,
ba.name AS agency_name,
ba.mdm_id AS agency_id,
case when activestatus='Active' then 1 else 0 end AS active,
v.sourceid AS source_id,
v.batchid AS batch_id
from dentsu_print_vendor_unique v
join map_sub_media_type msmt
ON lower(msmt.dan_media_type_name) = lower(v.media) AND
msmt.dan_toolkit_code = 'PRINT'
join base_sub_media_type bsmt
on msmt.fk_base_sub_media_type_id = bsmt.id
join base_media_type bmt
on bmt.id = bsmt.fk_base_media_type_id
join dentsu_vendor_unique dvu
on dvu.externalcode = v.accvendorcode
join base_agency ba on lower(ba.code) = lower(dvu.company)
where length(payname) > 1 and payname <> '..'
)
union all
(
SELECT DISTINCT
dvu.grp,
dvu.d365vendoraccount as erp_vendor_id,
case when clt <> '' then
concat(stationname,' (CLT=',clt,')')
else
stationname
end as addr1,
stationaddress as addr2,
'' as addr3,
city as city,
st as state,
zip as zip,
v.stationname AS vendor_name,
bmt.mdm_id as media_type_id,
bsmt.mdm_id as sub_media_type_id,
msmt.dan_media_type_name as dan_media_type,
v.accvendorcode AS accvendorcode,
ba.name AS agency_name,
ba.mdm_id AS agency_id,
case when activestatus='Active' then 1 else 0 end AS active,
v.sourceid AS source_id,
v.batchid AS batch_id
FROM dentsu_spot_vendor_unique v
JOIN map_sub_media_type msmt
ON lower(msmt.dan_media_type_name) = lower(v.media) AND
msmt.dan_toolkit_code = 'SPOT'
join base_sub_media_type bsmt
on msmt.fk_base_sub_media_type_id = bsmt.id
join base_media_type bmt
on bmt.id = bsmt.fk_base_media_type_id
join dentsu_vendor_unique dvu
on dvu.externalcode = v.accvendorcode
join base_agency ba on lower(ba.code) = lower(dvu.company)
WHERE length(stationname) > 1
)
)
select distinct
av.agency_name,
av.erp_vendor_id,
av.accvendorcode as accvendorcode,
av.vendor_name,
av.grp as vendor_group_type,
av.addr1,
av.addr2,
av.addr3,
av.city,
av.state,
av.zip,
av.dan_media_type,
av.active
from base_vendor_unique base
join all_vendors av
on av.accvendorcode = base.externalcode;
--BILLING PROFILES
select p.profile profile_name,
p.fa_id profile_id,
case when p.media_id is not null then bmt.name else 'all media types' end as media_type,
case when p.sub_media_id is not null then bsmt.name else 'all sub media types' end as sub_media_type,
case when p.client_id is not null then c.client_code else 'all' end as client_code,
case when p.client_id is not null then c.name else 'all clients' end as client_name,
case when p.agency_id is not null then a.name else 'all agencies' end as agency,
p.effective_start_date,
p.cleared_amounts_only,
p.calendar_definition,
p.bill_due_in,
p.bill_separation_setting,
p.print_detail_level,
p.page_break_level,
p.include_prior_months
from public.base_billing_profile p
left join public.base_client_unique c on c.id = p.client_id
left join public.base_agency a on a.id = p.agency_id
left join public.base_media_type bmt on bmt.id = p.media_id
left join public.base_sub_media_type bsmt on bsmt.id = p.sub_media_id;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment