How to get Qty from InventTrans based on Reference Category

(0) Share

Report

Posted on by premK6969

198

Hi there, we got an requirement to develop stock movement analysis report . My problem is i want to print the value of Qty for those particular ItemIds whose reference Category is ConsignmentReplenishmentOrder or Purchase Order etc. I have added my code as per the condition but not able to populate Qty from InventTrans as per the Reference Category in InventTransOrigin. Please find my code below and suggest me corrections as my code returning Qty either one record or for all the records. I need to get the Qty value for that particular ItemId if it has the selected Reference Category. Thanks in advance.

  while select * from inventTrans join inventTransOrigin  where 
  inventTrans.InventTransOrigin == inventTransOrigin.RecId &&
            inventTransOrigin.ReferenceCategory==InventTransType::ConsignmentReplenishmentOrder
             inventTrans.DatePhysical >= repFromDate && inventTrans.DatePhysical <= repToDate
           
            {
            
                        
                    reporttable.OpeningConsignmentStockQty = inventTrans.Qty;   // Opening Consignment Stock Qty
                    
                    reporttable.insert();
            
            }
            
            
            The above code not printing Qty correctly for that particular ItemId

I have the same question (0)

All responses (21)

Answers (1)

Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report
Hi premK6969,

I don't see ItemId anywhere in your code. If you need to get the information for one ItemId you need to include it in your statement.

Something like this:

select sum(Qty) from inventTrans where inventTrans.ItemId == myItemId && inventTrans.DatePhysical >= repFromDate && inventTrans.DatePhysical <= repToDate exists join inventTransOrigin where inventTrans.InventTransOrigin == inventTransOrigin.RecId && inventTransOrigin.ReferenceCategory==InventTransType::ConsignmentReplenishmentOrder; reporttable.OpeningConsignmentStockQty = inventTrans.Qty; reporttable.insert();

Was this reply helpful? Yes No
premK6969 198 on at

Like (0)

Report

Hi Nikolaos, i need it for the ItemIds which has Reference Category Consignment Replenishment Order within the given report parametres. Please be informed that there are other fields also which needs to populate Qty of the ItemId which has reference Category like SalesOrder, Purchase Order etc. So the code should populate Qty individually if it satisfies the required condition not for only one order

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report
If you need it for all matching item ids then the code would be like this:

select sum(Qty) from inventTrans group by ItemId && inventTrans.DatePhysical >= repFromDate && inventTrans.DatePhysical <= repToDate exists join inventTransOrigin where inventTrans.InventTransOrigin == inventTransOrigin.RecId && inventTransOrigin.ReferenceCategory==InventTransType::ConsignmentReplenishmentOrder; reportTable.clear(); reportTable.ItemId = inventTrans.ItemId; reporttable.OpeningConsignmentStockQty = inventTrans.Qty; reporttable.insert();

I think I don't understand the rest of your description. But hopefully my illustration helps you in developing the code that fits your exact requirement. At least it shows how to get Qty of inventory transactions for specific reference category.

Was this reply helpful? Yes No
premK6969 198 on at

Like (0)

Report
Hi Nikolaos, actually my requirement is to populate multiple fields from InvetTrans out of which Opening Consignment Stock Qty is the only field which has reference category as consignment replenishment Order. Your Code actually not meeting the exact requirement. Please find the below code attached for reference.

while select * from inventTrans where inventTrans.DatePhysical >= repFromDate && inventTrans.DatePhysical <= repToDate { reporttable.ItemId = inventTrans.ItemId; // Item number if( inventTrans.DatePhysical == repFromDate) { reporttable.OpeningStockQty = inventTrans.Qty; // Opening Stock Qty reporttable.OpeningStockValue = inventTrans.costValue(); // Opening Stock Value } if(inventTrans.DatePhysical == repToDate) { reporttable.ClosingStockQty = inventTrans.Qty; // Closing Stock Qty reporttable.ClosingStockValue = inventTrans.costValue() ; // Closing Stock Value } // Here i trying to assign InventTrans.Qty value to my report fields whose Reference Category is ConsignmentReplishmentOrder reporttable.OpeningConsignmentStockQty = inventTrans.Qty; // Opening Consignment Stock Qty reporttable.ClosingConsignmentStockQty = inventTrans.Qty; // Closing COnsignment Stock Qty // In the similar way i need to assign InventTrans.Qty value to my report fields whose reference Category is Sales Order and Inventory Adjustment reporttable.TotalArrivalsQty = inventTrans.Qty; // Total arrivals Qty // In the similar way i need to assign InventTrans.Qty value to my report fields whose reference Category is Transfer and Counting reporttable.TotalStockQty = inventTrans.Qty; // Total arrivals Qty reporttable.insert(); }

I need how can i select InventTRansOrigin with InventTrans based on that Reference Category for that particular ItemId and to populate InventTrans.Qty

Thanks ,

Prem

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

Hi premK6969,

next time, could you please share your actual business requirement right in the beginning? This would save your time.

Now you have brought some new requirements to the table. To me it seems that you can't fulfill your requirement with one query. My earlier advice is still valid for fulfilling the requirement that you originally described. If you need to populate some other fields using some other criteria, you can simply add new queries to get the data that matches your requirement. Basically since you now know how to query these tables, you can apply the knowledge to fetch information based on different criterias, and solve your business requirement.

Please let us know if there's anything unclear. Thanks!

Was this reply helpful? Yes No
premK6969 198 on at

Like (0)

Report
Hi Nikolas, i actually tried with your coding approach but still the expected Value is not populating. Like i mentioned in my comments i need to populate Qty for ItemId which has different ReferenceCategory. And i didn't want to get the sum and i just need the value of Qty of that ItemId to populate in the report. Please find my code modified as per your code. Please correct me the corrections

. Thanks for understanding in advance

while select * from inventTrans where inventTrans.DatePhysical >= repFromDate && inventTrans.DatePhysical <= repToDate { reporttable.ItemId = inventTrans.ItemId; // Item number if( inventTrans.DatePhysical == repFromDate) { reporttable.OpeningStockQty = inventTrans.Qty; // Opening Stock Qty reporttable.OpeningStockValue = inventTrans.costValue(); // Opening Stock Value } if(inventTrans.DatePhysical == repToDate) { reporttable.ClosingStockQty = inventTrans.Qty; // Closing Stock Qty reporttable.ClosingStockValue = inventTrans.costValue() ; // Closing Stock Value } select Qty from inventTrans exists join inventTransOrigin where inventTrans.InventTransOrigin == inventTransOrigin.RecId && inventTransOrigin.ReferenceCategory==InventTransType::ConsignmentReplenishmentOrder; reporttable.OpeningConsignmentStockQty = inventTrans.Qty; // Opening Consignment Stock Qty reporttable.ClosingConsignmentStockQty = inventTrans.Qty; // Closing COnsignment Stock Qty reporttable.insert(); } // The above still not populating the Qty value of Reference Category:: ConsignmentReplenishmentOrder.

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report
When you say that you want the value of Qty, but not sum, can you elaborate on that?

There could be 20 000 records in InventTrans for one item and one reference category.

Your current code (lines 20-27) will select the first matching record (for any item id!) and put the qty in your report table. The remaining 19 999 quantities would be ignored.

Should your report contain one record for each InventTrans record? Or one record for each item id? If you want one record for each InvenTrans, then it could be implemented like this:

while select inventTrans where inventTrans.DatePhysical >= repFromDate && inventTrans.DatePhysical <= repToDate join inventTransOrigin where inventTransOrigin.RecId == inventTrans.InventTransOrigin { reporttable.ItemId = inventTrans.ItemId; // Item number if (inventTransOrigin.ReferenceCatagory == InventTransType::ConsignmentReplenishmentOrder) { reporttable.OpeningConsignmentStockQty = inventTrans.Qty; // Opening Consignment Stock Qty reporttable.ClosingConsignmentStockQty = inventTrans.Qty; // Closing COnsignment Stock Qty } }

Was this reply helpful? Yes No
premK6969 198 on at

Like (0)

Report
Hi Nikolaos, yes i need it for each itemid and in the report we need to group it by category and need to calculate average values accordingly. I had tried with the code you provided and its printing all the value for all the ItemIds and not for only one which has referencecategory Consignement Replenishment Order. If if add itemid condition also inside if condition still its showing same result. Please find the screenshot attached for more reference.

From the above picture, the one highlighted needs to print the Value 100 and the rest needs to be blank but its printing for all the ItemIds. Please find code i added for the same.

while select * from inventTrans where inventTrans.DatePhysical >= repFromDate && inventTrans.DatePhysical <= repToDate join inventTransOrigin where inventTransOrigin.RecId == inventTrans.InventTransOrigin { reporttable.ItemId = inventTrans.ItemId; // Item number if( inventTrans.DatePhysical == repFromDate) { reporttable.OpeningStockQty = inventTrans.Qty; // Opening Stock Qty reporttable.OpeningStockValue = inventTrans.costValue(); // Opening Stock Value } if(inventTrans.DatePhysical == repToDate) { reporttable.ClosingStockQty = inventTrans.Qty; // Closing Stock Qty reporttable.ClosingStockValue = inventTrans.costValue() ; // Closing Stock Value } if (inventTransOrigin.ReferenceCategory == InventTransType::ConsignmentReplenishmentOrder // tried with this first and not sucessful && inventTransOrigin.ItemId== inventTrans.ItemId) // added itemid still not sucessful { reporttable.OpeningConsignmentStockQty = inventTrans.Qty; // Opening Consignment Stock Qty reporttable.ClosingConsignmentStockQty = inventTrans.Qty; // Closing COnsignment Stock Qty } reporttable.insert(); }

Thanks

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

Hi,

the new condition that you added doesn't change anything, since the InventTransOrigin always has the same item id than InventTrans.

I don't see the ReferenceCategory in your report so I can't evaluate whether the data is correct or not.

By looking at the report and the code, I think that your issue is somewhere else than in the code that we are looking at.

Otherwise we would not see different values in ClosingConsignmentStockQty and ClosingStockQty on those report rows.

Please do following steps:

1) Debug your code to see what it does

2) Check the data in your table

3) Verify that the report shows the table data correctly

Was this reply helpful? Yes No
premK6969 198 on at

Like (0)

Report

Hi Nikolaos, i tried to debug and found that the debugger is ignoring the if condition if it doesnt matches as expected but in the report all the other ItemIds also gettig the value. Any corrections for the above scneario ?

Was this reply helpful? Yes No