Hi all,
I'm developing a report on Purchase Order and I want to show fields like PO Status and PO Type and Item Type. based on the PurchId field.
Here is my code . I'm using multi select filter on PO Status and PurchId and Item ID
class CFZPOHistoryStatusReportDP extends SrsReportDataProviderPreProcessTempDB
{
CFZPOHistoryStatusReportHeaderTmp cfzPOHistoryStatusReportHeaderTmp;
CFZPOHistoryStatusReportLinesTmp cfzPOHistoryStatusReportLinesTmp;
CFZPOHistoryStatusReportContract contract;
TransDate fFromdate, tTodate;
CompanyInfo companyInfo;
List listSiteId, listPurchName, listLocationId, listPurchId, listPOStatus, listDepartment, listBrand, listCategory, listSubCategory;
ListEnumerator enumPurchId, enumPOStatus;
//RetailStoreId retailStoreId;
//AX Tables
//CustTrans custTrans;
VendTrans vendTrans;
PurchTable purchTable;
PurchLine purchLine;
VendInvoiceTrans vendInvoiceTrans;
InventDim inventDim;
InvoiceId invoiceId;
InventTable inventTable;
VendInvoiceJour vendInvoiceJour;
InventLocation inventLocation;
VendPackingSlipTrans vendPackingSlipTrans;
VendTable vendTable;
PurchId purchIDVar;
PurchStatus purchStatusVar;
//VendPackingSlipJour vendPackingSlipJour;
//TaxTrans taxTrans;
//TaxTable taxTable;
// CFZPOHistoryStatusReportHeaderTmp get and set
[SRSReportDataSetAttribute(tablestr('CFZPOHistoryStatusReportHeaderTmp'))]
public CFZPOHistoryStatusReportHeaderTmp getCFZPOHistoryStatusReportHeaderTmp()
{
//select data from table buffer
select * from cfzPOHistoryStatusReportHeaderTmp;
//return the buffer
return cfzPOHistoryStatusReportHeaderTmp;
}
// CFZPOHistoryStatusReportLinesTmp get and set
[SRSReportDataSetAttribute(tablestr('CFZPOHistoryStatusReportLinesTmp'))]
public CFZPOHistoryStatusReportLinesTmp getCFZPOHistoryStatusReportLinesTmp()
{
//select data from table buffer
select * from cfzPOHistoryStatusReportLinesTmp;
//return the buffer
return cfzPOHistoryStatusReportLinesTmp;
}
public void processreport()
{
contract = this.parmDataContract() as CFZPOHistoryStatusReportContract;
fFromdate = contract.parmFromDate();
tTodate = contract.parmToDate();
listSiteId = contract.parmSiteId();
listLocationId = contract.parmInventLocationId();
listPurchName = contract.parmPurchName();
listPurchId = contract.parmPONumber();
listPOStatus = contract.parmPOStatus();
listDepartment = contract.parmDepartment();
listBrand = contract.parmBrand();
listCategory = contract.parmCategory();
listSubCategory = contract.parmSubCategory();
this.HeaderTmp();
this.LinesTmp();
}
private void HeaderTmp()
{
cfzPOHistoryStatusReportHeaderTmp.clear();
companyInfo = CompanyInfo::find();
cfzPOHistoryStatusReportHeaderTmp.ReportName = "Purchase Order History Status Report";
cfzPOHistoryStatusReportHeaderTmp.CompanyName = companyinfo.Name;
cfzPOHistoryStatusReportHeaderTmp.insert();
}
private void LinesTmp()
{
enumPurchID = listPurchId.getEnumerator();
while(enumPurchId.moveNext())
{
purchIDVar = enumPurchId.current();
// while select * from purchtable where purchtable.PurchId == purchIDVar
//while select purchTable
// where purchTable.PurchId == purchIDVar
// join purchLine
// where purchLine.PurchId == purchTable.PurchId
// // join vendTable
// // where vendTable.AccountNum == purchTable.OrderAccount
// join inventDim
// where inventDim.inventDimId == purchLine.InventDimId
// join inventTable
// where inventTable.ItemId == purchLine.ItemId
// join vendPackingSlipTrans
// where vendPackingSlipTrans.ItemId == inventTable.ItemId
while select purchLine
where purchLine.PurchId == purchIDVar
join purchTable
where purchTable.PurchId == purchLine.PurchId
// join vendTable
// where vendTable.AccountNum == purchTable.OrderAccount
join inventDim
where inventDim.inventDimId == purchLine.InventDimId
join inventTable
where inventTable.ItemId == purchLine.ItemId
outer join vendPackingSlipTrans
where vendPackingSlipTrans.ItemId == inventTable.ItemId
{
cfzPOHistoryStatusReportLinesTmp.clear();
cfzPOHistoryStatusReportLinesTmp.FromDate = fFromdate;
cfzPOHistoryStatusReportLinesTmp.ToDate = tTodate;
cfzPOHistoryStatusReportLinesTmp.PurchId = purchTable.PurchId;
cfzPOHistoryStatusReportLinesTmp.PurchName = purchTable.PurchName;
cfzPOHistoryStatusReportLinesTmp.DlvDate = purchLine.deliveryDate;
cfzPOHistoryStatusReportLinesTmp.CreationDate = DateTimeUtil::date(purchTable.CreatedDateTime);
cfzPOHistoryStatusReportLinesTmp.ItemId = purchLine.ItemId;
cfzPOHistoryStatusReportLinesTmp.ItemName = InventTable::find(purchLine.ItemId).itemName();
cfzPOHistoryStatusReportLinesTmp.InventLocationName = InventLocation::find(purchTable.InventLocationId).Name;
cfzPOHistoryStatusReportLinesTmp.PurchPrice = PurchLine::find(purchLine.PurchId).PurchPrice;
cfzPOHistoryStatusReportLinesTmp.Color = inventDim.InventColorId;
cfzPOHistoryStatusReportLinesTmp.PurchUnit = purchLine.PurchUnit;
cfzPOHistoryStatusReportLinesTmp.QtyOrdered = vendPackingSlipTrans.Ordered;
cfzPOHistoryStatusReportLinesTmp.Qty = vendPackingSlipTrans.Qty;
cfzPOHistoryStatusReportLinesTmp.QtyReceived = vendPackingSlipTrans.ReceivedQty_IN;
cfzPOHistoryStatusReportLinesTmp.QtyPending = vendPackingSlipTrans.Ordered-vendPackingSlipTrans.Qty;
if(purchIDVar)
cfzPOHistoryStatusReportLinesTmp.PurchaseType = purchTable.PurchaseType;
cfzPOHistoryStatusReportLinesTmp.insert();
}
}
}
}
Please guide me
Thanks
