116 lines
3.4 KiB
SQL
116 lines
3.4 KiB
SQL
WITH
|
|
----------sequence the volume breaks and assume that the second one is the master pallet volume-------------------
|
|
--notes: this is approach is dubious but requested ASAP by customer service as required by customer---------------
|
|
---------pre-aggregate the minimum order quantity for active plants-----------------------------------------------
|
|
active_mins AS (
|
|
SELECT
|
|
AVPART part
|
|
,MAX(cast(round(v6moqt,0) as int)) mino
|
|
FROM
|
|
LGDAT.STKMM
|
|
LEFT OUTER JOIN lgdat.stka a ON
|
|
a.v6part = avpart
|
|
AND a.v6plnt IN ('112','113','152','154','155')
|
|
AND a.v6stat = 'A'
|
|
GROUP BY
|
|
AVPART
|
|
---------------------
|
|
UNION
|
|
---------------------
|
|
SELECT
|
|
AWPART part
|
|
,MAX(cast(round(v6moqt,0) as int)) mino
|
|
FROM
|
|
LGDAT.STKMP
|
|
LEFT OUTER JOIN lgdat.stka a ON
|
|
a.v6part = awpart
|
|
AND a.v6plnt IN ('112','113','152','154','155')
|
|
AND a.v6stat = 'A'
|
|
GROUP BY
|
|
AWPART
|
|
)
|
|
,raw AS (
|
|
SELECT
|
|
substring(japlds, 1, 1)||substring(japlds, 23, 1) pricelist
|
|
,a.japlcd
|
|
,c.JCPART
|
|
,i.DESCR
|
|
,i.DESCR2
|
|
,c.JCUNIT
|
|
,c.JCPRIC
|
|
,c.JCVOLL
|
|
,i.UOMP
|
|
,am.MINO
|
|
,i.CLSS
|
|
,RTRIM(i.ACC_LIST) ACC_LIST
|
|
,row_number() OVER (PARTITION BY japlcd, jcpart ORDER BY jcvoll ASC) SEQ
|
|
FROM
|
|
lgdat.iprcc c
|
|
INNER JOIN lgdat.iprca a ON
|
|
a.JAPLCD = c.JCPLCD
|
|
LEFT OUTER JOIN "CMS.CUSLG".itemm i ON
|
|
i.item = c.jcpart
|
|
LEFT OUTER JOIN active_mins am ON
|
|
am.part = c.jcpart
|
|
WHERE
|
|
a.japlcd LIKE ('X%')
|
|
--AND jcpart = 'FCT10400RBRD010'
|
|
)
|
|
----------start with only all sequence 1 items and then rejoin to get the 2nd break as a new column----------------
|
|
,pivot AS (
|
|
SELECT
|
|
w.pricelist
|
|
,w.japlcd
|
|
,w.JCPART
|
|
,w.DESCR
|
|
,w.DESCR2
|
|
,w.ACC_LIST
|
|
,w.JCUNIT
|
|
,w.JCPRIC
|
|
,w.seq
|
|
,w.uomp
|
|
,w.MINO
|
|
,w.CLSS
|
|
,COALESCE(c.jcvoll/w.jcvoll,1) cse_per_plt
|
|
,w.JCPRIC cse_price_m
|
|
,c.JCPRIC plt_price_m
|
|
,round(w.JCPRIC*w.jcvoll,2) cse_price_pkg
|
|
,round(c.JCPRIC*c.jcvoll,2) plt_price_pkg
|
|
,round(CASE WHEN w.uomp = 'PLT' THEN w.JCPRIC*w.JCVOLL ElSE CAST(null as numeric) END,2) plt_price_plt
|
|
,round(CASE WHEN w.uomp <> 'PLT' THEN cast(c.JCPRIC*C.JCVOLL as numeric)/cast(c.jcvoll/w.jcvoll as numeric) ElSE CAST(null as numeric) END,2) plt_price_cse
|
|
FROM
|
|
raw w
|
|
LEFT OUTER JOIN raw c ON
|
|
c.JCPART = w.JCPART
|
|
AND c.seq = 2
|
|
AND c.japlcd = w.japlcd
|
|
WHERE
|
|
w.SEQ = 1
|
|
ORDER BY
|
|
w.jcpart
|
|
,w.seq
|
|
)
|
|
------------pretty column names-----------------------------------------
|
|
SELECT
|
|
pricelist "Price List"
|
|
,rtrim(JCPART) "Product"
|
|
,DESCR "Description1"
|
|
,DESCR2 "Description2"
|
|
,ACC_LIST "Included Accessories"
|
|
,JCUNIT "UOM"
|
|
,JCPRIC "Price"
|
|
,cse_per_plt "Cases Per Pallet"
|
|
,CSE_PRICE_M "Single Case/Bulk Price Per M"
|
|
,PLT_PRICE_M "Master Pallet Price Per M"
|
|
,CSE_PRICE_PKG "Case/Bulk Price Per Package"
|
|
,PLT_PRICE_PKG "Master Pallet Price Per Pallet"
|
|
,PLT_PRICE_PLT "Bulk Pallet Price per Pallet"
|
|
,PLT_PRICE_CSE "Master Pallet Price Per Case"
|
|
,mino "Non-Stocking Minimum"
|
|
,clss "Inventory Class"
|
|
,japlcd "delete this column"
|
|
FROM
|
|
pivot
|
|
ORDER BY
|
|
PRICELIST
|
|
,JCPART |