363 lines
16 KiB
PL/PgSQL
363 lines
16 KiB
PL/PgSQL
BEGIN;
|
|
|
|
WITH
|
|
targ AS (
|
|
SELECT
|
|
account
|
|
,segment
|
|
,targv::numeric targv
|
|
,targm::numeric targm
|
|
,targv::numeric - targm::numeric targc
|
|
FROM (VALUES
|
|
('THE HOME DEPOT','17000000','3074775','Retail'),
|
|
('L&L NURSERY SUPPLY INC','2350000','1323646','Retail'),
|
|
('WAL-MART','0','0','Retail'),
|
|
('FRED MEYER','2000000','856002','Retail'),
|
|
('AMAZON.COM','1640000','1030332','Retail'),
|
|
('BFG SUPPLY','1500000','836573','Retail'),
|
|
('MENARDS','1638401','608147','Retail'),
|
|
('ARETT SALES','1241808','664301','Retail'),
|
|
('VM INNOVATIONS-NE','1000000','550614','Retail'),
|
|
('CANADIAN TIRE CORP LTD','680606','47642','Retail'),
|
|
('CANADIAN TIRE CORP LTD','319394','160394','Sustainable'),
|
|
('ALLIANCE INTERNATIONAL LLC','500000','206602','Retail'),
|
|
('OCEAN STATE JOBBERS','300000','155878','Retail'),
|
|
('GRIFFIN','258240','118623','Retail'),
|
|
('DO IT BEST','297067','177889','Retail'),
|
|
('BWI','249061','117374','Retail'),
|
|
('BRADLEY CALDWELL INC','226211','134560','Retail'),
|
|
('PHILLIPS PET FOOD & SUPPLIES','194414','113853','Retail'),
|
|
('ACE HARDWARE','195043','128460','Retail'),
|
|
('EDDIS WHOLESALE GDN SUPPLIES','156305','76972','Retail'),
|
|
('GARD N WISE','154657','83291','Retail'),
|
|
('FOSTERS INC','154278','84043','Retail'),
|
|
('GARDEN CENTRE GROUP CO-OP','148400','79640','Retail'),
|
|
('TRUE VALUE','125272','66837','Retail'),
|
|
('RONA INC (PICK UP)ACE CANADA','125154','49123','Retail'),
|
|
('HB DAVIS SEED CO','103990','60800','Retail'),
|
|
('BOMGAARS SUPPLY INC. RETAIL','96550','55201','Retail'),
|
|
('CANAC MARQUIS GRENIER','84336','26284','Retail'),
|
|
('LOWES','82640','33445','Retail'),
|
|
('JENSEN DISTRIBUTION','79189','47567','Retail'),
|
|
('EARL MAY SEED','77321','45084','Retail'),
|
|
('INTERMOUNTAIN FARMERS ASSN','71161','25896','Retail'),
|
|
('PEAVEY INDUSTRIES LP RET','63755','38070','Retail'),
|
|
('SRC CORPORATION','62610','34107','Retail'),
|
|
('HORIZON DISTRIBUTION INC','52200','29953','Retail'),
|
|
('HALIFAX SEED CO INC','47859','23365','Retail'),
|
|
('JAVIC STEIN GARDEN CENTER RET','47332','29831','Retail'),
|
|
('TERIS SUPPLY SERVICES','46454','21876','Retail'),
|
|
('LITTLE MOUNTAIN GREENHOUSES','0','0','Retail'),
|
|
('WYATT QUARLES','45961','19806','Retail'),
|
|
('KROGER ACCOUNTING SERVICES','0','0','Retail'),
|
|
('AURORA WHOLESALERS, LLC','39005','1950','Retail'),
|
|
('CIMARRON LUMBER CO','41255','29321','Retail'),
|
|
('SKH WHOLESALE DIST','39498','22002','Retail'),
|
|
('GREENHOUSE MEGASTORE','38481','22165','Retail'),
|
|
('MEYER SEED CO.OF BALTIMORE INC','36907','21255','Retail'),
|
|
('GREEN ISLAND DISTRIBUTORS INC.','36335','16598','Retail'),
|
|
('ALASKA GDN & PET SUPPLY RET','34416','17673','Retail'),
|
|
('CY GROWERS SUPPLIES LTD','33516','17268','Retail'),
|
|
('PL ROHRER & BRO INC RET','32682','18721','Retail'),
|
|
('CISCO COMPANIES (THE)','31294','16958','Retail'),
|
|
('EXACTA SALES','29985','16997','Retail'),
|
|
('CARLIN HORTICULTURAL SUPP','29766','17926','Retail'),
|
|
('LEON KOROL COMPANY','27634','-20540','Retail'),
|
|
('FAMILY TREE NURSERY','26732','14253','Retail'),
|
|
('GREENHOUSE & GARDEN SUPPLY INC','28432','16180','Retail'),
|
|
('MIKES GARDEN CENTER','24804','8682','Retail'),
|
|
('HORTMARK','24148','14260','Retail'),
|
|
('FLORIDA HARDWARE COMPANY','22952','13416','Retail'),
|
|
('EARL''S FARM & GARDEN INC','20856','11680','Retail'),
|
|
('MIZE FARM & GARDEN SUPPLY','20317','11079','Retail'),
|
|
('HUMMERT INTERNATIONAL','19214','10602','Retail'),
|
|
('BAYERS GARDEN SHOP','18177','11247','Retail'),
|
|
('FEDERATED CO-OPERATIVES LTD','16347','9370','Retail'),
|
|
('VANDENBERG BULB CO INC','15895','8533','Retail'),
|
|
('RS GROWERS SUPPLY','15676','8691','Retail'),
|
|
('PLANTERS SEED & SPICE RET','15395','8520','Retail'),
|
|
('UNITED GARDEN CENTER','15303','8117','Retail'),
|
|
('NORTH 40 OUTFITTERS','13975','9093','Retail'),
|
|
('ERVA TOOL & DIE CO','13102','10114','Retail'),
|
|
('NITRO-PHOS FERTILIZERS','12772','7397','Retail'),
|
|
('OTTEN BROS','12390','6556','Retail'),
|
|
('GROSOUTH','12229','5783','Retail'),
|
|
('WAYFAIR LLC','10894','6861','Retail'),
|
|
('PRINCE CORPORATION','10627','6478','Retail'),
|
|
('BLISH-MIZE CO','10502','6181','Retail'),
|
|
('UNITED GARDEN CENTERS RET','10323','5880','Retail'),
|
|
('KC SCHAEFER SUPPLY','10176','5658','Retail'),
|
|
('LIFOAM INDUSTRIES LLC','9922','1437','Retail'),
|
|
('CARL BREHOB & SON','9909','4862','Retail'),
|
|
('GPF CORPORATION','8497','4907','Retail'),
|
|
('APACHE SEEDS LTD','7524','4823','Retail'),
|
|
('PRO GROWER SUPPLY','7160','4488','Retail'),
|
|
('ALTON GREENHOUSES','6282','4571','Retail'),
|
|
('VG SUPPLY CO INC','6170','3302','Retail'),
|
|
('PATRICK MORIN','5550','4185','Retail'),
|
|
('PROFESSIONAL GARDENER CO','5534','3780','Retail'),
|
|
('PREMIUM HORTICULTURAL SUPPLY','5260','2953','Retail'),
|
|
('SCHROTH WHOLESALE SUPPLY CO.','4886','2841','Retail'),
|
|
('WALDO & ASSOCIATES','4848','2662','Retail'),
|
|
('SERRES STE-ELIE','4554','2217','Retail'),
|
|
('JEAN H HENLE INC RET','4390','2596','Retail'),
|
|
('GOLDCREST DIST','4203','2652','Retail'),
|
|
('KEN VAN WINGERDEN GREENHOUSES','3934','2721','Retail'),
|
|
('PASTANCH LLC','3558','2300','Retail'),
|
|
('TESSMAN SEED','3490','1837','Retail'),
|
|
('GEMMELLS GARDEN CENTRE INC','3356','2459','Retail'),
|
|
('BELLE INVESTMENTS INC','2969','1901','Retail'),
|
|
('BLUE STAR WHOLESALE','2870','1321','Retail'),
|
|
('GLEN ECHO NURSERIES INC','2856','1358','Retail'),
|
|
('Distribution','2856','1000','Retail'),
|
|
('EZ GRO GARDEN','2770','970','Retail'),
|
|
('ENVIROGREENERY PLANTS LLC','2759','1546','Retail'),
|
|
('GERTENS GREENHOUSE','2669','934','Retail'),
|
|
('KROGER (KRG LLC)','2363','1074','Retail'),
|
|
('CINNABAR VALLEY','1873','1210','Retail'),
|
|
('EDWARDS GREENHOUSE INC','1842','1179','Retail'),
|
|
('DIRECT DISTRIBUTORS INC','1732','1140','Retail'),
|
|
('YODERS PRODUCE','1295','514','Retail'),
|
|
('ABC NURSERY LANDSCAPEMAIN','556','305','Retail'),
|
|
('DUNN SALES & MARKETING, INC.','385','197','Retail'),
|
|
('GARDENERS SUPPLY CO','375','263','Retail'),
|
|
('FARMERS COOP','355','135','Retail'),
|
|
('CROP PROD SERVICES INC','351','199','Retail'),
|
|
('ARNOLDS GREENHOUSES','309','249','Retail'),
|
|
('GEORGE''S FLOWERS','287','132','Retail'),
|
|
('HJS WHOLESALE LTD','162','117','Retail'),
|
|
('HOLMBERG FARMS INC','101','72','Retail'),
|
|
('KINNEY BONDED','93','49','Retail'),
|
|
('IRRIGATION PLUS INC','35','21','Retail'),
|
|
('GLENKIRK ASSOCIATES INC','0','0','Retail'),
|
|
('MILITARY PRODUCE GROUP LLC','0','0','Retail'),
|
|
('WONDER SOIL','0','0','Retail'),
|
|
('CHUCK BECK','0','0','Retail'),
|
|
('MENARDS INC STORE 3256 RET','0','0','Retail'),
|
|
('GREENSTAR PLANT-MANCHESTER','0','0','Retail'),
|
|
('MAIN LINE POTTERY','0','0','Retail'),
|
|
('PSR SALES INC','0','0','Retail'),
|
|
('CLEVELAND FLORAL PRODUCTS INC','0','0','Retail'),
|
|
('RACHEL BOWMAN','0','0','Retail'),
|
|
('ENG SVCS & PROD','0','0','Retail'),
|
|
('RITCHIE FEED & SEED','0','0','Retail'),
|
|
('PARK SEED (DBA JPPA)','0','0','Retail'),
|
|
('ART KNAPP','0','0','Retail'),
|
|
('VARIETY WHOLESALE (9001)','0','0','Retail'),
|
|
('ROY SIEMENS','0','0','Retail'),
|
|
('MARC DESROSIERS','0','0','Retail'),
|
|
('JIM REGER','0','0','Retail'),
|
|
('MARK WILKINSON','0','0','Retail'),
|
|
('SAN MARCOS GROWERS','0','0','Retail'),
|
|
('GRANBYS GREENHOUSE','0','0','Retail'),
|
|
('GIANT TIGERE STORES LIMITED','0','0','Retail'),
|
|
('INTEGRITY SALES & DISTRIBUTION','0','0','Retail'),
|
|
('GLOECKNER & CO. INC.','0','0','Retail'),
|
|
('DORAN MARABLE','0','0','Retail'),
|
|
('SEVEN OAKS PLANT SHOP INC','0','0','Retail'),
|
|
('BURPEE GARDEN PRODUCTS RET','0','0','Retail'),
|
|
('SHERRILL INC','0','0','Retail'),
|
|
('PARKWAY GARDENS','0','0','Retail'),
|
|
('GS DISTRIBUTION','0','0','Retail'),
|
|
('GALES GARDEN CENTER','0','0','Retail'),
|
|
('DAVE BODO','0','0','Retail'),
|
|
('FLOWERLAND GARDEN CTR OF CLEVE','0','0','Retail'),
|
|
('STACK-A-POTS','0','0','Retail'),
|
|
('BEAUTIFUL LAND PRODUCTS','0','0','Retail'),
|
|
('STRADERS','0','0','Retail'),
|
|
('WESTLAND GREENHOUSES','0','0','Retail'),
|
|
('SUNNYSIDE GREENHOUSES LTD. RET','0','0','Retail'),
|
|
('DM COLOR EXPRESS','0','0','Retail'),
|
|
('TERIS SVSC D''''APPROVISIONNEMNT','0','0','Retail'),
|
|
('YONAS GREENHOUSES','0','0','Retail'),
|
|
('FLORALIES JOUVENCE','0','0','Retail'),
|
|
('ALECIA LATINI','0','0','Retail'),
|
|
('AL PAR PEAT CO','0','0','Retail'),
|
|
('IVAN VANDERDEEN','0','0','Retail'),
|
|
('TIDBURY CREEK FARMS','0','0','Retail'),
|
|
('AGRO PRODUCTS LIMITED','0','0','Retail'),
|
|
('ALDI','0','0','Retail'),
|
|
('MISC ACCT - (US )','0','0','Retail'),
|
|
('TIMBERS MARKETING LLC','0','0','Retail'),
|
|
('ALLEN STERLING AND LOTHROP','0','0','Retail'),
|
|
('TINA PETTIGREW','0','0','Retail'),
|
|
('TONY LANDINO','0','0','Retail'),
|
|
('TRICIA PRICE','0','0','Retail'),
|
|
('PEAK DISTRIBUTION LLC','0','0','Retail'),
|
|
('DILLON SEED & SUPPLY','0','0','Retail'),
|
|
('MENARDS INC STORE 3510 RET','0','0','Retail'),
|
|
('PLANT BEST','0','0','Retail'),
|
|
('DENVER WHOLESALE FLORISTS CO','0','0','Retail'),
|
|
('TSC STORES LIMITED','0','0','Retail'),
|
|
('CENTRAL GARDEN & PET','0','0','Retail'),
|
|
('ESERRO','0','0','Retail'),
|
|
('AMA PLASTICS','0','0','Retail'),
|
|
('LITTLE MOUNTAIN GREENHOUSES U','0','0','Retail'),
|
|
('PATAPSCO VALLEY SALES','0','0','Retail'),
|
|
('UNITED HARDWARE','0','0','Retail'),
|
|
('COLOUR PARADISE GRHS LTD','0','0','Retail'),
|
|
('GARDEN RIDGE CORP','0','0','Retail'),
|
|
('VARIETY DISTRIBUTORS INC','0','0','Retail'),
|
|
('BLUE WATER BALTIMORE','0','0','Retail'),
|
|
('LEE VALLEY TOOLS','0','0','Retail')
|
|
) x(account,targv,targm,segment)
|
|
)
|
|
,factor AS (
|
|
SELECT
|
|
account
|
|
,segment
|
|
,targv
|
|
,targm
|
|
,targc
|
|
,sum(value_usd) value_usd
|
|
,sum(units) units
|
|
,sum(cost_usd) cost_usd
|
|
,round(CASE WHEN sum(value_usd) = 0 THEN 0 ELSE targv::numeric/sum(value_usd) END,5) vfactor
|
|
,round(CASE WHEN sum(cost_usd) = 0 THEN 0 ELSE targc::numeric/sum(cost_usd) END,5) cfactor
|
|
FROM
|
|
targ t
|
|
LEFT OUTER JOIN rlarp.osm_pool o ON
|
|
o.billto_group = t.account
|
|
AND o.segm = t.segment
|
|
AND o.order_season = 2022
|
|
GROUP BY
|
|
account
|
|
,segment
|
|
,targv
|
|
,targm
|
|
,targc
|
|
)
|
|
--SELECT * FROM agg_curr
|
|
----------------create a log entry--------------------
|
|
,log AS (
|
|
INSERT INTO
|
|
rlarp.osm_log(doc)
|
|
SELECT
|
|
$${
|
|
"message":"force accounts to match target total value and margin",
|
|
"tag":"retail plug",
|
|
"type":"build"
|
|
}$$::jsonb doc
|
|
RETURNING *
|
|
)
|
|
-------build adjustment rows collapsed for all iterations----------
|
|
,dv AS (
|
|
SELECT
|
|
o.fspr
|
|
,o.plnt ---master data
|
|
,o.promo --history date mix
|
|
,o.terms
|
|
,o.bill_cust_descr --history cust mix
|
|
,o.ship_cust_descr --history cust mix
|
|
,o.dsm
|
|
,o.quota_rep_descr --master data
|
|
,o.director
|
|
,o.billto_group --master data
|
|
,o.shipto_group
|
|
,o.chan --master data
|
|
,o.chansub
|
|
,o.chan_retail
|
|
,o.part
|
|
,o.part_descr
|
|
,o.part_group
|
|
,o.branding
|
|
,o.majg_descr
|
|
,o.ming_descr
|
|
,o.majs_descr
|
|
,o.mins_descr
|
|
,o.segm
|
|
,o.substance
|
|
,o.fs_line --master data
|
|
,o.r_currency --history cust mix
|
|
,o.r_rate --master data
|
|
,o.c_currency --master data
|
|
,o.c_rate --master data
|
|
,round(sum(o.units*s.cfactor - o.units ),2) units
|
|
,round(sum(o.value_loc*s.vfactor - o.value_loc ),2) value_loc
|
|
,round(sum(o.value_usd*s.vfactor - o.value_usd ),2) value_usd
|
|
,round(sum(o.cost_loc*s.cfactor - o.cost_loc ),2) cost_loc
|
|
,round(sum(o.cost_usd*s.cfactor - o.cost_usd ),2) cost_usd
|
|
,o.calc_status --0
|
|
,o.flag --0
|
|
,o.order_date --history date mix
|
|
,o.order_month
|
|
,o.order_season
|
|
,o.request_date --history date mix
|
|
,o.request_month
|
|
,o.request_season
|
|
,o.ship_date --history date mix
|
|
,o.ship_month
|
|
,o.ship_season
|
|
,'b22' aS version
|
|
,'upload volume' iter
|
|
,log.id
|
|
,coalescE(log.doc->>'tag','') AS "tag"
|
|
,log.doc->>'message' AS "comment"
|
|
,log.doc->>'type' module
|
|
FROM
|
|
rlarp.osm_pool o
|
|
INNER JOIN factor s ON
|
|
s.account = o.billto_group
|
|
CROSS JOIN log
|
|
WHERE
|
|
o.ship_season = 2022
|
|
AND o.segm = s.segment
|
|
GROUP BY
|
|
o.fspr
|
|
,o.plnt ---master data
|
|
,o.promo --history date mix
|
|
,o.terms
|
|
,o.bill_cust_descr --history cust mix
|
|
,o.ship_cust_descr --history cust mix
|
|
,o.dsm
|
|
,o.quota_rep_descr --master data
|
|
,o.director
|
|
,o.billto_group --master data
|
|
,o.shipto_group
|
|
,o.chan --master data
|
|
,o.chansub
|
|
,o.chan_retail
|
|
,o.part
|
|
,o.part_descr
|
|
,o.part_group
|
|
,o.branding
|
|
,o.majg_descr
|
|
,o.ming_descr
|
|
,o.majs_descr
|
|
,o.mins_descr
|
|
,o.segm
|
|
,o.substance
|
|
,o.fs_line --master data
|
|
,o.r_currency --history cust mix
|
|
,o.r_rate --master data
|
|
,o.c_currency --master data
|
|
,o.c_rate --master data
|
|
,o.calc_status --0
|
|
,o.flag --0
|
|
,o.order_date --history date mix
|
|
,o.order_month
|
|
,o.order_season
|
|
,o.request_date --history date mix
|
|
,o.request_month
|
|
,o.request_season
|
|
,o.ship_date --history date mix
|
|
,o.ship_month
|
|
,o.ship_season
|
|
,log.id
|
|
,coalescE(log.doc->>'tag','')
|
|
,log.doc->>'message'
|
|
,log.doc->>'type'
|
|
)
|
|
INSERT INTO rlarp.osm_pool SELECT * FROM dv;
|
|
--SELECT
|
|
-- billto_group
|
|
-- ,sum(value_usd) val
|
|
-- ,sum(cost_usd) AS cost
|
|
-- ,sum(units) units
|
|
--FROM
|
|
-- dv
|
|
--GROUP BY
|
|
-- billto_group
|
|
|
|
COMMIT;
|