Issue having my SQL generate values for “Monthly Inventory Turns by Dollars” chart… Can someone assist ?
select tbl.*,tbl.Item_number as item_number1,tbl.Item_number as item_number2,
tbl.SUBINVENTORY_CODE as SUBINVENTORY_CODE1,tbl.SUBINVENTORY_CODE as SUBINVENTORY_CODE2
from (with cal_onhand as ( select nvl(sum(iub1.PRIMARY_QUANTITY),0) as ON_HAND_QTY_AS_ON_DATE,IUB1.INVENTORY_ITEM_ID,IUB1.SUBINVENTORY_CODE , TRANS_MONTH,day,MONTH,YEAR,ORGANIZATION_ID
from INV_MATERIAL_TXNS iub1,(select dt1,to_char(dt1, 'MM-YYYY','NLS_DATE_LANGUAGE = AMERICAN') AS TRANS_MONTH, CAST(to_char(LAST_DAY(dt1),'dd') AS INT) day ,EXTRACT(YEAR FROM DT1) YEAR,EXTRACT(MONTH FROM DT1) AS MONTH from (select TO_DATE(trunc(:P_FROM_DATE) +rownum-1,'YYYY-MM-DD') dt1
from all_objects
where rownum <= trunc(:P_TO_DATE)-trunc(:P_FROM_DATE)+1) )t2
where trunc(iub1.TRANSACTION_date)<dt1
group by IUB1.INVENTORY_ITEM_ID,IUB1.SUBINVENTORY_CODE,TRANS_MONTH,day,MONTH,YEAR,ORGANIZATION_ID),
onhand as (select esi.inventory_item_id, ESI.ITEM_NUMBER ,
IODV.ORGANIZATION_NAME,
ESI.DESCRIPTION ,
IOQD.ORGANIZATION_ID,
IOP.ORGANIZATION_CODE ,
UOMT.UNIT_OF_MEASURE as stock_uom ,
IOQD.SUBINVENTORY_CODE ,
(IIL.SEGMENT1 ||'.'||
IIL.SEGMENT2||'.'||
IIL.SEGMENT3||'.'||
IIL.SEGMENT4) as locator,
esi.list_price_per_unit
from INV_ONHAND_QUANTITIES_DETAIL IOQD ,
EGP_SYSTEM_ITEMS ESI ,
INV_ORG_PARAMETERS IOP ,
INV_ORGANIZATION_DEFINITIONS_V IODV,
INV_UNITS_OF_MEASURE_TL UOMT ,
INV_UNITS_OF_MEASURE_B UOMB,
INV_ITEM_LOCATIONS IIL
where 1 = 1
and IODV.ORGANIZATION_ID =IOP.ORGANIZATION_ID
and IOQD.INVENTORY_ITEM_ID = ESI.INVENTORY_ITEM_ID
and IOQD.ORGANIZATION_ID = ESI.ORGANIZATION_ID
and ESI.ORGANIZATION_ID = IOP.ORGANIZATION_ID
and UOMT.UNIT_OF_MEASURE_ID = UOMB.UNIT_OF_MEASURE_ID
and UOMB.UOM_CODE = IOQD.TRANSACTION_UOM_CODE
and IOQD.ORGANIZATION_ID = IIL.ORGANIZATION_ID(+)
and IOQD.SUBINVENTORY_CODE = IIL.SUBINVENTORY_CODE(+)
and IOQD.LOCATOR_ID =IIL.INVENTORY_LOCATION_ID(+)
),
issue as (select esi.inventory_item_id, ESI.ITEM_NUMBER ,
IUB.ORGANIZATION_ID,
iub.SUBINVENTORY_CODE ,
sum(abs(iub.primary_quantity)) as issue_quantity
,max(iub.Transaction_UOM) as issue_uom
,to_char(Transaction_DATE, 'MM-YYYY','NLS_DATE_LANGUAGE = AMERICAN') AS TRANS_MONTH
from
EGP_SYSTEM_ITEMS ESI ,
INV_MATERIAL_TXNS iub,
INV_TRANSACTION_TYPES_TL itt
where 1=1
and IUB.INVENTORY_ITEM_ID=ESI.INVENTORY_ITEM_ID
and ESI.ORGANIZATION_ID=IUB.ORGANIZATION_ID
and itt.TRANSACTION_TYPE_ID=iub.TRANSACTION_TYPE_ID
AND trunc(iub.Transaction_DATE) between trunc(:P_FROM_DATE) and trunc(:P_TO_DATE)
AND upper(itt.transaction_type_name) In ('MOVEMENT REQUEST TRANSFER', 'ACCOUNT ALIAS ISSUE', 'ACCOUNT ISSUE', 'MISCELLANEOUS ISSUE', 'MOVEMENT REQUEST ISSUE', 'RESIDUAL QUANTITY ISSUE', 'SALES ORDER ISSUE', 'TRANSFER ORDER ISSUE', 'WORK IN PROCESS MATERIAL ISSUE', 'WORK IN PROCESS NEGATIVE MATERIAL ISSUE')
group by esi.inventory_item_id,
ESI.ITEM_NUMBER ,
IUB.ORGANIZATION_ID,
iub.SUBINVENTORY_CODE
,to_char(Transaction_DATE, 'MM-YYYY','NLS_DATE_LANGUAGE = AMERICAN') ),
DATA1 as (select a.locator
,a.SUBINVENTORY_CODE,a.item_number,b.issue_quantity SUM_ISSUE_QTY,b.issue_uom,
stock_uom,a.inventory_item_id, a.list_price_per_unit,
a.ORGANIZATION_NAME,
a.DESCRIPTION ,
a.ORGANIZATION_ID,
a.ORGANIZATION_CODE , b.TRANS_MONTH
,nvl((issue_quantity)*list_price_per_unit,0) SUM_TRANS_AMT,
c.TRANS_MONTH as mon_yyyy,day,MONTH,YEAR,
(trunc(:P_TO_DATE)-trunc(:P_FROM_DATE)+1 ) AS num_day,
nvl(ON_HAND_QTY_AS_ON_DATE,0) SUM_ON_HAMD_QTY
,nvl((ON_HAND_QTY_AS_ON_DATE*list_price_per_unit),0) SUM_ON_HAMD_AMT
,(nvl(ON_HAND_QTY_AS_ON_DATE,0)/day) as avg_onhand_qty,((nvl(ON_HAND_QTY_AS_ON_DATE,0)/day)*list_price_per_unit) as avg_onhand_amt
from onhand a,issue b,cal_onhand c
where a.inventory_item_id=b.inventory_item_id
and a.SUBINVENTORY_CODE=b.SUBINVENTORY_CODE
and a.ORGANIZATION_ID=b.ORGANIZATION_ID
and a.inventory_item_id=c.inventory_item_id
and a.SUBINVENTORY_CODE=c.SUBINVENTORY_CODE
and a.ORGANIZATION_ID=c.ORGANIZATION_ID
and c.trans_month=b.trans_month
),
data as (select * from data1
union
select a.locator
,a.SUBINVENTORY_CODE,a.item_number, 0 SUM_ISSUE_QTY,'NA' issue_uom,
stock_uom,a.inventory_item_id, a.list_price_per_unit,
a.ORGANIZATION_NAME,
a.DESCRIPTION ,
a.ORGANIZATION_ID,
a.ORGANIZATION_CODE , 'NA' TRANS_MONTH
,0 SUM_TRANS_AMT,
c.TRANS_MONTH as mon_yyyy,day,MONTH,YEAR,
(trunc(:P_TO_DATE)-trunc(:P_FROM_DATE)+1 ) AS num_day,
nvl(ON_HAND_QTY_AS_ON_DATE,0) SUM_ON_HAMD_QTY
,nvl((ON_HAND_QTY_AS_ON_DATE*list_price_per_unit),0) SUM_ON_HAMD_AMT
,(nvl(ON_HAND_QTY_AS_ON_DATE,0)/day) as avg_onhand_qty,((nvl(ON_HAND_QTY_AS_ON_DATE,0)/day)*list_price_per_unit) as avg_onhand_amt
from onhand a,cal_onhand c
where 1=1
and a.inventory_item_id=c.inventory_item_id
and a.SUBINVENTORY_CODE=c.SUBINVENTORY_CODE
and a.ORGANIZATION_ID=c.ORGANIZATION_ID
AND item_number||C.SUBINVENTORY_CODE NOT IN (SELECT DISTINCT (item_number||SUBINVENTORY_CODE) FROM ISSUE B
WHERE c.trans_month=b.trans_month)
),
annual as (SELECT nvl(sum(SUM_TRANS_AMT),0) SUM_TRANS_AMT,
nvl(sum(SUM_ISSUE_QTY),0) SUM_ISSUE_QTY,nvl(sum(SUM_ON_HAMD_QTY),0) SUM_ON_HAMD_QTY,a.inventory_item_id,a.SUBINVENTORY_CODE,round((nvl(sum(SUM_ON_HAMD_QTY),0)/(trunc(:P_TO_DATE)-trunc(:P_FROM_DATE)+1 )),6) as avg_onhand_qty
from data A
group by a.inventory_item_id,a.SUBINVENTORY_CODE)
SELECT A.*,ROUND((((B.SUM_ISSUE_QTY/(A.num_day-1))/decode(B.avg_onhand_qty,0,1,B.avg_onhand_qty))*365),4) AS Annualized_Inventory_Turn,
nvl(ROUND((((A.SUM_ISSUE_QTY/(A.num_day-1))/decode(A.avg_onhand_qty,0,1,A.avg_onhand_qty))*12),4),0) AS Annualized_Inventory_Turn_MON,
nvl(rOUND(((A.SUM_TRANS_AMT/(A.num_day-1))/decode(A.avg_onhand_amt,0,1,a.avg_onhand_amt)),4),0) AS Inventory_Turn, a.day as last_day
FROM DATA A,annual B
WHERE A.INVENTORY_ITEM_Id=B.INVENTORY_ITEM_ID
AND A.SUBINVENTORY_CODE=B.SUBINVENTORY_CODE
) tbl
order by month asc,year asc