Wednesday, October 18, 2017

Get unused Project categories In AX 2012

Below is the code for getting the unused project dimensions in AX.

Note : Please create a table(Test) with two string fields.


static void ProjCategory(Args _args)
{
    ProjUnpostedTransView   projUnpostedTransView;
    ProjPostTransView       projPostTransView;
    CategoryTable           categoryTable;
    Test           test;
    int iCounter;
    delete_from test;
    while select * from categoryTable
    {
        test.clear();
        select firstOnly projPostTransView where projPostTransView.CategoryId ==  categoryTable.CategoryId;
        if(projPostTransView)
        {
            continue;
        }
      //  else if(!projPostTransView)
      //  {
        select firstonly projUnpostedTransView where projUnpostedTransView.CategoryId ==  categoryTable.CategoryId;
        if(projUnpostedTransView)
        {
            continue;
        }
        else
        {
            test.AccountNum = categoryTable.CategoryId;
            test.Name = categoryTable.CategoryName;
            test.insert();
            iCounter++;
        }

SQL Query:

select * from CATEGORYTABLE where not exists
( select * from projPostTransView where CATEGORYTABLE.CategoryId =  projPostTransView.CategoryId) and 
not exists (select * from ProjLedgerJournalTransUnpostedView where CATEGORYTABLE.CategoryId =  ProjLedgerJournalTransUnpostedView.CATEGORYID)


@Rahul Talasila

1 comment:

  1. Super useful post, Rahul! Cleaning up unused project categories in AX 2012 can really streamline system performance and reduce clutter in reports. The combination of X++ code and SQL query makes this easy to implement across environments. For those managing ERP systems like this, choosing the right hosting provider matters too I’d recommend checking out this webspace web hosting option for reliable and scalable performance. Looking forward to more deep-dive posts like this!

    ReplyDelete

What is the primary purpose of using a Solution in Microsoft Power Platform & ALM?

As organizations embrace low-code development with Microsoft Power Platform , it becomes essential to manage and govern apps, flows, and dat...