Hello everyone,
I’m facing an issue with calculating outstanding quantities for purchase orders (POs) that have delivery schedules. Here’s the scenario:
- Problem Description:
- I have PO lines with varying items.
- Some POs use delivery schedules, while others don’t.
- I need to calculate the outstanding PO quantity for each line, considering partial receipts.
- Example:
Example 1
- PO Line 1: Item A, 830,000 ordered quantity.
- Initial receipt: 25,100.
- Subsequent receipts:
- No issue here, if the PO just have 1 item
Example 2
-
PO with multiple items in a single PO without delivery schedules.
-
I’ve tried different approach and this issue solved, but the issue persists when there are multiple items with delivery schedules.
Example 3: -
PO with multiple items with delivery schedules.
-
First receipt
-
Subsequent receipt
-
If i use the this code: (see full code below)
deliveredQty = 0;
while select sum(Qty) from VendPackingSlipTran2
join VendPackingSlipVersio2
where VendPackingSlipTran2.ItemId == tmpBPB.ItemId
&& VendPackingSlipVersio2.VendPackingSlipJour == VendPackingSlipTran2.VendPackingSlipJour
&& VendPackingSlipTran2.OrigPurchid == PurchID
&& VendPackingSlipVersio2.InternalPackingSlipId <= PurchInternalPackingSlipID
{
deliveredQty = VendPackingSlipTran2.Qty;
tmpBPB.QtyOS = purchLine.PurchQty - deliveredQty;
tmpBPB.update();
deliveredQty += VendPackingSlipTran2.Qty;
}
i will get this, the receipt has the last qty for every line
- Desired Outcome:
- Calculate the outstanding quantity for each line.
- Distribute outstanding quantities proportionally based on delivery schedules (if applicable).
- Full code:
class IMP_SCM_BuktiPenerimaanBarangDP extends SRSReportDataProviderBase
{
//declare temp table
IMP_SCM_BuktiPenerimaanBarang tmpBPB;
IMP_SCM_BuktiPenerimaanBarangContract contract;
//declare Parameter
PurchId PurchID;
PurchInternalPackingSlipId PurchInternalPackingSlipID;
PrintType PrintTypeItem;
//declare table
PurchParmTable PurchParmTabl;
VendPackingSlipJour VendPackingSlipJou;
VendPackingSlipTrans VendPackingSlipTran, VendPackingSlipTran2;
VendPackingSlipVersion VendPackingSlipVersio, VendPackingSlipVersio2;
PurchParmLine PurchParmLin;
DimensionAttributeValueSet dimAttribValSet;
PurchTable purchTable;
[SrsReportDataSetAttribute(tableStr(IMP_SCM_BuktiPenerimaanBarang))]
public IMP_SCM_BuktiPenerimaanBarang getDataTmp()
{
select tmpBPB;
return tmpBPB;
}
public void processReport()
{
CompanyInfo companyInfo;
Bitmap companyLogo;
PurchReqLine PurchReqLin;
PurchReqBusJustification PurchReqBusJustificatio;
PurchLine purchLine, purchline2;
Qty totalQty;
Qty shippedQty;
Qty purchDeliveredQty;
Qty deliveredQty;
Qty totalDeliveredQty;
companyInfo = CompanyInfo::find();
contract = this.parmDataContract() as IMP_SCM_BuktiPenerimaanBarangContract;
PurchID = contract.parmPurchID();
PrintTypeItem = contract.parmPrintTypeItem();
PurchInternalPackingSlipID = contract.parmPurchInternalPackingSlipID();
ttsbegin;
while select VendPackingSlipTran join VendPackingSlipVersio where VendPackingSlipTran.CostLedgerVoucher == VendPackingSlipVersio.CostLedgerVoucher && VendPackingSlipVersio.VendPackingSlipJour == VendPackingSlipTran.VendPackingSlipJour && VendPackingSlipTran.OrigPurchid == PurchID && VendPackingSlipVersio.InternalPackingSlipId == PurchInternalPackingSlipID
{
select PurchParmTabl where PurchParmTabl.PurchId == VendPackingSlipTran.OrigPurchid && PurchParmTabl.Ordering == DocumentStatus::PackingSlip && PurchParmTabl.ParmId == VendPackingSlipVersio.ParmId;
select PurchQty from purchline2 where purchline2.PurchId == PurchID && (purchline2.LineDeliveryType == LineDeliveryType::OrderLine || purchline2.LineDeliveryType == LineDeliveryType::OrderLineWithMultipleDeliveries);
totalQty = purchline2.PurchQty;
select sum(Qty) from VendPackingSlipTran2 join VendPackingSlipVersio2 where VendPackingSlipTran2.OrigPurchid == PurchID && VendPackingSlipVersio2.VendPackingSlipJour == VendPackingSlipTran2.VendPackingSlipJour && VendPackingSlipVersio2.InternalPackingSlipId < PurchInternalPackingSlipID;
totalDeliveredQty = VendPackingSlipTran2.Qty;
tmpBPB.clear();
if(subStr(VendPackingSlipTran.OrigPurchid,4,4) == "0101")
{
tmpBPB.IMP_SCM_IsoNum = "IP-2.FM.GD.006";
}
else
{
tmpBPB.IMP_SCM_IsoNum = "IP-1.GD06";
}
tmpBPB.ProductReceiptId = VendPackingSlipTran.PackingSlipId;
tmpBPB.PurchName = PurchParmTabl.PurchName;
tmpBPB.VendAccount = PurchParmTabl.InvoiceAccount;
tmpBPB.VendName = PurchParmTabl.PurchName;
tmpBPB.LogisticsAddressing = VendTable::find(PurchParmTabl.InvoiceAccount).postalAddress().Address;
tmpBPB.PurchIdBase = VendPackingSlipTran.OrigPurchid;
tmpBPB.DeliveryDate = VendPackingSlipTran.DeliveryDate;
tmpBPB.PurchInternalPackingSlipId = VendPackingSlipVersio.InternalPackingSlipId;
tmpBPB.ItemId = VendPackingSlipTran.ItemId;
if(PrintTypeItem == PrintType::BusinessJustification)
{
select PurchReqLin where PurchReqLin.PurchIdDataArea == VendPackingSlipTran.DataAreaId && PurchReqLin.PurchId == VendPackingSlipTran.OrigPurchid ;
select PurchReqBusJustificatio where PurchReqBusJustificatio.RefTableId == PurchReqLin.TableId
&& PurchReqBusJustificatio.RefRecId == PurchReqLin.RecId;
select purchLine where purchLine.InventTransId == VendPackingSlipTran.InventTransId;
tmpBPB.VendPackingName = purchLine.IMPBJDescription;
}
else
{
tmpBPB.VendPackingName = VendPackingSlipTran.itemName();
}
tmpBPB.PurchDeliveredQty = VendPackingSlipTran.Qty;
tmpBPB.PurchUnit = VendPackingSlipTran.PurchUnit;
tmpBPB.IMP_SCM_DeliveryBy = PurchParmTabl.IMP_SCM_DeliveryBy;
tmpBPB.IMP_SCM_TruckNo = PurchParmTabl.IMP_SCM_TruckNo;
tmpBPB.ErasoftNo = PurchParmTabl.IMP_SCM_ErasoftNo;
select PurchParmLin where PurchParmLin.ParmId == PurchParmTabl.ParmId && PurchParmLin.InventTransId == VendPackingSlipTran.InventTransId;
tmpBPB.IMP_SCM_Remarks = PurchParmLin.IMP_SCM_Remarks;
tmpBPB.companyName = companyInfo.name();
tmpBPB.companyLogo = FormLetter::companyLogo();
tmpBPB.Ordered = totalQty;
tmpBPB.Rec = VendPackingSlipTran.RecId;
tmpBPB.insert();
}
shippedQty = 0;
while select forupdate tmpBPB where tmpBPB.PurchIdBase == PurchID
{
select PurchQty, LineDeliveryType from purchLine where purchLine.ItemId == tmpBPB.ItemId && purchLine.PurchId == PurchID;
int itemCount = 0;
while select ItemId from vendPackingSlipTran group by ItemId join VendPackingSlipVersio
where vendPackingSlipTran.OrigPurchId == purchId && VendPackingSlipVersio.VendPackingSlipJour == VendPackingSlipTran.VendPackingSlipJour && VendPackingSlipVersio.InternalPackingSlipId == PurchInternalPackingSlipID
{
itemCount++;
}
if(itemCount == 1 && purchline.LineDeliveryType == LineDeliveryType::OrderLineWithMultipleDeliveries)
{
purchDeliveredQty = tmpBPB.PurchDeliveredQty;
tmpBPB.QtyOS = tmpBPB.Ordered - totalDeliveredQty - shippedQty - purchDeliveredQty;
tmpBPB.update();
shippedQty += tmpBPB.PurchDeliveredQty;
}
else
{
deliveredQty = 0;
while select sum(Qty) from VendPackingSlipTran2
join VendPackingSlipVersio2
where VendPackingSlipTran2.ItemId == tmpBPB.ItemId
&& VendPackingSlipVersio2.VendPackingSlipJour == VendPackingSlipTran2.VendPackingSlipJour
&& VendPackingSlipTran2.OrigPurchid == PurchID
&& VendPackingSlipVersio2.InternalPackingSlipId <= PurchInternalPackingSlipID
{
deliveredQty = VendPackingSlipTran2.Qty;
tmpBPB.QtyOS = purchLine.PurchQty - deliveredQty;
tmpBPB.update();
deliveredQty += VendPackingSlipTran2.Qty;
}
}
}
ttscommit;
}
}
how can I accurately calculate outstanding quantities for each PO line, especially when dealing with multiple items and delivery schedules?
Any insights or suggestions would be greatly appreciated!