Purchase order detail report

Contract Class:

class MZNNM_InventoryStockContract
{
    ItemId      itemCode;
    TransDate   fromDate;
    TransDate   toDate;
    VendAccount vendAccount;

    [
       DataMemberAttribute("VendorNumber"),
       sysoperationlabelAttribute(literalstr("Vendor Code"))
    ]
    public VendAccount parmVendAccount( VendAccount _vendAccount = vendAccount)
     {
        vendAccount= _vendAccount;
        return vendAccount;
     }

    [
       DataMemberAttribute("ItemCode"),
       sysoperationlabelAttribute(literalstr("Item Code"))
    ]
    public ItemId parmItemCode( ItemId _itemCode= itemCode)
    {
       itemCode= _itemCode;
       return itemCode;
    }

    [
       DataMemberAttribute("Fromdate"),
       sysoperationlabelAttribute(literalstr("From date"))
    ]
    public TransDate parmFromdate( TransDate _fromDate = fromDate)
    {
       fromDate= _fromDate;
       return fromDate;
    }

    [
       DataMemberAttribute("Todate"),
       sysoperationlabelAttribute(literalstr("To date"))
    ]
    public TransDate parmTodate( TransDate _toDate = toDate)
    {
       toDate= _toDate;
       return toDate;
    }
}

DP Class:

[
   SRSReportParameterAttribute (classstr(MZNNM_InventoryStockContract)),
   SRSReportQueryAttribute (queryStr(MZNNM_PurchInventoryStockQuery))
]

class MZNNM_InventoryStockDP extends SRSReportDataProviderBase
{

    MZNNM_InventoryStockTmp      objInvStockTmp;
    PurchLine                    purchLine;
    PurchTable                   purchTable;
    ItemId                       itemId;
    VENDPACKINGSLIPTRANS         VENDPACKINGSLIPTRANS;
    VENDPACKINGSLIPJOUR          VENDPACKINGSLIPJOUR;
    VendTable                    vendTable;
    DirPartyTable                dirPartyTable;
    VendInvoiceTrans             vendInvoiceTrans;
    VendInvoiceJour              vendInvoiceJour;
    TransDate                    fromDate;
    TransDate                    toDate;
    VendAccount                  vendAccount;

    [SRSReportDataSetAttribute (tablestr(MZNNM_InventoryStockTmp))]
    public MZNNM_InventoryStockTmp getMZNNM_InventoryStockTmp()
    {
       select * from objInvStockTmp;
       return objInvStockTmp;
    }

    public void processReport()
    {
        MZNNM_InventoryStockContract contract  = new MZNNM_InventoryStockContract();
        contract     = this.parmDataContract() as MZNNM_InventoryStockContract;
        itemId       = contract.parmItemCode();
        fromDate     = contract.parmFromdate();
        toDate       = contract.parmTodate();
        vendAccount  = contract.parmVendAccount();
        CompanyInfo   companyInfo = CompanyInfo::find();
        Query query  = new Query(this.parmQuery());

        if(fromDate && toDate){
            query.dataSourceTable(tableNum(PurchTable)).addRange(fieldnum(PurchTable, CreatedDateTime)).value(queryRange(DateTimeUtil::newDateTime(fromDate,0),DateTimeUtil::newDateTime(toDate,0)));
        }
        if(vendAccount){
           query.dataSourceTable(tableNum(PurchLine)).addRange(fieldnum(PurchLine, VendAccount)).value(vendAccount);
        }
        if(itemId){
           query.dataSourceTable(tableNum(PurchLine)).addRange(fieldnum(PurchLine, ItemId)).value(itemId);
        }
        QueryRun run = new QueryRun(query);
        while(run.next())
        {
           purchLine = run.get(tableNum(PurchLine));
           purchTable = run.get(tableNum(PurchTable));
       
           while select VENDPACKINGSLIPTRANS
                 where VENDPACKINGSLIPTRANS.InventTransId == purchLine.InventTransId
           {
              VENDPACKINGSLIPJOUR.clear();
              select VENDPACKINGSLIPJOUR
                     where VENDPACKINGSLIPJOUR.PackingSlipId == VENDPACKINGSLIPTRANS.PackingSlipId;
              vendTable.clear();
              select vendTable
                     where vendTable.AccountNum == purchTable.OrderAccount;
              dirPartyTable.clear();
              select dirPartyTable
                     where dirPartyTable.RecId == vendTable.Party;

              vendInvoiceTrans.clear();
              vendInvoiceJour.clear();
              select InvoiceId,InvoiceDate from vendInvoiceJour
                     join vendInvoiceTrans
                          where vendInvoiceTrans.InventTransId == VENDPACKINGSLIPTRANS.InventTransId
                          && vendInvoiceJour.InvoiceId == vendInvoiceTrans.InvoiceId;
              objInvStockTmp.clear();
              //logo
              objInvStockTmp.Logo = CompanyImage::findByRecord(CompanyInfo).Image;
              //Packing info
              objInvStockTmp.GRNQty = VENDPACKINGSLIPTRANS.Qty;
              objInvStockTmp.GRNDate = VENDPACKINGSLIPJOUR.DeliveryDate;
              objInvStockTmp.GRNNo = VENDPACKINGSLIPJOUR.PackingSlipId;
              //Purch line info
              objInvStockTmp.Description = this.productWithDimensions(purchLine.RecId);
              objInvStockTmp.SupplierName = vendTable.AccountNum+':'+dirPartyTable.Name;
              objInvStockTmp.PurchLineQty = purchLine.PurchQty;
              objInvStockTmp.PuchLineCostPerUnit = purchLine.PurchPrice;
              objInvStockTmp.PurchLineTotal = purchLine.LineAmount;
              objInvStockTmp.PONo = purchLine.PurchId;
              objInvStockTmp.POStatus = enum2Str(purchLine.PurchStatus);
              objInvStockTmp.PODateUTC = purchTable.CreatedDateTime;
              //invoice details
              objInvStockTmp.InvoiceDate = vendInvoiceJour.InvoiceDate;
              //payment details
              objInvStockTmp.PaymentDates = this.paymentDate(vendInvoiceJour.InvoiceId);
              objInvStockTmp.insert();
           }
        }
    }

   private str paymentDate( VendInvoiceId invoiceId= '')
   {
      VendTrans            vendTrans , vendTrans2;
      InventDim            inventDim;
      LedgerJournalTrans   ledgerJournalTrans;
      VendInvoiceJour      VendInvoiceJour2;
      VendSettlement       VendSettlement;
      str                  TransDates;

      TransDates='';

      vendTrans.clear();
      select RecId from vendTrans
             where vendTrans.Invoice == InvoiceId;
      if(vendTrans){
           while select * from vendSettlement
                 where vendSettlement.TransRecId == vendTrans.RecId
           {
               ledgerJournalTrans.clear();
               select TRANSDATE from ledgerJournalTrans 
                      where ledgerJournalTrans.VendTransId == vendSettlement.OffsetRecid;
                if(ledgerJournalTrans){
                    TransDates += (TransDates=='') ? date2Str(ledgerJournalTrans.TransDate,123,DateDay::Digits2,DateSeparator::Space,DateMonth::Short,DateSeparator::Space,DateYear::Digits4)
                    : '\n'+ date2Str(ledgerJournalTrans.TransDate,123,DateDay::Digits2,DateSeparator::Space,DateMonth::Short,DateSeparator::Space,DateYear::Digits4);
                }
           }
     }
     return TransDates;
  }

  private str productWithDimensions( RecId purchLineId)
  {
     InventDim                inventDim;
     InventTable              inventTable;
     PurchLine                purchline2;
     EcoResProductTranslation ecoResProductTranslation;
     str                      dimensionsList;
     str                      productName;

     inventDim.clear();
     productName    = '';
     dimensionsList = '';
     purchline2.clear();
     select * from purchline2
            where purchline2.RecId == purchLineId;
     inventTable.clear();
     select * from inventTable
            where inventTable.ItemId == purchline2.ItemId;

     productName     = purchline2.ItemId;
     productName    +=' : '+ EcoResProductTranslation::findByProductLanguage(inventTable.Product,CompanyInfo::languageId()).Name;
     //productName  +=' : '+ inventTable::find(purchline2.ItemId).productName(CompanyInfo::languageId(),purchline2.InventDimId);
     select * from inventDim
            where inventDim.InventDimId == purchline2.InventDimId;
     if(inventDim){
         dimensionsList = (inventDim.CONFIGID == '') ? '' : inventDim.CONFIGID;
         dimensionsList += (inventDim.INVENTSIZEID == '') ? '' : ' : '+inventDim.INVENTSIZEID;
         dimensionsList += (inventDim.INVENTCOLORID == '') ? '' : ' : '+inventDim.INVENTCOLORID;
         dimensionsList += (inventDim.INVENTSTYLEID == '') ? '' : ' : '+inventDim.INVENTSTYLEID;
     }

  return productName+' : '+dimensionsList;
 }
}

Adding the Simple Query:

Adding Sr# in the report design:

 

Leave a Reply

Your email address will not be published. Required fields are marked *