Inventory Adjustment Codes
start_date ='2024-06-01'
end_date = datetime.now().strftime("%Y-%m-%d")
Sales Trajectory
with
loc_data as (select * from master.loc
where loc_type = 'DARKSTORE'and (close_date is null or inactive_date is null)
and loc in (1014,3688,4051, 4054, 4055, 4057,4061,4062,4063,4064,4065,4066))
select
last_day(b_date) as month,
loc_name,
count(distinct b_date) as days,round(sum(bill_net_value)/1e7,1) as ns
from tran.txn t join loc_data l on t.location_code = l.loc
where b_date between {{start_date}} and {{end_date}}
group by 1,2
order by 1,2
Overall DDES Split
with
loc_data as (select * from master.loc
where loc_type = 'DARKSTORE'and (close_date is null or inactive_date is null)
and loc in (1014,3688,4051, 4054, 4055, 4057,4061,4062,4063,4064,4065,4066)),
adj_data as (select date(created_at) as dt,location,ia.item,
sum(case when reason IN (87) then -1.0*quantity else quantity end) as adj_qty,
sum(case when reason in (9) then quantity else 0.0 end) as expired_qty,
sum(case when reason in (11) then quantity else 0.0 end) as damage_qty,
sum(case when reason in (113,114) then quantity else 0.0 end) as ret_adj,
sum(case when reason IN (87) then -1.0*quantity when reason in (88) then quantity else 0.0 end) as shrink_qty,
sum(case when reason IN (7,112) then quantity else 0.0 end) as dump_qty,
sum(case when reason IN (7,88,113,114,9,11,112) then quantity when reason IN (87) then -1.0*quantity else 0.0 end) as valid_adj_qty
from fnvportal_prod.fnvportalapp_inventoryadjustment ia
join loc_data l on ia.location = l.loc
join master.item i on ia.item = i.item
and approval_status = 'AP' and rtv_file_name = ''
where date(created_at) between {{start_date}} and {{end_date}}
group by 1,2,3),
soh_data as (select s.date,
s.loc, s.item,
sum(stock_on_hand*av_cost) as inventory_value,sum(stock_on_hand) as soh_qty,
case when sum(stock_on_hand) <> 0 then sum(stock_on_hand*av_cost)/sum(stock_on_hand) else avg(av_cost) end as avg_cost
from tran.soh s
join master.item i on s.item = i.item
join loc_data l on s.loc = l.loc
where s.date between {{start_date}} and {{end_date}}
group by 1,2,3
)
select last_day(date) as month,
count(distinct date) as days,
--case when shelf_life<=0 or dept = 85 then 'perishable' else 'dry' end as item_type,
count(distinct loc) as stores,
round(sum(avg_cost*nvl(adj_qty,0))/1e5,1) as inv_adj,
round(sum(avg_cost*nvl(ret_adj,0))/1e5,1) as ret_adj,
round(sum(avg_cost*nvl(expired_qty,0))/1e5,1) as exp_adj,
round(sum(avg_cost*nvl(damage_qty,0))/1e5,1) as dmg_adj,
round(sum(avg_cost*nvl(shrink_qty,0))/1e5,1) as shrink_adj,
round(sum(avg_cost*nvl(dump_qty,0))/1e5,1) as dump_adj,
round(sum(avg_cost*nvl(valid_adj_qty,0))/1e5,1) as valid_adj,
round(sum(inventory_value)/1e5/count(distinct date),1) as inv_value
from adj_data
full outer join soh_data on adj_data.dt = soh_data.date and adj_data.location = soh_data.loc and
adj_data.item = soh_data.item
join master.item i on soh_data.item = i.item
group by 1
order by month desc
Dump
with
loc_data as (
select * from master.loc
where loc_type = 'DARKSTORE'and (close_date is null or inactive_date is null)
and loc in (1014,3688,4051, 4054, 4055, 4057,4061,4062,4063,4064,4065,4066)),
soh_data as (select s.date,
s.loc, s.item,
sum(stock_on_hand*av_cost) as inventory_value,sum(stock_on_hand) as soh_qty,
case when sum(stock_on_hand) <> 0 then sum(stock_on_hand*av_cost)/sum(stock_on_hand) else avg(av_cost) end as avg_cost
from tran.soh s
join master.item i on s.item = i.item
join loc_data l on s.loc = l.loc
where s.date between {{start_date}} and {{end_date}}
and (dept = 85 or division_name = 'BDF')
group by 1,2,3),
adj_data as (
select date(created_at) as dt,location,ia.item,
sum(case when reason IN (87) then -1.0*quantity else quantity end) as adj_qty,
sum(case when reason in (9) then quantity else 0.0 end) as expired_qty,
sum(case when reason in (11) then quantity else 0.0 end) as damage_qty,
sum(case when reason in (113,114) then quantity else 0.0 end) as ret_adj,
sum(case when reason IN (87) then -1.0*quantity when reason in (88) then quantity else 0.0 end) as shrink_qty,
sum(case when reason IN (7,112) then quantity else 0.0 end) as dump_qty,
sum(case when reason IN (7,88,113,114,9,11,112) then quantity when reason IN (87) then -1.0*quantity else 0.0 end) as valid_adj_qty
from fnvportal_prod.fnvportalapp_inventoryadjustment ia
join loc_data l on ia.location = l.loc
join master.item i on ia.item = i.item
and approval_status = 'AP' and rtv_file_name = ''
where date(created_at) between {{start_date}} and {{end_date}}
and (dept = 85 or division_name = 'BDF')
group by 1,2,3),
indent_data as (
select dateadd(d,2,date) as sup_date,location,item,final_indent
from fnvportal_prod.fnvportalapp_indentdetail indent join loc_data on loc_data.loc = indent.location
where date between {{start_date}} and {{end_date}}),
dos_data as (
select date,loc_data.loc,item,salesq,salesq_adj,
bcrosq,bcrosq_adj,rosq_7d,rosq_7d_adj,rosq_adj_3m,ros,bcros,ros_7d,shelf_life
from analytics.dos join loc_data on loc_data.loc = analytics.dos.loc
where (dept = 85 or division_name = 'BDF') and date between '2024-06-01' and '2024-07-31'),
joined_data as (
select soh_data.date,soh_data.loc,soh_data.item,avg_cost,
greatest(nvl(shelf_life::float,1),1) as shelf_life,
nvl(soh_qty,0) as soh_qty,
nvl(final_indent,0) as final_indent,
nvl(salesq,0) as salesq,
nvl(salesq_adj,0) as salesq_adj,
nvl(bcrosq,0) as bcrosq,
nvl(bcrosq_adj,0) as bcrosq_adj,
nvl(rosq_7d,0) as rosq_7d,
nvl(rosq_7d_adj,0) as rosq_7d_adj,
nvl(rosq_adj_3m,0) as rosq_adj_3m,
nvl(dump_qty,0) as dump_qty,
greatest(salesq,salesq_adj,bcrosq,bcrosq_adj,rosq_7d,rosq_7d_adj,rosq_adj_3m) as sales_trend,
case
when sales_trend<=0 and final_indent>0 then 'no sales indent'
when sales_trend<=0 and final_indent<=0 then 'ok indent'
else 'check indent'
end as error_flag,
case
when error_flag = 'check indent' and sales_trend<>0 and (soh_qty/sales_trend)>=greatest(nvl(shelf_life::float,1),1) then 'excees indent'
else 'ok indent'
end as indent_flag
from soh_data
full outer join dos_data on soh_data.date = dos_data.date and soh_data.loc = dos_data.loc and soh_data.item = dos_data.item
full outer join adj_data on soh_data.date = adj_data.dt and soh_data.loc = adj_data.location and soh_data.item = adj_data.item
full outer join indent_data on soh_data.date = indent_data.sup_date and soh_data.loc = indent_data.location and soh_data.item = indent_data.item)
select
last_day(date) as month,
error_flag,
indent_flag,
round(sum(soh_qty*avg_cost)/1e5,1) as inv_value,
round(sum(greatest(salesq,salesq_adj,bcrosq,bcrosq_adj,rosq_7d,rosq_7d_adj,rosq_adj_3m)*avg_cost)/1e5,1) as sales_value,
round(sum(dump_qty*avg_cost)/1e5,1) as dump_value,
round(sum(final_indent*avg_cost)/1e5,1) as final_indent_value
from joined_data
where date between {{start_date}} and {{end_date}}
group by 1,2,3
order by 3,2,1 desc
Expiry
with
loc_data as (
select * from master.loc
where loc_type = 'DARKSTORE'and (close_date is null or inactive_date is null)
and loc in (1014,3688,4051, 4054, 4055, 4057,4061,4062,4063,4064,4065,4066)),
soh_data as (select s.date,
s.loc, s.item,
sum(stock_on_hand*av_cost) as inventory_value,sum(stock_on_hand) as soh_qty,
case when sum(stock_on_hand) <> 0 then sum(stock_on_hand*av_cost)/sum(stock_on_hand) else avg(av_cost) end as avg_cost
from tran.soh s
join master.item i on s.item = i.item
join loc_data l on s.loc = l.loc
where s.date between {{start_date}} and {{end_date}}
and dept <> 85
group by 1,2,3),
adj_data as (
select date(created_at) as dt,location,ia.item,
sum(case when reason IN (87) then -1.0*quantity else quantity end) as adj_qty,
sum(case when reason in (9) then quantity else 0.0 end) as expired_qty,
sum(case when reason in (11) then quantity else 0.0 end) as damage_qty,
sum(case when reason in (113,114) then quantity else 0.0 end) as ret_adj,
sum(case when reason IN (87) then -1.0*quantity when reason in (88) then quantity else 0.0 end) as shrink_qty,
sum(case when reason IN (7,112) then quantity else 0.0 end) as dump_qty,
sum(case when reason IN (7,88,113,114,9,11,112) then quantity when reason IN (87) then -1.0*quantity else 0.0 end) as valid_adj_qty
from fnvportal_prod.fnvportalapp_inventoryadjustment ia
join loc_data l on ia.location = l.loc
join master.item i on ia.item = i.item
and approval_status = 'AP' and rtv_file_name = ''
where date(created_at) between {{start_date}} and {{end_date}}
and dept <> 85
group by 1,2,3),
dos_data as (
select date,
loc_data.loc,
item,
greatest(nvl(shelf_life::float,1),1) as shelf_life,
rtv_nrtv,
nvl(max_stock,0) as rop,
nvl(soh_qty,0) as soh_qty,
nvl(salesq,0) as salesq,
nvl(salesq_adj,0) as salesq_adj,
nvl(bcrosq,0) as bcrosq,
nvl(bcrosq_adj,0) as bcrosq_adj,
nvl(rosq_7d,0) as rosq_7d,
nvl(rosq_7d_adj,0) as rosq_7d_adj,
nvl(rosq_adj_3m,0) as rosq_adj_3m,
greatest(salesq,salesq_adj,bcrosq,bcrosq_adj,rosq_7d,rosq_7d_adj,rosq_adj_3m) as sales_trend
from analytics.dos join loc_data on loc_data.loc = analytics.dos.loc
where dept <> 85 and date between {{start_date}} and {{end_date}}),
joined_data as (
select soh_data.date,soh_data.loc,soh_data.item,avg_cost,
shelf_life,
rtv_nrtv,
nvl(shelf_life,1)<=30 as short_shelf_life_flag,
sales_trend,
soh_data.soh_qty,
expired_qty,
rop,
case when soh_data.soh_qty>2*rop then 'excess' else 'ok'end as excess_flag
from soh_data
full outer join dos_data on soh_data.date = dos_data.date and soh_data.loc = dos_data.loc and soh_data.item = dos_data.item
full outer join adj_data on soh_data.date = adj_data.dt and soh_data.loc = adj_data.location and soh_data.item = adj_data.item)
select
last_day(date) as month,
short_shelf_life_flag,
excess_flag,
round(avg(shelf_life),1) as avg_shelf_life,
round(sum(expired_qty*avg_cost)/1e5,1) as expired_value,
round(sum(sales_trend*avg_cost)/1e5,1) as sales_trend_value,
round(sum(soh_qty*avg_cost)/1e5/count(distinct date),1) as avg_inv_value,
round(sum(rop*avg_cost)/1e5/count(distinct date),1) as avg_rop_value
from joined_data
where date between {{start_date}} and {{end_date}}
group by 1,2,3
order by 3,2,1 desc
Damage
with
loc_data as (
select * from master.loc
where loc_type = 'DARKSTORE'and (close_date is null or inactive_date is null)
and loc in (1014,3688,4051, 4054, 4055, 4057,4061,4062,4063,4064,4065,4066)),
soh_data as (select s.date,
s.loc, s.item,
sum(stock_on_hand*av_cost) as inventory_value,sum(stock_on_hand) as soh_qty,
case when sum(stock_on_hand) <> 0 then sum(stock_on_hand*av_cost)/sum(stock_on_hand) else avg(av_cost) end as avg_cost
from tran.soh s
join master.item i on s.item = i.item
join loc_data l on s.loc = l.loc
where s.date between {{start_date}} and {{end_date}}
and dept <> 85
group by 1,2,3),
adj_data as (
select date(created_at) as dt,location,ia.item,
sum(case when reason IN (87) then -1.0*quantity else quantity end) as adj_qty,
sum(case when reason in (9) then quantity else 0.0 end) as expired_qty,
sum(case when reason in (11) then quantity else 0.0 end) as damage_qty,
sum(case when reason in (113,114) then quantity else 0.0 end) as ret_adj,
sum(case when reason IN (87) then -1.0*quantity when reason in (88) then quantity else 0.0 end) as shrink_qty,
sum(case when reason IN (7,112) then quantity else 0.0 end) as dump_qty,
sum(case when reason IN (7,88,113,114,9,11,112) then quantity when reason IN (87) then -1.0*quantity else 0.0 end) as valid_adj_qty
from fnvportal_prod.fnvportalapp_inventoryadjustment ia
join loc_data l on ia.location = l.loc
join master.item i on ia.item = i.item
and approval_status = 'AP' and rtv_file_name = ''
where date(created_at) between{{start_date}} and {{end_date}}
and dept <> 85
group by 1,2,3),
dos_data as (
select date,
loc_data.loc,
master.item.item,
greatest(nvl(analytics.dos.shelf_life::float,1),1) as shelf_life,
nvl(max_stock,0) as rop,
nvl(soh_qty,0) as soh_qty,
nvl(salesq,0) as salesq,
nvl(salesq_adj,0) as salesq_adj,
nvl(bcrosq,0) as bcrosq,
nvl(bcrosq_adj,0) as bcrosq_adj,
nvl(rosq_7d,0) as rosq_7d,
nvl(rosq_7d_adj,0) as rosq_7d_adj,
nvl(rosq_adj_3m,0) as rosq_adj_3m,
greatest(salesq,salesq_adj,bcrosq,bcrosq_adj,rosq_7d,rosq_7d_adj,rosq_adj_3m) as sales_trend,
nvl(net_weight::float,0) as nwt
from analytics.dos join loc_data on loc_data.loc = analytics.dos.loc
join master.item on master.item.item = analytics.dos.item
where master.item.dept <> 85 and date between {{start_date}} and {{end_date}}),
joined_data as (
select soh_data.date,soh_data.loc,soh_data.item,avg_cost,
shelf_life,
nvl(shelf_life,1)<=30 as short_shelf_life_flag,
sales_trend,
soh_data.soh_qty,
damage_qty,
rop,
case when soh_data.soh_qty>2*rop then 'excess' else 'ok'end as excess_flag,
nwt<=2 as small_nwt_flag
from soh_data
full outer join dos_data on soh_data.date = dos_data.date and soh_data.loc = dos_data.loc and soh_data.item = dos_data.item
full outer join adj_data on soh_data.date = adj_data.dt and soh_data.loc = adj_data.location and soh_data.item = adj_data.item)
select
last_day(date) as month,
round(avg(shelf_life),1) as avg_shelf_life,
round(sum(damage_qty*avg_cost)/1e5,1) as damaged_value,
round(sum(sales_trend*avg_cost)/1e5,1) as sales_trend_value,
round(sum(soh_qty*avg_cost)/1e5/count(distinct date),1) as avg_inv_value,
round(sum(rop*avg_cost)/1e5/count(distinct date),1) as avg_rop_value
from joined_data
where date between {{start_date}} and {{end_date}}
group by 1
order by 1 desc
Overall Wastages BDF
start_date = '2024-06-01'
#end_date = '2024-07-31'
end_date = datetime.now().strftime("%Y-%m-%d")
with
loc_data as (
select * from master.loc
where loc_type in ('DARKSTORE','STORE') and (close_date is null or inactive_date is null)),
item_data as (
select * from master.item
where division_name = 'BDF'
),
soh_data as (select s.date,
s.loc, s.item,
sum(stock_on_hand*av_cost) as inventory_value,sum(stock_on_hand) as soh_qty,
case when sum(stock_on_hand) <> 0 then sum(stock_on_hand*av_cost)/sum(stock_on_hand) else avg(av_cost) end as avg_cost
from tran.soh s
join item_data i on s.item = i.item
join loc_data l on s.loc = l.loc
where s.date between {{start_date}} and {{end_date}}
and dept <> 85
group by 1,2,3),
adj_data as (
select date(created_at) as dt,location,ia.item,
sum(case when reason IN (87) then -1.0*quantity else quantity end) as adj_qty,
sum(case when reason in (9) then quantity else 0.0 end) as expired_qty,
sum(case when reason in (11) then quantity else 0.0 end) as damage_qty,
sum(case when reason in (113,114) then quantity else 0.0 end) as ret_adj,
sum(case when reason IN (87) then -1.0*quantity when reason in (88) then quantity else 0.0 end) as shrink_qty,
sum(case when reason IN (7,112) then quantity else 0.0 end) as dump_qty,
sum(case when reason IN (7,88,113,114,9,11,112) then quantity when reason IN (87) then -1.0*quantity else 0.0 end) as valid_adj_qty
from fnvportal_prod.fnvportalapp_inventoryadjustment ia
join loc_data l on ia.location = l.loc
join item_data i on ia.item = i.item
and approval_status = 'AP' and rtv_file_name = ''
where date(created_at) between{{start_date}} and {{end_date}}
group by 1,2,3),
dos_data as (
select date,
loc_data.loc,
i.item,
greatest(nvl(dos.shelf_life::float,1),1) as shelf_life,
nvl(max_stock,0) as rop,
nvl(soh_qty,0) as soh_qty,
nvl(salesq,0) as salesq,
nvl(salesq_adj,0) as salesq_adj,
nvl(bcrosq,0) as bcrosq,
nvl(bcrosq_adj,0) as bcrosq_adj,
nvl(rosq_7d,0) as rosq_7d,
nvl(rosq_7d_adj,0) as rosq_7d_adj,
nvl(rosq_adj_3m,0) as rosq_adj_3m,
greatest(salesq,salesq_adj,bcrosq,bcrosq_adj,rosq_7d,rosq_7d_adj,rosq_adj_3m) as sales_trend,
nvl(net_weight::float,0) as nwt
from analytics.dos dos join loc_data on loc_data.loc = dos.loc
join item_data i on i.item = dos.item
where date between {{start_date}} and {{end_date}}),
joined_data as (
select soh_data.date,soh_data.loc,soh_data.item,avg_cost,
shelf_life,
nvl(shelf_life,1)<=30 as short_shelf_life_flag,
sales_trend,
soh_data.soh_qty,
dump_qty,
expired_qty,
shrink_qty,
rop,
case when soh_data.soh_qty>2*rop then 'excess' else 'ok'end as excess_flag,
nwt<=2 as small_nwt_flag
from soh_data
full outer join dos_data on soh_data.date = dos_data.date and soh_data.loc = dos_data.loc and soh_data.item = dos_data.item
full outer join adj_data on soh_data.date = adj_data.dt and soh_data.loc = adj_data.location and soh_data.item = adj_data.item)
select
last_day(date) as month,
short_shelf_life_flag,
excess_flag,
round(avg(shelf_life),1) as avg_shelf_life,
round(sum(dump_qty*avg_cost)/1e5,1) as dump_value,
round(sum(expired_qty*avg_cost)/1e5,1) as expired_value,
round(sum(shrink_qty*avg_cost)/1e5,1) as shrink_value,
round(sum(sales_trend*avg_cost)/1e5,1) as sales_trend_value,
round(sum(soh_qty*avg_cost)/1e5/count(distinct date),1) as avg_inv_value,
round(sum(rop*avg_cost)/1e5/count(distinct date),1) as avg_rop_value,
round(((sum(dump_qty*avg_cost)+sum(expired_qty*avg_cost))*1e2/sum(soh_qty*avg_cost)),1) as perc_inv_adj
from joined_data
where date between {{start_date}} and {{end_date}}
group by 1,2,3
order by 1,2,3 desc