ANSARIBalanceSheetReconDP

X++ Programming Language SQL Server Reporting Services (SSRS) (Article) SQL Server Reporting Services (SSRS) (Program)

8

---

Program:

<pre class = "prettyprint"><xmp>
/// <summary>
/// The <c>ANSARIBalanceSheetReconDP</c> class is the report data provider class for the <c>ANSARIBalanceSheetReconReport</c> report.
/// </summary>
/// <remarks>
/// Developed for ANSARI_RDD_LEW_002_BalanceSheetRecon by v-kikulk on 09-02-2022.
/// </remarks>
[
    SRSReportQueryAttribute(queryStr(ANSARIInventOnhand)),
    SRSReportParameterAttribute(classStr(ANSARIBalanceSheetReconContract))
]
class ANSARIBalanceSheetReconDP extends SrsReportDataProviderPreProcess
{
    ANSARIBalanceSheetReconTmp balanceSheetReconTmp;
    ItemId                  prevsItemId;
    InventDimParm           inventDimParm;
    RecordInsertList        recordInsertList;
    ListIterator            siteListIterator,locationListIterator;
    ANSARIBalanceSheetReconContract    contract;


    /// <summary>
    ///    Fetches data from the <c>InventValueReportTmpLedgerLine</c> table.
    /// </summary>
    /// <returns>
    ///    The required report data.
    /// </returns>
    [
    SRSReportDataSetAttribute(tableStr(InventValueReportTmpLedgerLine))
    ]
    public InventValueReportTmpLedgerLine getInventValueReportTmpLedgerLine()
    {
        InventValueReportTmpLedgerLine inventValueReportTmpLedgerLine;

        inventValueReportTmpLedgerLine.setConnection(this.parmUserConnection());

        select inventValueReportTmpLedgerLine;
        return inventValueReportTmpLedgerLine;
    }

    /// <summary>
    /// Fetches data from the <c>ANSARIBalanceSheetReconTmp</c> temporary table.
    /// </summary>
    /// <returns>
    /// The data from <c>ANSARIBalanceSheetReconTmp</c> table.
    /// </returns>
    [
        SRSReportDataSetAttribute(tableStr(ANSARIBalanceSheetReconTmp))
    ]
    public ANSARIBalanceSheetReconTmp getBalanceSheetReconTmp()
    {
        balanceSheetReconTmp.setConnection(this.parmUserConnection());

        select  balanceSheetReconTmp;
        return  balanceSheetReconTmp;
    }

    /// <summary>
    /// Processes the report business logic.
    /// </summary>
    /// <remarks>
    /// Provides the ability to write the report business logic. This method will be called by  at runtime.
    /// The method should compute data and populate the data tables that will be returned to .
    /// </remarks>
    public void processReport()
    {
        InventTable                     inventTable;
        InventDim                       inventDim;
        InventDimParm                   inventDimParmCriteria;
        List                            siteId;
        List                            locationId;
        DatePhysical                    datePhysical;
        QueryBuildDataSource            qdbs, qdbsInventTrans;
        Inventtrans                     inventTrans;

        contract = this.parmDataContract() as ANSARIBalanceSheetReconContract;
        InventValueReportInit inventValueReportInit = InventValueReportInit::construct(); //N added for the Task 296313: Balance Sheet Recon
        InventValueReportContract inventValueReportContract = new InventValueReportContract();
        inventValueReportContract.parmToDate(contract.parmDatePhysical());
        inventValueReportContract.parmFromDate(datenull());//Task238028
        //Task238028 --> 
        inventValueReportContract.parmIsANSARIStockReconReport(NoYes::Yes);
        //Task238028 <-- 
        this.parmCreatedTransactionId(appl.curTransactionId(true));

        inventValueReportInit.parmInventValueReportContract(inventValueReportContract);
        inventValueReportInit.parmUserConnection(this.parmUserConnection());
        inventValueReportInit.parmCreatedTransactionId(this.parmCreatedTransactionId());
        inventValueReportInit.run();

        this.processInventValueReportTmpLedgerLine();
        this.processInventValueReportTmpLine();//N added for the Task 296313: Balance Sheet Recon

        siteId = new List(Types::String);
        locationId = new List(Types::String);

        inventDimParm.ItemIdFlag            = NoYes::Yes;
        inventDimParm.InventSiteIdFlag      = NoYes::Yes;
        inventDimParm.InventLocationIdFlag  = NoYes::Yes;
        inventDimParm.WMSLocationIdFlag     = NoYes::Yes;
        inventDimParmCriteria.ClosedFlag    = NoYes::No;

        
        siteId          = contract.parmInventSiteId();
        locationId      = contract.parmInventLocationId();
        datePhysical    = contract.parmDatePhysical();

        Query query = this.parmQuery();

        qdbs = query.dataSourceTable(tableNum(InventDim));
        qdbsInventTrans = query.dataSourceTable(tableNum(InventTrans));

        if (siteId)
        {
            qdbs.addRange(fieldNum(InventDim, InventSiteId)).value(strList2str(siteId));
        }

        if (locationId)
        {
            qdbs.addRange(fieldNum(InventDim, InventLocationId)).value(strList2str(locationId));
        }

        if (datePhysical)
        {
            qdbsInventTrans.addRange(fieldNum(InventTrans, DatePhysical)).value(SysQuery::range(datePhysical, true));
        }

        QueryRun queryRun = new QueryRun(query);
        balanceSheetReconTmp.setConnection(this.parmUserConnection());
        
        recordInsertList    = new RecordInsertList(tableNum(ANSARIBalanceSheetReconTmp), true, true, true, false, true, balanceSheetReconTmp);
        
        while (queryRun.next())
        {
            inventTrans = queryRun.get(tableNum(Inventtrans));
            inventTable = queryRun.get(tableNum(InventTable));
            if (InventTable::find(inventTable.ItemId).inventItemType().mustInventBeControlled())
            {
                inventDim = queryRun.get(tableNum(InventDim));
            }
            if (!InventTable::find(inventTable.ItemId).inventItemType().isServiceItem())
            {
                this.insertTmpTable(inventTrans, inventDim, inventTable);
                inventDim.clear();
            }
        }
        recordInsertList.insertDatabase();
    }

    /// <summary>
    /// Method for sum Qty field based on the location type
    /// </summary>
    /// <param name = "itemId">itemId</param>
    /// <param name = "inventDim">inventDim</param>
    /// <returns>Qty</returns>
    public real qtyInTransit(Itemid itemId, InventDim inventDim)
    {
        InventTrans    inventTrans;
        InventDim      inventDimension;
        InventLocation inventLocation;
        
        select sum(Qty) from inventTrans
            exists join inventDimension
            where inventDimension.inventsiteid == inventDim.inventsiteid
            && inventDimension.InventLocationId == inventDim.InventLocationId
            && inventTrans.inventDimId == inventDimension.inventDimId
            && inventTrans.ItemId == itemId
            exists join inventLocation
            where inventLocation.InventLocationType == InventLocationType::Transit
            && inventLocation.InventLocationId == inventDimension.InventLocationId;
        
        return inventTrans.Qty;
    }

    /// <summary>
    /// Item physical value
    /// </summary>
    /// <param name = "inventTrans">inventTrans</param>
    /// <param name = "inventDim">inventTrans</param>
    /// <returns>inventTrans</returns>
    public InventSum itemPhysicalValue(InventTrans inventTrans, InventDim inventDim)
    {
        InventSum      inventSum;
        InventDim      inventDimension;
        
        select sum(PhysicalValue) from inventSum
            exists join inventDimension
            where inventDimension.inventsiteid == inventDim.inventsiteid
            && inventDimension.InventLocationId == inventDim.InventLocationId
            && inventTrans.inventDimId == inventDimension.inventDimId
            && inventSum.ItemId == inventTrans.ItemId
            && InventSum.Closed == NoYes::No ;
        
        return InventSum;
    }

    /// <summary>
    /// Inserts data into a temporary table.
    /// </summary>
    /// <param name = "inventTrans">
    /// A table buffer of an <c>InventTrans</c> table.
    /// </param>
    /// <param name = "inventDim">
    /// A table buffer of the <c>Inventdim</c> table.
    /// </param>
    /// <param name = "inventTable">
    /// A table buffer of the <c>InventTable</c> table.
    /// </param>
    public void insertTmpTable(InventTrans inventTrans, InventDim inventDim, InventTable inventTable )
    {
        InventQtyAvailPhysical      availPhysical, availPhysicalForTruckStock;
        CostAmountPhysical          physicalCostAmt, physicalCostAmtForTruckStock;
        InventSum                   inventSum;
        InventCountJour             inventCountJour;
        InventDim                   inventDimension;
        InventValueReportTmpLine    inventValueReportTmpLine;

        inventSum = this.itemPhysicalValue(inventTrans, inventDim);
                
        select firstonly CountedQty from inventCountJour
            order by RecId desc
            exists join inventDimension
            where inventDimension.InventSiteId == inventDim.InventSiteId
            && inventDimension.InventLocationId == inventDim.InventLocationId
            && inventCountJour.ItemId == inventTable.ItemId;

        balanceSheetReconTmp.ItemId                                 = inventTable.ItemId;
        balanceSheetReconTmp.ItemName                               = inventTable.itemName();
        balanceSheetReconTmp.QuantityOnhand                         = inventTrans.Qty;
        balanceSheetReconTmp.ActualCount                            = inventCountJour.CountedQty;
        balanceSheetReconTmp.VarianceOnhand                         = balanceSheetReconTmp.QuantityOnhand - balanceSheetReconTmp.ActualCount;
        [availPhysical, physicalCostAmt]                            = this.getQtyInTransit(inventSum, inventDim.InventLocat

Output:

---

---