Pages

Banner 468 x 60px

 

Wednesday, February 21, 2018

RELATION BETWEEN LEDGERDIMENSION AND DIMENSIONATTRIBUTEVALUECOMBINATION AX 2012 USING SQL :

0 comments

RELATION BETWEEN LEDGERDIMENSION AND DIMENSIONATTRIBUTEVALUECOMBINATION AX 2012 USING SQL :



SELECT  A.JOURNALID,C.[DESCRIPTION],
              A.VOUCHER,
              A.ITEMID,
              A.TRANSDATE,
              A.INVENTONHAND ,A.QTY,
              B.INVENTLOCATIONID, B.WMSLOCATIONID,
              (CASE  WHEN C.POSTED = 1 THEN  'YES'
                           WHEN C.POSTED = 0 THEN 'NO'
               END) AS POSTED,
              D.PRODUCTNAME,
              (CASE WHEN A.JOURNALTYPE = 0 THEN 'MOVEMENT'
                           WHEN A.JOURNALTYPE = 2 THEN 'TRANSFER'
                           WHEN A.JOURNALTYPE = 3 THEN 'BOM'
                           WHEN A.JOURNALTYPE = 4 THEN 'COUNTING'
               END) TYP,
               B.INVENTSITEID,
               E.DISPLAYVALUE AS DEPT,
               A.LEDGERDIMENSION,
               (CASE WHEN A.JOURNALTYPE = 3 THEN '1133-1001--'
                           ELSE
               F.DISPLAYVALUE END) AS ACCOUNT,
               (SELECT TOP 1 Y.COSTAMOUNTPHYSICAL / Y.QTY FROM [MDXABU].[DBO].[INVENTTRANSORIGIN] AS X WITH (NOLOCK)
                           INNER JOIN [MDXABU].[DBO].[INVENTTRANS] AS Y WITH (NOLOCK)
                                  ON Y.INVENTTRANSORIGIN = X.RECID
                WHERE X.INVENTTRANSID = A.INVENTTRANSID) AS COSTPRICE,
                (SELECT TOP 1 Y.COSTAMOUNTPHYSICAL  FROM [MDXABU].[DBO].[INVENTTRANSORIGIN] AS X WITH (NOLOCK)
                           INNER JOIN [MDXABU].[DBO].[INVENTTRANS] AS Y WITH (NOLOCK)
                                  ON Y.INVENTTRANSORIGIN = X.RECID
                WHERE X.INVENTTRANSID = A.INVENTTRANSID) AS COSTAMOUNT
INTO #TEMPJOURNAL
FROM [MDXABU].[DBO].INVENTJOURNALTRANS AS A WITH (NOLOCK)
       INNER JOIN [MDXABU].[DBO].INVENTDIM AS B WITH (NOLOCK)
              ON B.INVENTDIMID = A.INVENTDIMID
       INNER JOIN [MDXABU].[DBO].INVENTJOURNALTABLE AS C WITH (NOLOCK)
              ON C.JOURNALID = A.JOURNALID
       INNER JOIN [MDXABU].[DBO].ECORESPRODUCTTRANSLATIONS AS D WITH (NOLOCK)
              ON D.DISPLAYPRODUCTNUMBER = A.ITEMID
       LEFT JOIN [MDXABU].[DBO].[DIMENSIONATTRIBUTEVALUESETITEMVIEW] AS E WITH (NOLOCK)
              ON E.DIMENSIONATTRIBUTEVALUESET = A.DEFAULTDIMENSION
              AND E.DIMENSIONATTRIBUTE = 5637146078
       LEFT JOIN [MDXABU].[DBO].[DIMENSIONATTRIBUTEVALUECOMBINATION] AS F WITH (NOLOCK)
              ON F.RECID = A.LEDGERDIMENSION
       INNER JOIN [MDXABU].[DBO].[INVENTJOURNALTABLE] AS G WITH (NOLOCK)
              ON G.JOURNALID = A.JOURNALID
WHERE CAST(G.POSTEDDATETIME AS DATE) >= @FROMDATE
AND CAST(G.POSTEDDATETIME AS DATE) <= @TODATE
AND A.ITEMID LIKE 'P%'


SELECT B.NAME AS COANAME, * FROM #TEMPJOURNAL AS A WITH (NOLOCK)
       LEFT JOIN [MDXABU].[DBO].[MAINACCOUNT] AS B WITH (NOLOCK)
              ON B.MAINACCOUNTID = A.ACCOUNT

-----------------------------------------------------------------------------------------



SELECT A.INVOICEID,
              A.SALESID,
              A.INVOICEACCOUNT,
              A.INVOICINGNAME,
              A.INVOICEDATE,
              F.INVENTSITEID ,
              F.INVENTLOCATION AS INVENTLOCATIONID,
              E.ITEMGROUPID,
              A.INVOICEAMOUNT,
              B.ITEMID,
              C.PRODUCTNAME,
              B.QTY,
              B.SALESPRICE,
              B.LINEAMOUNTTAX,
              B.LINEAMOUNT,
              (SELECT (SUM(RA.COSTAMOUNTADJUSTMENT) + SUM(RA.COSTAMOUNTPOSTED)) FROM [MDXABU].[DBO].INVENTTRANS AS RA WITH (NOLOCK)
               INNER JOIN [MDXABU].[DBO].INVENTTRANSORIGIN  AS RB WITH (NOLOCK)
               ON RA.INVENTTRANSORIGIN = RB.RECID
               WHERE RB.INVENTTRANSID = B.INVENTTRANSID
              ) AS COGS,
              G.ABUITEMGROUPID AS SUBCATEGORY
 FROM [MDXABU].[DBO].CUSTINVOICEJOUR AS A WITH (NOLOCK)
       INNER JOIN [MDXABU].[DBO].CUSTINVOICETRANS AS B WITH (NOLOCK)
              ON B.INVOICEID = A.INVOICEID
       LEFT JOIN [MDXABU].[DBO].ECORESPRODUCTTRANSLATIONS AS C WITH (NOLOCK)
              ON C.DISPLAYPRODUCTNUMBER = B.ITEMID
       LEFT JOIN [MDXABU].[DBO].INVENTDIM AS D WITH (NOLOCK)
              ON  D.INVENTDIMID = B.INVENTDIMID
              AND D.DATAAREAID = A.DATAAREAID
       LEFT JOIN [MDXABU].[DBO].INVENTITEMGROUPITEM AS E WITH (NOLOCK)
              ON E.ITEMID = B.ITEMID
       INNER JOIN [MDXABU].[DBO].CUSTTABLE AS F WITH (NOLOCK)
              ON F.ACCOUNTNUM = A.INVOICEACCOUNT
              AND F.DATAAREAID = A.DATAAREAID
       INNER JOIN [MDXABU].[DBO].[ABUITEMGROUP] AS G WITH (NOLOCK)
              ON  G.ITEMID = B.ITEMID
              AND G.ABUITEMGROUPID in (@CATEGORY)

       LEFT JOIN [MDXABU].[DBO].[DIMENSIONATTRIBUTEVALUESETITEMVIEW] AS H WITH (NOLOCK)
       ON H.DIMENSIONATTRIBUTEVALUESET = B.DEFAULTDIMENSION
       AND H.DIMENSIONATTRIBUTE = 5637146077
       AND H.DISPLAYVALUE in (@FINDIMBRANCHES)
       LEFT JOIN [MDXABU].[DBO].[DIMENSIONATTRIBUTEVALUECOMBINATION] AS I WITH (NOLOCK)
       ON I.RECID = B.LEDGERDIMENSION

       WHERE A.DATAAREAID = 'ABU'
       AND A.INVOICEDATE >= @FROMDATE
       AND A.INVOICEDATE <= @TODATE

Read more...

FormCtrl using x++ :

0 comments

FormCtrl using x++ :



void clicked()
{
    ABUTallyHeader  _abutallyheader;
    ABUTallyLine    _abutallyline;

    super();

    while select _abutallyheader where
    _abutallyheader.RefRecId == WMSOrderTrans.RecId
    {

        select count(RecId), sum(Qty) from _abutallyline where _abutallyline.RefRecIdRouteLine == _abutallyheader.recid;

        WMSOrderTrans.qty       = _abutallyline.Qty;
        WMSOrderTrans.QtyBOX    = _abutallyline.RecId;

        if (formCtrl)
        {
            formCtrl.fieldModified(fieldNum(WMSOrderTrans, Qty));
            formCtrl.fieldModified(fieldNum(WMSOrderTrans, QtyBOX));
            wmsOrderTrans_ds.refresh();
        }

        info(strFmt("Update qty : %1 And Box : %2",_abutallyline.Qty,_abutallyline.RecId));
    }

}


Read more...

Creating Financial Dimension in General Journals in AX 2012 in X++

0 comments

Creating Financial Dimension in General Journals in AX 2012 in X++ :

AxLedgerJournalTable header = new AxLedgerJournalTable();

AxLedgerJournalTrans trans = new AxLedgerJournalTrans();
container ledgerDimensions, offsetDimensions;

header.parmJournalName("GenJrn");
header.save();

trans.parmAccountType(LedgerJournalACType::Ledger);
trans.parmJournalNum(header.ledgerJournalTable().JournalNum);
ledgerDimensions = ["131100-USA","131100", 1, "COUNTRY","USA"];
trans.parmLedgerDimension(AxdDimensionUtil::getLedgerAccountId(ledgerDimensions));
trans.parmAmountCurDebit(230);
offsetDimensions = ["0103-USA","0103", 1, "COUNTRY", "USA"];
trans.parmOffsetLedgerDimension(AxdDimensionUtil::getLedgerAccountId(offsetDimensions));
trans.save();

Read more...

Thursday, February 8, 2018

Update Default Financial Dimension in AX 2012 using X++

0 comments

Update Default Financial Dimension in AX 2012 using X++ 


static void ABUDefaultFinancialDim_AP(Args _args)
{
    DimensionAttributeValue             dimAttrBUValue,dimAtrrCCValue,dimAtrrDepValue,dimAttrIGValue,dimAtrrProjValue;
    DimensionAttribute                  dimAttrBU,dimAtrrCC,dimAtrrDep,dimAttrIG,dimAtrrProj;
    DimensionAttributeValueSetStorage   davss;
    RecId                               defaultDimension;
    AssetBook                           Assetbook;
    AssetId                             AssetId;
    str                                 A_SITES, B_BRANCHES, C_DEPARTMENTS;

    SysExcelApplication         application;
    SysExcelWorkbooks           workbooks;
    SysExcelWorkbook            workbook;
    SysExcelWorksheets          worksheets;
    SysExcelWorksheet           worksheet;
    SysExcelCells               cells;
    COMVariantType              type1;
    Name                        name;
    HcmWorker                   HCMWorker;
    HcmPersonnelNumberId        HcmPersonnelNumberId;
    FileName                    filename;
    CustTable                   CustTable;
    CustAccount                 CustAccount;
    DirPartyName                DirPartyName;
    CustPriceGroup              PriceGroup;
    DirPartyTable               dirPartyTable1;
    LogisticsPostalAddress      postalAddress;
    LogisticsLocation           location;
    LogisticsLocationRole       locationRole;
    DirPartyLocationRole        partyLocationRole;
    DirPartyLocation            partyLocation;


    int                         row;
    str                         currency;
    str                         site,warehouse,payterid,paymode;
    str  100                    Address,Description;
    Dialog                      _dialog;
    DialogField                 _file;

     str COMVariant2Str(COMVariant _cv, int _decimals = 0,int _characters = 0,int _separator1 = 0,int _separator2 = 0)
       {
            switch(_cv.variantType())
            {
                case (COMVariantType::VT_BSTR):
                    return _cv.bStr();
                case (COMVariantType::VT_R4):
                    return num2str(_cv.float(),_characters,_decimals, _separator1,_separator2);
                case (COMVariantType::VT_R8):
                    return num2str(_cv.double(),_characters,_decimals,_separator1,_separator2);
                case (COMVariantType::VT_DECIMAL):
                    return num2str(_cv.decimal(),_characters,_decimals, _separator1, _separator2);
                case (COMVariantType::VT_DATE):
                    return date2str(_cv.date(),123,2,1,2, 1,4);
                case (COMVariantType::VT_EMPTY):
                    return "";
                default:
                    throw error(strfmt("@SYS26908",_cv.variantType()));
            }
            return "";
        }
    ;

    _dialog = new Dialog("Please select the file to load");
    _dialog.addText("Select file:");
    _file = _dialog.addField(ExtendedTypeStr("FilenameOpen"));
    _dialog.run();

    application = SysExcelApplication::construct();
    workbooks = application.workbooks();
    filename = _file.value();
    try
    {
        workbooks.open(filename);
    }
    catch (Exception::Error)
    {
        throw error("File cannot be opened.");
    }
    workbook = workbooks.item(1);
    worksheets = workbook.worksheets();
    worksheet = worksheets.itemFromNum(1);
    cells = worksheet.cells();
    row = 1;
    do
    {
        row++;

        Assetid                      = cells.item(row, 1).value().bStr();
        A_SITES                      = cells.item(row, 2).value().bStr();
        B_BRANCHES                   = cells.item(row, 3).value().bStr();
        C_DEPARTMENTS                = cells.item(row, 4).value().bStr();
       
        davss = DimensionAttributeValueSetStorage::find(AssetBook::find(Assetid, 'Commercial').DefaultDimension);

        dimAttrBU = DimensionAttribute::findByName('A_SITES');
        dimAtrrCC = DimensionAttribute::findByName('B_BRANCHES');
        dimAtrrDep = DimensionAttribute::findByName('C_DEPARTMENTS');

        dimAttrBUValue = DimensionAttributeValue::findByDimensionAttributeAndValue(dimAttrBU, A_SITES, false, true);
        dimAtrrCCValue = DimensionAttributeValue::findByDimensionAttributeAndValue(dimAtrrCC, B_BRANCHES, false, true);
        dimAtrrDepValue = DimensionAttributeValue::findByDimensionAttributeAndValue(dimAtrrDep, C_DEPARTMENTS, false, true);   

        if(dimAttrBUValue || dimAtrrCCValue ||dimAtrrDepValue)
        {
            ttsBegin;
            davss.addItem(dimAttrBUValue);
            davss.addItem(dimAtrrCCValue);
            davss.addItem(dimAtrrDepValue); 
       
            select forUpdate Assetbook where Assetbook.AssetId == Assetid;
            Assetbook.DefaultDimension = davss.save();
            Assetbook.update();
            ttsCommit;
           
            info(strFmt('Asset id %1 - DefaultDimension %2', AssetId, Assetbook.DefaultDimension));
        }

        type1 = cells.item(row+1, 1).value().variantType();

    }
    while (type1 != COMVariantType::VT_EMPTY);

    application.quit();

}

This is template for update DefaultDimension fixed Asset (AssetTable) :




Read more...

A financial dimension value is based on the record and has been used on a transaction. You cannot delete the record

  SOLUTION delete in table DimensionAttributeLevelValue base on Bank Account  :  and Delete the Bank Account FINISH