Following is the logic of FIFO Inventory Method.
Stock_Summary:
Load distinct
MATERIAL,
PLANT,
Storage_Location,
M_Date ,
SUM(Quantity_Received_Into_Valuated_Stock - Quantity_Issued_From_Valuated_Stock) as Rec_Issue_Valuated_Stock,
SUM(Receipt_Quantity_Blocked_Stock - Issue_Quantity_Blocked_Stock) as Rec_Issue_Blocked_Stock,
SUM(Quantity_Received_Into_Consignment_Stock - Quantity_Issued_From_Consignment_Stock) as Rec_Issue_Consignment_Stock,
SUM(Receipt_Quantity_Stock_In_Quality_Inspection - Issue_Quantity_Stock_In_Quality_Inspection) as Rec_Issue_Quality_Inspection_Stock,
//SUM(Issue_Quantity_Stock_In_Transit- Receipt_Quantity_Stock_In_Transit) as Rec_Issue_Intransit_Stock,
SUM(Value_Received_Into_Valuated_Stock - Value_Issued_From_Valuated_Stock) AS Rec_Issue_Value_Valuated_Stock ,
SUM(Receipt_Quantity_Stock_In_Transit) as Rec_Issue_Intransit_Stock
resident Stock_Master
group by PLANT,Storage_Location, MATERIAL,M_Date ;
drop table Stock_Master;
Stock_Summary_Index:
load
*,
recno() as d
resident Stock_Summary order by PLANT,Storage_Location, MATERIAL, M_Date;
drop table Stock_Summary;
Material_Stock:
Load
MATERIAL,
PLANT,
Storage_Location,
M_Date ,
Rec_Issue_Valuated_Stock,
Rec_Issue_Intransit_Stock,
Rec_Issue_Blocked_Stock,
Rec_Issue_Consignment_Stock,
if(Peek(MATERIAL) = MATERIAL and Peek(PLANT) = PLANT and Peek(Storage_Location) = Storage_Location ,Peek(Total_Valuated_Stock)+Rec_Issue_Valuated_Stock,Rec_Issue_Valuated_Stock) as Total_Valuated_Stock,
if(Peek(MATERIAL) = MATERIAL and Peek(PLANT) = PLANT and Peek(Storage_Location) = Storage_Location ,Peek(Total_Valuated_Stock)) as Opening_Valuated_Stock,
if(Peek(MATERIAL) = MATERIAL and Peek(PLANT) = PLANT and Peek(Storage_Location) = Storage_Location ,Peek(Total_Blocked_Stock)+Rec_Issue_Blocked_Stock, Rec_Issue_Blocked_Stock) as Total_Blocked_Stock,
if(Peek(MATERIAL) = MATERIAL and Peek(PLANT) = PLANT and Peek(Storage_Location) = Storage_Location ,Peek(Total_Consignment_Stock)+Rec_Issue_Consignment_Stock, Rec_Issue_Consignment_Stock) as Total_Consignment_Stock,
if(Peek(MATERIAL) = MATERIAL and Peek(PLANT) = PLANT and Peek(Storage_Location) = Storage_Location ,Rec_Issue_Intransit_Stock) as Total_Intransit_Stock,
if(Peek(MATERIAL) = MATERIAL and Peek(PLANT) = PLANT and Peek(Storage_Location) = Storage_Location ,Peek(Total_Value_Valuated_Stock)+Rec_Issue_Value_Valuated_Stock, Rec_Issue_Value_Valuated_Stock) as Total_Value_Valuated_Stock
resident Stock_Summary_Index order by d;
drop field d;
drop table Stock_Summary_Index;
store Material_Stock into ..\Data\Modelled QVD\Material_Stock.qvd;
Drop Table Material_Stock;
//```````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````
Stock_Summary:
LOAD
MATERIAL,
PLANT,
Storage_Location,
M_Date ,
Rec_Issue_Valuated_Stock,
Rec_Issue_Intransit_Stock,
Rec_Issue_Blocked_Stock,
Rec_Issue_Consignment_Stock,
Total_Valuated_Stock,
Opening_Valuated_Stock,
Total_Blocked_Stock,
Total_Consignment_Stock,
Total_Intransit_Stock,
Total_Value_Valuated_Stock
FROM
[..\Data\Modelled QVD\Material_Stock.qvd]
(qvd);
RIGHT JOIN (Stock_Summary) LOAD
*,
if( previous(PLANT) = PLANT AND // If the same Product,
previous(Storage_Location) = Storage_Location AND
previous(MATERIAL) = MATERIAL ,
date(previous(M_Date)-1) // Assign the prior day
, '' ) as EndDate // Otherwise assign infinity
RESIDENT Stock_Summary
// The ORDER is important. We must group by Product, and within Product, we must read Descending -- most recent Price first.
ORDER BY PLANT,Storage_Location,MATERIAL, M_Date DESC ;
/*
Determine the lowest date in the entire transaction set.
We will use this to generate the range of dates.
*/
MinDate:
LOAD
num(min(M_Date)) as minAllDate
RESIDENT Stock_Summary
;
LET vStartDate = fieldValue('minAllDate',1)-1; // Put the mindate in a variable for later loop
DROP TABLE MinDate; // No longer need the table
/*
Generate a table of Dates from the minDate up through and including today.
*/
LET vEndDate = num(today(1)); // Generate dates through today
//-----------------------------------------------------------------------------------------------------------
StockMaster: // This will be the new pricing table
LOAD *, month(Date) as Month // Add a Month dimension to ease navigation
;
LOAD
date($(vStartDate) + IterNo(),'DD.MM.YYYY') as Date
AUTOGENERATE 1
WHILE $(vStartDate) + IterNo() <= $(vEndDate); //OK
/*
Use IntervalMatch to JOIN PriceMaster and PriceTransaction by StartDate/EndDate range.
*/
LEFT JOIN (StockMaster) IntervalMatch(Date)
LOAD
DISTINCT M_Date,
EndDate
RESIDENT Stock_Summary; //OK
/*
JOIN the Price field to PriceMaster to allow us to drop PriceMaster and
avoid the synthetic key.
*/
LEFT JOIN (StockMaster) LOAD * RESIDENT Stock_Summary; //OK
//------------------------------------------------------------------------------------------------------------
//store StockMaster into ..\Data\Modelled QVD\Summarize_Stock_Master.qvd; //
DROP TABLE Stock_Summary;
// We don't need the StartDate & EndDate fields anymore
