select
KPIs,
values_mtd,
values_lmtd,
values_lfm,
uni_line_mtd,
uni_line_lmtd,
uni_line_lfm,
per_mtd,
per_lmtd
per_lfm
from(
select
Staples_Oil_MTD,
Staples_Oil_LMTD,
Staples_Oil_LFM,
Staples_Sugar_MTD,
Staples_Sugar_LMTD,
Staples_Sugar_LFM,
Rest_of_Staples_MTD,
Rest_of_Staples_LMTD,
Rest_of_Staples_LFM,
Dairy_Fresh_Frozen_MTD,
Dairy_Fresh_Frozen_LMTD,
Dairy_Fresh_Frozen_LFM,
FMCG_Foods_MTD,
FMCG_Foods_LMTD,
FMCG_Foods_LFM,
HPC_MTD,
HPC_LMTD,
HPC_LFM,
GM_MTD,
GM_LMTD,
GM_LFM,
Others_MTD,
Others_LMTD,
Others_LFM,
Staples_Oil_UNIQUE_LINES_MTD,
Staples_Oil_UNIQUE_LINES_LMTD,
Staples_Oil_UNIQUE_LINES_LFM,
Staples_Sugar_UNIQUE_LINES_MTD,
Staples_Sugar_UNIQUE_LINES_LMTD,
Staples_Sugar_UNIQUE_LINES_LFM,
Rest_of_Staples_UNIQUE_LINES_MTD,
Rest_of_Staples_UNIQUE_LINES_LMTD,
Rest_of_Staples_UNIQUE_LINES_LFM,
Dairy_Fresh_Frozen_UNIQUE_LINES_MTD,
Dairy_Fresh_Frozen_UNIQUE_LINES_LMTD,
Dairy_Fresh_Frozen_UNIQUE_LINES_LFM,
FMCG_Foods_UNIQUE_LINES_MTD,
FMCG_Foods_UNIQUE_LINES_LMTD,
FMCG_Foods_UNIQUE_LINES_LFM,
HPC_UNIQUE_LINES_MTD,
HPC_UNIQUE_LINES_LMTD,
HPC_UNIQUE_LINES_LFM,
GM_UNIQUE_LINES_MTD,
GM_UNIQUE_LINES_LMTD,
GM_UNIQUE_LINES_LFM,
Others_UNIQUE_LINES_MTD,
Others_UNIQUE_LINES_LMTD,
Others_UNIQUE_LINES_LFM,
(Staples_Oil_MTD / NULLIF(total_mtd, 0)) * 100 Staples_Oil_MTD_per,
(Staples_Sugar_MTD / NULLIF(total_mtd, 0)) * 100 Staples_Sugar_MTD_per,
(Rest_of_Staples_MTD / NULLIF(total_mtd, 0)) * 100 Rest_of_Staples_MTD_per,
(Dairy_Fresh_Frozen_MTD / NULLIF(total_mtd, 0)) * 100 Dairy_Fresh_Frozen_MTD_per,
(FMCG_Foods_MTD / NULLIF(total_mtd, 0)) * 100 FMCG_Foods_MTD_per,
(HPC_MTD / NULLIF(total_mtd, 0)) * 100 HPC_MTD_per,
(GM_MTD / NULLIF(total_mtd, 0)) * 100 GM_MTD_per,
(Others_MTD / NULLIF(total_mtd, 0)) * 100 Others_MTD_per,
(Staples_Oil_UNIQUE_LINES_MTD / NULLIF(total_mtd, 0)) * 100 Staples_Oil_UNIQUE_LINES_MTD_per,
(
Staples_Sugar_UNIQUE_LINES_MTD / NULLIF(total_mtd, 0)
) * 100 Staples_Sugar_UNIQUE_LINES_MTD_per,
(
Rest_of_Staples_UNIQUE_LINES_MTD / NULLIF(total_mtd, 0)
) * 100 Rest_of_Staples_UNIQUE_LINES_MTD_per,
(
Dairy_Fresh_Frozen_UNIQUE_LINES_MTD / NULLIF(total_mtd, 0)
) * 100 Dairy_Fresh_Frozen_UNIQUE_LINES_MTD_per,
(FMCG_Foods_UNIQUE_LINES_MTD / NULLIF(total_mtd, 0)) * 100 FMCG_Foods_UNIQUE_LINES_MTD_per,
(HPC_UNIQUE_LINES_MTD / NULLIF(total_mtd, 0)) * 100 HPC_UNIQUE_LINES_MTD_per,
(GM_UNIQUE_LINES_MTD / NULLIF(total_mtd, 0)) * 100 GM_UNIQUE_LINES_MTD_per,
(Others_UNIQUE_LINES_MTD / NULLIF(total_mtd, 0)) * 100 Others_UNIQUE_LINES_MTD_per,
(Staples_Oil_LMTD / NULLIF(total_lmtd, 0)) * 100 Staples_Oil_LMTD_per,
(Staples_Sugar_LMTD / NULLIF(total_lmtd, 0)) * 100 Staples_Sugar_LMTD_per,
(Rest_of_Staples_LMTD / NULLIF(total_lmtd, 0)) * 100 Rest_of_Staples_LMTD_per,
(Dairy_Fresh_Frozen_LMTD / NULLIF(total_lmtd, 0)) * 100 Dairy_Fresh_Frozen_LMTD_per,
(FMCG_Foods_LMTD / NULLIF(total_lmtd, 0)) * 100 FMCG_Foods_LMTD_per,
(HPC_LMTD / NULLIF(total_lmtd, 0)) * 100 HPC_LMTD_per,
(GM_LMTD / NULLIF(total_lmtd, 0)) * 100 GM_LMTD_per,
(Others_LMTD / NULLIF(total_lmtd, 0)) * 100 Others_LMTD_per,
(
Staples_Oil_UNIQUE_LINES_LMTD / NULLIF(total_lmtd, 0)
) * 100 Staples_Oil_UNIQUE_LINES_LMTD_per,
(
Staples_Sugar_UNIQUE_LINES_LMTD / NULLIF(total_lmtd, 0)
) * 100 Staples_Sugar_UNIQUE_LINES_LMTD_per,
(
Rest_of_Staples_UNIQUE_LINES_LMTD / NULLIF(total_lmtd, 0)
) * 100 Rest_of_Staples_UNIQUE_LINES_LMTD_per,
(
Dairy_Fresh_Frozen_UNIQUE_LINES_LMTD / NULLIF(total_lmtd, 0)
) * 100 Dairy_Fresh_Frozen_UNIQUE_LINES_LMTD_per,
(
FMCG_Foods_UNIQUE_LINES_LMTD / NULLIF(total_lmtd, 0)
) * 100 FMCG_Foods_UNIQUE_LINES_LMTD_per,
(HPC_UNIQUE_LINES_LMTD / NULLIF(total_lmtd, 0)) * 100 HPC_UNIQUE_LINES_LMTD_per,
(GM_UNIQUE_LINES_LMTD / NULLIF(total_lmtd, 0)) * 100 GM_UNIQUE_LINES_LMTD_per,
(Others_UNIQUE_LINES_LMTD / NULLIF(total_lmtd, 0)) * 100 Others_UNIQUE_LINES_LMTD_per,
(Staples_Oil_LFM / NULLIF(total_lfm, 0)) * 100 Staples_Oil_LFM_per,
(Staples_Sugar_LFM / NULLIF(total_lfm, 0)) * 100 Staples_Sugar_LFM_per,
(Rest_of_Staples_LFM / NULLIF(total_lfm, 0)) * 100 Rest_of_Staples_LFM_per,
(Dairy_Fresh_Frozen_LFM / NULLIF(total_lfm, 0)) * 100 Dairy_Fresh_Frozen_LFM_per,
(FMCG_Foods_LFM / NULLIF(total_lfm, 0)) * 100 FMCG_Foods_LFM_per,
(HPC_LFM / NULLIF(total_lfm, 0)) * 100 HPC_LFM_per,
(GM_LFM / NULLIF(total_lfm, 0)) * 100 GM_LFM_per,
(Others_LFM / NULLIF(total_lfm, 0)) * 100 Others_LFM_per,
(Staples_Oil_UNIQUE_LINES_LFM / NULLIF(total_lfm, 0)) * 100 Staples_Oil_UNIQUE_LINES_LFM_per,
(
Staples_Sugar_UNIQUE_LINES_LFM / NULLIF(total_lfm, 0)
) * 100 Staples_Sugar_UNIQUE_LINES_LFM_per,
(
Rest_of_Staples_UNIQUE_LINES_LFM / NULLIF(total_lfm, 0)
) * 100 Rest_of_Staples_UNIQUE_LINES_LFM_per,
(
Dairy_Fresh_Frozen_UNIQUE_LINES_LFM / NULLIF(total_lfm, 0)
) * 100 Dairy_Fresh_Frozen_UNIQUE_LINES_LFM_per,
(FMCG_Foods_UNIQUE_LINES_LFM / NULLIF(total_lfm, 0)) * 100 FMCG_Foods_UNIQUE_LINES_LFM_per,
(HPC_UNIQUE_LINES_LFM / NULLIF(total_lfm, 0)) * 100 HPC_UNIQUE_LINES_LFM_per,
(GM_UNIQUE_LINES_LFM / NULLIF(total_lfm, 0)) * 100 GM_UNIQUE_LINES_LFM_per,
(Others_UNIQUE_LINES_LFM / NULLIF(total_lfm, 0)) * 100 Others_UNIQUE_LINES_LFM_per
from(
select
Staples_Oil_MTD,
Staples_Oil_LMTD,
Staples_Oil_LFM,
Staples_Sugar_MTD,
Staples_Sugar_LMTD,
Staples_Sugar_LFM,
Rest_of_Staples_MTD,
Rest_of_Staples_LMTD,
Rest_of_Staples_LFM,
Dairy_Fresh_Frozen_MTD,
Dairy_Fresh_Frozen_LMTD,
Dairy_Fresh_Frozen_LFM,
FMCG_Foods_MTD,
FMCG_Foods_LMTD,
FMCG_Foods_LFM,
HPC_MTD,
HPC_LMTD,
HPC_LFM,
GM_MTD,
GM_LMTD,
GM_LFM,
Others_MTD,
Others_LMTD,
Others_LFM,
Staples_Oil_UNIQUE_LINES_MTD,
Staples_Oil_UNIQUE_LINES_LMTD,
Staples_Oil_UNIQUE_LINES_LFM,
Staples_Sugar_UNIQUE_LINES_MTD,
Staples_Sugar_UNIQUE_LINES_LMTD,
Staples_Sugar_UNIQUE_LINES_LFM,
Rest_of_Staples_UNIQUE_LINES_MTD,
Rest_of_Staples_UNIQUE_LINES_LMTD,
Rest_of_Staples_UNIQUE_LINES_LFM,
Dairy_Fresh_Frozen_UNIQUE_LINES_MTD,
Dairy_Fresh_Frozen_UNIQUE_LINES_LMTD,
Dairy_Fresh_Frozen_UNIQUE_LINES_LFM,
FMCG_Foods_UNIQUE_LINES_MTD,
FMCG_Foods_UNIQUE_LINES_LMTD,
FMCG_Foods_UNIQUE_LINES_LFM,
HPC_UNIQUE_LINES_MTD,
HPC_UNIQUE_LINES_LMTD,
HPC_UNIQUE_LINES_LFM,
GM_UNIQUE_LINES_MTD,
GM_UNIQUE_LINES_LMTD,
GM_UNIQUE_LINES_LFM,
Others_UNIQUE_LINES_MTD,
Others_UNIQUE_LINES_LMTD,
Others_UNIQUE_LINES_LFM,
(
Staples_Oil_MTD + Staples_Sugar_MTD + Rest_of_Staples_MTD + Dairy_Fresh_Frozen_MTD + FMCG_Foods_MTD + HPC_MTD + GM_MTD + Others_MTD + Staples_Oil_UNIQUE_LINES_MTD + Staples_Sugar_UNIQUE_LINES_MTD + Rest_of_Staples_UNIQUE_LINES_MTD + Dairy_Fresh_Frozen_UNIQUE_LINES_MTD + FMCG_Foods_UNIQUE_LINES_MTD + HPC_UNIQUE_LINES_MTD + GM_UNIQUE_LINES_MTD + Others_UNIQUE_LINES_MTD
) as total_mtd,
(
Staples_Oil_LMTD + Staples_Sugar_LMTD + Rest_of_Staples_LMTD + Dairy_Fresh_Frozen_LMTD + FMCG_Foods_LMTD + HPC_LMTD + GM_LMTD + Others_LMTD + Staples_Oil_UNIQUE_LINES_LMTD + Staples_Sugar_UNIQUE_LINES_LMTD + Rest_of_Staples_UNIQUE_LINES_LMTD + Dairy_Fresh_Frozen_UNIQUE_LINES_LMTD + FMCG_Foods_UNIQUE_LINES_LMTD + HPC_UNIQUE_LINES_LMTD + GM_UNIQUE_LINES_LMTD + Others_UNIQUE_LINES_LMTD
) as total_lmtd,
(
Staples_Oil_LFM + Staples_Sugar_LFM + Rest_of_Staples_LFM + Dairy_Fresh_Frozen_LFM + FMCG_Foods_LFM + HPC_LFM + GM_LFM + Others_LFM + Staples_Oil_UNIQUE_LINES_LFM + Staples_Sugar_UNIQUE_LINES_LFM + Rest_of_Staples_UNIQUE_LINES_LFM + Dairy_Fresh_Frozen_UNIQUE_LINES_LFM + FMCG_Foods_UNIQUE_LINES_LFM + HPC_UNIQUE_LINES_LFM + GM_UNIQUE_LINES_LFM + Others_UNIQUE_LINES_LFM
) as total_lfm
from(
select
SUM(
CASE when RH_GPC_CATEGORY in (
'Foodgrains, Oils ' || '&' || ' Masala',
'Marka ' || '&' || ' Bulk Packs'
)
and RH_PARENT_NAME in ('Oils', 'Oils ' || '&' || ' Ghee')
and TRUNC(b.ORDER_DATE) BETWEEN ((LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -1)) + 1))
and (trunc(sysdate) -1) then (NVL(STR_LINE_TOTAL, 0) - NVL(INVOICED_LINE_TOTAL, 0)) END
) Staples_Oil_MTD,
SUM(
CASE when RH_GPC_CATEGORY in (
'Foodgrains, Oils ' || '&' || ' Masala',
'Marka ' || '&' || ' Bulk Packs'
)
and RH_PARENT_NAME in ('Oils', 'Oils ' || '&' || ' Ghee')
and TRUNC(b.ORDER_DATE) BETWEEN ((LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -2)) + 1))
and (((ADD_MONTHS(TRUNC(SYSDATE), -1)))) then (NVL(STR_LINE_TOTAL, 0) - NVL(INVOICED_LINE_TOTAL, 0)) END
) Staples_Oil_LMTD,
SUM(
CASE when RH_GPC_CATEGORY in (
'Foodgrains, Oils ' || '&' || ' Masala',
'Marka ' || '&' || ' Bulk Packs'
)
and RH_PARENT_NAME in ('Oils', 'Oils ' || '&' || ' Ghee')
and TRUNC(b.ORDER_DATE) BETWEEN ((LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -2)) + 1))
and LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -1)) then (NVL(STR_LINE_TOTAL, 0) - NVL(INVOICED_LINE_TOTAL, 0)) END
) Staples_Oil_LFM,
SUM(
CASE when RH_GPC_CATEGORY in (
'Foodgrains, Oils ' || '&' || ' Masala',
'Marka ' || '&' || ' Bulk Packs'
)
and RH_PARENT_NAME in ('Salt ' || '&' || ' Sugar')
and CATEGORYNAME in ('Sugar')
and TRUNC(b.ORDER_DATE) BETWEEN ((LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -1)) + 1))
and (trunc(sysdate) -1) then (NVL(STR_LINE_TOTAL, 0) - NVL(INVOICED_LINE_TOTAL, 0)) END
) Staples_Sugar_MTD,
SUM(
CASE when RH_GPC_CATEGORY in (
'Foodgrains, Oils ' || '&' || ' Masala',
'Marka ' || '&' || ' Bulk Packs'
)
and RH_PARENT_NAME in ('Salt ' || '&' || ' Sugar')
and CATEGORYNAME in ('Sugar')
and TRUNC(b.ORDER_DATE) BETWEEN ((LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -2)) + 1))
and (((ADD_MONTHS(TRUNC(SYSDATE), -1)))) then (NVL(STR_LINE_TOTAL, 0) - NVL(INVOICED_LINE_TOTAL, 0)) END
) Staples_Sugar_LMTD,
SUM(
CASE when RH_GPC_CATEGORY in (
'Foodgrains, Oils ' || '&' || ' Masala',
'Marka ' || '&' || ' Bulk Packs'
)
and RH_PARENT_NAME in ('Salt ' || '&' || ' Sugar')
and CATEGORYNAME in ('Sugar')
and TRUNC(b.ORDER_DATE) BETWEEN ((LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -2)) + 1))
and LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -1)) then (NVL(STR_LINE_TOTAL, 0) - NVL(INVOICED_LINE_TOTAL, 0)) END
) Staples_Sugar_LFM,
SUM(
CASE when RH_GPC_CATEGORY in (
'Foodgrains, Oils ' || '&' || ' Masala',
'Marka ' || '&' || ' Bulk Packs'
)
and RH_PARENT_NAME not in ('Oils')
and CATEGORYNAME not in ('Sugar')
and TRUNC(b.ORDER_DATE) BETWEEN ((LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -1)) + 1))
and (trunc(sysdate) -1) then (NVL(STR_LINE_TOTAL, 0) - NVL(INVOICED_LINE_TOTAL, 0)) END
) Rest_of_Staples_MTD,
SUM(
CASE when RH_GPC_CATEGORY in (
'Foodgrains, Oils ' || '&' || ' Masala',
'Marka ' || '&' || ' Bulk Packs'
)
and RH_PARENT_NAME not in ('Oils')
and CATEGORYNAME not in ('Sugar')
and TRUNC(b.ORDER_DATE) BETWEEN ((LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -2)) + 1))
and (((ADD_MONTHS(TRUNC(SYSDATE), -1)))) then (NVL(STR_LINE_TOTAL, 0) - NVL(INVOICED_LINE_TOTAL, 0)) END
) Rest_of_Staples_LMTD,
SUM(
CASE when RH_GPC_CATEGORY in (
'Foodgrains, Oils ' || '&' || ' Masala',
'Marka ' || '&' || ' Bulk Packs'
)
and RH_PARENT_NAME not in ('Oils')
and CATEGORYNAME not in ('Sugar')
and TRUNC(b.ORDER_DATE) BETWEEN ((LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -2)) + 1))
and LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -1)) then (NVL(STR_LINE_TOTAL, 0) - NVL(INVOICED_LINE_TOTAL, 0)) END
) Rest_of_Staples_LFM,
SUM(
CASE when RH_GPC_CATEGORY in (
'Bakery ' || '&' || ' Dairy',
'Fruits ' || '&' || ' Vegetables',
'Frozen Foods'
)
and TRUNC(b.ORDER_DATE) BETWEEN ((LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -1)) + 1))
and (trunc(sysdate) -1) then (NVL(STR_LINE_TOTAL, 0) - NVL(INVOICED_LINE_TOTAL, 0)) END
) Dairy_Fresh_Frozen_MTD,
SUM(
CASE when RH_GPC_CATEGORY in (
'Bakery ' || '&' || ' Dairy',
'Fruits ' || '&' || ' Vegetables',
'Frozen Foods'
)
and TRUNC(b.ORDER_DATE) BETWEEN ((LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -2)) + 1))
and (((ADD_MONTHS(TRUNC(SYSDATE), -1)))) then (NVL(STR_LINE_TOTAL, 0) - NVL(INVOICED_LINE_TOTAL, 0)) END
) Dairy_Fresh_Frozen_LMTD,
SUM(
CASE when RH_GPC_CATEGORY in (
'Bakery ' || '&' || ' Dairy',
'Fruits ' || '&' || ' Vegetables',
'Frozen Foods'
)
and TRUNC(b.ORDER_DATE) BETWEEN ((LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -2)) + 1))
and LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -1)) then (NVL(STR_LINE_TOTAL, 0) - NVL(INVOICED_LINE_TOTAL, 0)) END
) Dairy_Fresh_Frozen_LFM,
SUM(
CASE when RH_GPC_CATEGORY in (
'Instant ' || '&' || ' Ready Foods',
'Snacks ' || '&' || ' Confectionery',
'Health Drinks ' || '&' || ' Beverages'
)
and TRUNC(b.ORDER_DATE) BETWEEN ((LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -1)) + 1))
and (trunc(sysdate) -1) then (NVL(STR_LINE_TOTAL, 0) - NVL(INVOICED_LINE_TOTAL, 0)) END
) FMCG_Foods_MTD,
SUM(
CASE when RH_GPC_CATEGORY in (
'Instant ' || '&' || ' Ready Foods',
'Snacks ' || '&' || ' Confectionery',
'Health Drinks ' || '&' || ' Beverages'
)
and TRUNC(b.ORDER_DATE) BETWEEN ((LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -2)) + 1))
and (((ADD_MONTHS(TRUNC(SYSDATE), -1)))) then (NVL(STR_LINE_TOTAL, 0) - NVL(INVOICED_LINE_TOTAL, 0)) END
) FMCG_Foods_LMTD,
SUM(
CASE when RH_GPC_CATEGORY in (
'Instant ' || '&' || ' Ready Foods',
'Snacks ' || '&' || ' Confectionery',
'Health Drinks ' || '&' || ' Beverages'
)
and TRUNC(b.ORDER_DATE) BETWEEN ((LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -2)) + 1))
and LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -1)) then (NVL(STR_LINE_TOTAL, 0) - NVL(INVOICED_LINE_TOTAL, 0)) END
) FMCG_Foods_LFM,
SUM(
CASE when RH_GPC_CATEGORY in (
'Beauty ' || '&' || ' Hygiene',
'Cleaning ' || '&' || ' Household Care',
'Health ' || '&' || ' Wellness',
'Mom ' || '&' || ' Baby'
)
and TRUNC(b.ORDER_DATE) BETWEEN ((LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -1)) + 1))
and (trunc(sysdate) -1) then (NVL(STR_LINE_TOTAL, 0) - NVL(INVOICED_LINE_TOTAL, 0)) END
) HPC_MTD,
SUM(
CASE when RH_GPC_CATEGORY in (
'Beauty ' || '&' || ' Hygiene',
'Cleaning ' || '&' || ' Household Care',
'Health ' || '&' || ' Wellness',
'Mom ' || '&' || ' Baby'
)
and TRUNC(b.ORDER_DATE) BETWEEN ((LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -2)) + 1))
and (((ADD_MONTHS(TRUNC(SYSDATE), -1)))) then (NVL(STR_LINE_TOTAL, 0) - NVL(INVOICED_LINE_TOTAL, 0)) END
) HPC_LMTD,
SUM(
CASE when RH_GPC_CATEGORY in (
'Beauty ' || '&' || ' Hygiene',
'Cleaning ' || '&' || ' Household Care',
'Health ' || '&' || ' Wellness',
'Mom ' || '&' || ' Baby'
)
and TRUNC(b.ORDER_DATE) BETWEEN ((LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -2)) + 1))
and LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -1)) then (NVL(STR_LINE_TOTAL, 0) - NVL(INVOICED_LINE_TOTAL, 0)) END
) HPC_LFM,
SUM(
CASE when RH_GPC_CATEGORY in ('Home ' || '&' || ' Living')
and TRUNC(b.ORDER_DATE) BETWEEN ((LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -1)) + 1))
and (trunc(sysdate) -1) then (NVL(STR_LINE_TOTAL, 0) - NVL(INVOICED_LINE_TOTAL, 0)) END
) GM_MTD,
SUM(
CASE when RH_GPC_CATEGORY in ('Home ' || '&' || ' Living')
and TRUNC(b.ORDER_DATE) BETWEEN ((LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -2)) + 1))
and (((ADD_MONTHS(TRUNC(SYSDATE), -1)))) then (NVL(STR_LINE_TOTAL, 0) - NVL(INVOICED_LINE_TOTAL, 0)) END
) GM_LMTD,
SUM(
CASE when RH_GPC_CATEGORY in ('Home ' || '&' || ' Living')
and TRUNC(b.ORDER_DATE) BETWEEN ((LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -2)) + 1))
and LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -1)) then (NVL(STR_LINE_TOTAL, 0) - NVL(INVOICED_LINE_TOTAL, 0)) END
) GM_LFM,
SUM(
CASE when (RH_GPC_CATEGORY is null)
and TRUNC(b.ORDER_DATE) BETWEEN ((LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -1)) + 1))
and (trunc(sysdate) -1) then (NVL(STR_LINE_TOTAL, 0) - NVL(INVOICED_LINE_TOTAL, 0)) END
) Others_MTD,
SUM(
CASE when (RH_GPC_CATEGORY is null)
and TRUNC(b.ORDER_DATE) BETWEEN ((LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -2)) + 1))
and (((ADD_MONTHS(TRUNC(SYSDATE), -1)))) then (NVL(STR_LINE_TOTAL, 0) - NVL(INVOICED_LINE_TOTAL, 0)) END
) Others_LMTD,
SUM(
CASE when (RH_GPC_CATEGORY is null)
and TRUNC(b.ORDER_DATE) BETWEEN ((LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -2)) + 1))
and LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -1)) then (NVL(STR_LINE_TOTAL, 0) - NVL(INVOICED_LINE_TOTAL, 0)) END
) Others_LFM,
count(
distinct(
case when RH_GPC_CATEGORY in (
'Foodgrains, Oils ' || '&' || ' Masala',
'Marka ' || '&' || ' Bulk Packs'
)
and RH_PARENT_NAME in ('Oils', 'Oils ' || '&' || ' Ghee')
and TRUNC(b.ORDER_DATE) BETWEEN ((LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -1)) + 1))
and (trunc(sysdate) -1) then (P_SAPARTICLENUMBER) END
)
) Staples_Oil_UNIQUE_LINES_MTD,
count(
distinct(
case when RH_GPC_CATEGORY in (
'Foodgrains, Oils ' || '&' || ' Masala',
'Marka ' || '&' || ' Bulk Packs'
)
and RH_PARENT_NAME in ('Oils', 'Oils ' || '&' || ' Ghee')
and TRUNC(b.ORDER_DATE) BETWEEN ((LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -2)) + 1))
and (((ADD_MONTHS(TRUNC(SYSDATE), -1)))) then (P_SAPARTICLENUMBER) END
)
) Staples_Oil_UNIQUE_LINES_LMTD,
count(
distinct(
case when RH_GPC_CATEGORY in (
'Foodgrains, Oils ' || '&' || ' Masala',
'Marka ' || '&' || ' Bulk Packs'
)
and RH_PARENT_NAME in ('Oils', 'Oils ' || '&' || ' Ghee')
and TRUNC(b.ORDER_DATE) BETWEEN ((LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -2)) + 1))
and LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -1)) then (P_SAPARTICLENUMBER) END
)
) Staples_Oil_UNIQUE_LINES_LFM,
count(
distinct(
case when RH_GPC_CATEGORY in (
'Foodgrains, Oils ' || '&' || ' Masala',
'Marka ' || '&' || ' Bulk Packs'
)
and RH_PARENT_NAME in ('Salt ' || '&' || ' Sugar')
and CATEGORYNAME in ('Sugar')
and TRUNC(b.ORDER_DATE) BETWEEN ((LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -1)) + 1))
and (trunc(sysdate) -1) then (P_SAPARTICLENUMBER) END
)
) Staples_Sugar_UNIQUE_LINES_MTD,
count(
distinct(
case when RH_GPC_CATEGORY in (
'Foodgrains, Oils ' || '&' || ' Masala',
'Marka ' || '&' || ' Bulk Packs'
)
and RH_PARENT_NAME in ('Salt ' || '&' || ' Sugar')
and CATEGORYNAME in ('Sugar')
and TRUNC(b.ORDER_DATE) BETWEEN ((LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -2)) + 1))
and (((ADD_MONTHS(TRUNC(SYSDATE), -1)))) then (P_SAPARTICLENUMBER) END
)
) Staples_Sugar_UNIQUE_LINES_LMTD,
count(
distinct(
case when RH_GPC_CATEGORY in (
'Foodgrains, Oils ' || '&' || ' Masala',
'Marka ' || '&' || ' Bulk Packs'
)
and RH_PARENT_NAME in ('Salt ' || '&' || ' Sugar')
and CATEGORYNAME in ('Sugar')
and TRUNC(b.ORDER_DATE) BETWEEN ((LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -2)) + 1))
and LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -1)) then (P_SAPARTICLENUMBER) END
)
) Staples_Sugar_UNIQUE_LINES_LFM,
count(
distinct(
case when RH_GPC_CATEGORY in (
'Foodgrains, Oils ' || '&' || ' Masala',
'Marka ' || '&' || ' Bulk Packs'
)
and RH_PARENT_NAME not in ('Oils')
and CATEGORYNAME not in ('Sugar')
and TRUNC(b.ORDER_DATE) BETWEEN ((LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -1)) + 1))
and (trunc(sysdate) -1) then (P_SAPARTICLENUMBER) END
)
) Rest_of_Staples_UNIQUE_LINES_MTD,
count(
distinct(
case when RH_GPC_CATEGORY in (
'Foodgrains, Oils ' || '&' || ' Masala',
'Marka ' || '&' || ' Bulk Packs'
)
and RH_PARENT_NAME not in ('Oils')
and CATEGORYNAME not in ('Sugar')
and TRUNC(b.ORDER_DATE) BETWEEN ((LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -2)) + 1))
and (((ADD_MONTHS(TRUNC(SYSDATE), -1)))) then (P_SAPARTICLENUMBER) END
)
) Rest_of_Staples_UNIQUE_LINES_LMTD,
count(
distinct(
case when RH_GPC_CATEGORY in (
'Foodgrains, Oils ' || '&' || ' Masala',
'Marka ' || '&' || ' Bulk Packs'
)
and RH_PARENT_NAME not in ('Oils')
and CATEGORYNAME not in ('Sugar')
and TRUNC(b.ORDER_DATE) BETWEEN ((LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -2)) + 1))
and LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -1)) then (P_SAPARTICLENUMBER) END
)
) Rest_of_Staples_UNIQUE_LINES_LFM,
count(
distinct(
case when RH_GPC_CATEGORY in (
'Bakery ' || '&' || ' Dairy',
'Fruits ' || '&' || ' Vegetables',
'Frozen Foods'
)
and TRUNC(b.ORDER_DATE) BETWEEN ((LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -1)) + 1))
and (trunc(sysdate) -1) then (P_SAPARTICLENUMBER) END
)
) Dairy_Fresh_Frozen_UNIQUE_LINES_MTD,
count(
distinct(
case when RH_GPC_CATEGORY in (
'Bakery ' || '&' || ' Dairy',
'Fruits ' || '&' || ' Vegetables',
'Frozen Foods'
)
and TRUNC(b.ORDER_DATE) BETWEEN ((LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -2)) + 1))
and (((ADD_MONTHS(TRUNC(SYSDATE), -1)))) then (P_SAPARTICLENUMBER) END
)
) Dairy_Fresh_Frozen_UNIQUE_LINES_LMTD,
count(
distinct(
case when RH_GPC_CATEGORY in (
'Bakery ' || '&' || ' Dairy',
'Fruits ' || '&' || ' Vegetables',
'Frozen Foods'
)
and TRUNC(b.ORDER_DATE) BETWEEN ((LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -2)) + 1))
and LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -1)) then (P_SAPARTICLENUMBER) END
)
) Dairy_Fresh_Frozen_UNIQUE_LINES_LFM,
count(
distinct(
case when RH_GPC_CATEGORY in (
'Instant ' || '&' || '