1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90
| SELECT sale.period_dt "PERIOD_DT",
sale.period_sw "PERIOD_SW",
p.rgd_sell_price_bl "RGD_FLAG",
p.but_id "BUT_ID",
bu.nm "BU_NAME",
p.prf_id "PRF_ID",
prodfam.nm "FAMILY_NAME",
CASE
WHEN sale.but_id = '01' THEN
'All'
ELSE
p.prd_id
END "PRD_ID",
CASE
WHEN sale.but_id = '01' THEN
'All'
ELSE
product_desc.nm
END "PRODUCT_NAME",
cont.id "CONTINENT_ID",
cont.nm "CONTINENT_NAME",
NULL "ZONE_ID",
NULL "ZONE_NAME",
SUM(sale.can) "CAN",
SUM(
CASE
WHEN sale.but_id = '01' THEN sale.qty
ELSE sale.m2e
END) "VOLUME",
ROUND(decode(SUM(
CASE
WHEN sale.but_id = '01' THEN sale.qty
ELSE sale.m2e
END), 0, 0, (SUM(sale.can) / SUM(
CASE
WHEN sale.but_id = '01' THEN sale.qty
ELSE sale.m2e
END))), 2) "ACTUAL_CAN_VOLUME",
ROUND((SUM(sale.bud_can) / SUM(sale.bud_qty)), 2) "BUD_CAN_VOLUME",
NULL "AVERAGE" --for the future
FROM sck_products p,
scr_but bu,
sck_sales sale,
scr_zones zones,
scr_subcontinents subcont,
scr_continents cont,
scr_prodfam prodfam,
scr_products product_desc
WHERE bu.id = p.but_id
AND p.but_id = sale.but_id
AND p.prc_id = sale.prc_id
AND p.prf_id = sale.prf_id
AND p.prd_id = sale.prd_id
AND sale.ZON_ID = zones.ID
AND subCont.ID = zones.SCT_ID
AND subCont.CON_ID = cont.ID
AND p.but_id = prodfam.but_id
AND p.prc_id = prodfam.prc_id
AND p.prf_id = prodfam.id
AND product_desc.id = p.prd_id
AND product_desc.but_id = p.but_id
AND product_desc.prc_id = p.prc_id
AND product_desc.prf_id = p.prf_id
--calculation
AND p.rgd_sell_price_bl = 'Y'
GROUP BY sale.period_dt,
sale.period_sw,
p.rgd_sell_price_bl,
p.but_id,
bu.nm,
p.prf_id,
prodfam.nm,
CASE
WHEN sale.but_id = '01' THEN
'All'
ELSE
p.prd_id
END,
CASE
WHEN sale.but_id = '01' THEN
'All'
ELSE
product_desc.nm
END,
cont.id,
cont.nm,
zones.ID ,
zones.NM |
Partager