BI Inventory Aging Query Author: Srinivas Neelam. Summary: Inventory Aging query to determine age of available stocks. Sample Layout: atorar Age Beat Day rial Group ‘Overall Result Example: Avail. >730 Bt: 366-730 | Bt: 180-365 Bt: 0-180, Stock Days Days Days Days Today Receipt Stock 1000 o 500 300 1800 Issue Stock 200 300 400 200 +100 Balance Previous Balance Previous Receipts - Issues intime |+Receipts- | Previous Balance + | Previous Balance + | available Logie bucket Issues Receipts Issues| Receipts Issues | Balance Formula | 1000 - 200 800 + 0-300 [500+ 500-400 | 600 + 300-200 Balance stock 800 500 600 zoo | __700 aaa Previous Stock Balance(if Previous Stock Previous Stock Negative) + Balancet Balance(i Negative) Current Bucket Negative) + Current | + Current Bucket. Receipts ORit | Bucket Receipts | Receipts OR i Positive Current | ORI Positive Positive Current Receipt - (All Issues till Bucket Receipts | Current Bucket Bucket Receipts Logie current date) Only Receipts Only | Oni 1000 - 200- 300-400- Formula__| 200 -100 +0 = 100 + 500 300 Derived Value 100 100 400 300 Final Value Positive or Zeroilf 400 + 300 Negative) 0 o 400 300 700BI Inventory Aging Query Author: Srinivas Neelam. Summary * Calculation of calculated keyfigures based on FIFO logic * Query based on standard inventory CUBE: OIC_C03 - Material Stocks/Movements © Gives proper results at lowest granularity (Material or Batch), due to different Units of Measure for different materials and No Dimension used to derive differences. © Same query can be created to Value fields also. Or can also add value fields to same query. * If batch is not available then calculation takes place based on FIFO assumption. * Ifbatch is available in drill down, it will take care of Receipts and Issues based on Batch number along with FIFO logic. Due to this query may show different output results with and with out Batch drill down, * Aim of this query is to display age of stock for finished goods(Batch available) only. Can be used to other materials by considering above limitations,BI Inventory Aging Query Author: Srinivas Neelam Steps: 1. Creation of Restricted Keyfigures for Receipt Quantity: 1.1. Create Restricted Keyfigure for Receipt Quantity according to selection (here _ more than 366 days and less than 731 days) ‘Sores |Aeowomon | Gacy | Avonced | Canvemens | Fava | Cleans | “RRRARR TREES Si 1.2. Create Restricted Keyfigure for Receipt Quantity according to selection (here more than 180 days and less than 366 days) "HB Gh Se ‘Cent |szeiion| Ds | Avance | Common | Peng | Casto | Ee > ae Pc ee eae) altri rare ee no (MB IORECVALSTCHI Rectavalstock Bp oa Sen ecnee Ce ny A E26: Cec Dy HP EY «| SeBI Inventory Aging Query Author: Srinivas Neelam 1.3, Some more Restricted Keyfigures for Receipt Quantity. Sa Cerro Ker Fee [i mcevis io paconvsoce BAe Coen Dor {BH Dont OAT 11 Came Calder Do AP a 20- Caner er Day (SAP EaBI Inventory Aging Query Author: Srinivas Neelam 2. Creation of Restricted Keyfigures for Receipt Quantity: 2.1, In Same Way, create restricted Keyfigures for Issue Quantity as follows: (0) a TeeP Kr Fase Cp aan {3 00218” SOAT caret senate SAP) 68a ese Dy SAP a1 2.2. Some more restricted Keyfigures for Issue Quantity:BI Inventory Aging Query Author: Srinivas Neelam © ai wenlenrtcaeran oo {BF Dost 100 COAT an Camere Cen Day SAP Ge) 189: Cret Cer ay SAP B91 3. Creation of Calculated Keyfigures for stock derivation:BI Inventory Aging Query Author: Srinivas Neelam. 3.1, Create Calculated Keyfigure or Formula to derive Stock > 730 Days. Logic: Subtract total Issue Quantities (in last 730 days) from stock available before 730 Days. You may get Negative or Positive value. All calculations based on FIFO Logie. Note: As we are calculating on Quantity here used No Dimension, report gives proper results if it is displayed at Material Level or low Only (Batch). FEB change ‘General | Aozreostion | Desi | Advenced | Convemions | Penning | Caleuetions Ee NODIM( [OVALSTCKGTY] Stock Avail > 730 Days") -(NODIMC? |/5 2/3 | “OISSVALSCRP] Issue Bt: 366 to 730 Days' ) >= NODIMCT? “OISSVALSTCXI Issue 365 Days") = NODIMC 1? “OISSVALSTGXI lsoue BE 91 to 180 Days") + NODIM T? 3.2, Create Calculated Key igure or Formula to derive Stock bt: 366 to 730 Days.BI Inventory Aging Query Author: Srinivas Neelam. Logic: As already explained Stock > 730 Days may Positive or Negative, If its Negative: Should adds to next bucket Receipts (bt: 366 to 730 Days). If its Positive: Only next buckets Receipts only should display. ( Since old stock still available to Issue). Based on FIFO Logic. See details in Example. 3.3. One more Calculated or restricted keyfigure for 3 Bucket:BI Inventory Aging Query Author: Srinivas Neelam Logic: Same as before (Point 4), Consider current bucket(bt: 181 to 365 days) and pervious bucket (Stock bt: 366 to 730 Days) age [CStock bt 181 te 365 Days" = 0) ~ (NODIAK TORECVALSTOX] > to 180 Days") NODIM( "Stock bt 181 to 365” (en) [non] [in] [son] [sm ae [ne] (nae |eatiatte Operant SB Agro, SSS 4, Final Formul/calculated keyfigure to display in report:BI Inventory Aging Query Author: Srinivas Neelam 4.1, As we are getting Positive or Negative Values out of Previous formulas and ‘we need to show only Positive or Zero (If its Negative), here Boolean operation used to achieve same. Tete Tol TT] oo Sea oe “gtcat Ave yl ae" 35 Lain din) [il (nh em) [dn] [in] [ee] Operands gg vera Acasa T grate ee a 8 S 8 8 reco Beavis "sce 335 om Fay att a fa Fax Nolet