web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Removing un-wanted req...
Microsoft Dynamics GP (Archived)

Removing un-wanted requisition in Business Portal

(0) ShareShare
ReportReport
Posted on by babubaskaran@outloo... 12

Dear All,

I have few number of requisition on business portal which is not going to be used which is old I wanted to remove the same from the system.  Can it be done within Business Portal or else removing from Dynamics database by deleting is the only option available. 

*This post is locked for comments

I have the same question (0)
  • Rob Bernhardt Profile Picture
    Rob Bernhardt on at

    Babu,

    What version of GP/BP are you using?  You can manage the purchase requests from within BP.  

    If you are no longer using BP, you could uninstall it and choose to remove data.  

    Thanks

    Rob

  • babubaskaran@outlook.com Profile Picture
    babubaskaran@outloo... 12 on at

    Hi Rob,

    Thanks for your reply.  We are using GP 2013 R2 and BP I think 6.0 not sure how to check the version of BP but it is upgraded while upgrading 2013 R2.  These are requisition which is approved but not going to be converted to PO.  Can you please let me know how to remove the same.

  • Verified answer
    Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,058 Moderator on at

    Hi Babu,

    I've created myself a few queries years ago to manage the requisitions in BP (was 4.0 at that time, but still valid for 5.0 & 6.0 I think).

    /* Use this script to query BP requisition documents
    (c) buchb / 2010-07-21
    */
    use DYNAMICS
    declare @REQNUMB varchar(20);
    declare @CPNY int;
    select @REQNUMB = 'R-MTL00115%' --< change the Req #
    select @CPNY = 2    --< change the company origin # (1-LAR, 2-MTL, 3-DUB, 14-FTT, 16-FTM, 10-TST)

    print 'ReqMgmtOpenTasks Table :'
    select * from ReqMgmtOpenTasks where UniqueID like @REQNUMB and CompanyID = @CPNY order by UniqueID desc
    print 'ReqMgmtDocument Table :'
    select * from ReqMgmtDocument where DocumentID like @REQNUMB and CompanyID = @CPNY order by DocumentID desc
    print 'ReqMgmtLines Table :'
    select * from ReqMgmtLines where DocumentID like @REQNUMB and CompanyID = @CPNY order by DocumentID desc
    print 'ReqMgmtAuditHistoryDocument Table :'
    select * from ReqMgmtAuditHistoryDocument where DocumentID like @REQNUMB and CompanyID = @CPNY
    print 'ReqMgmtAuditHistoryLines Table :'
    select * from ReqMgmtAuditHistoryLines where DocumentID like @REQNUMB and CompanyID = @CPNY order by EditSequenceNumber, LineSequenceNumber

    /* current locked requisitions */
    print 'MultiUserManager Table :'
    select * from MultiUserManager
    -- Delete from MultiUserManager where KeyValue like 'R-MTL0009222%'
    /* Purge Requistions by DocumentID */
    print 'Deletion of records'
    /*
    Delete from ReqMgmtDocument where DocumentID like @REQNUMB and CompanyID = @CPNY
    Delete from ReqMgmtLines where DocumentID like @REQNUMB and CompanyID = @CPNY
    Delete from ReqMgmtOpenTasks where UniqueID like @REQNUMB and CompanyID = @CPNY
    Delete from ReqMgmtAuditHistoryDocument where DocumentID like @REQNUMB and CompanyID = @CPNY
    Delete from ReqMgmtAuditHistoryLines where DocumentID like @REQNUMB and CompanyID = @CPNY
    */
    /*
    update ReqMgmtDocument
        set Status = 1
        where DocumentID = 'R-MTL0008140' and Status = 2
    update ReqMgmtDocument
        set CurrentAssignedType = 1
        where DocumentID = 'R-MTL0008140' and CurrentAssignedType = 2
    Update ReqMgmtLines
        set LineStatus = 3
        where DocumentID = 'R-MTL0004885' and LineStatus = 2
    */

    The variable for the Company # has to be matched with your company # from your SY01500 table...

    The second part of the script allows you either to delete completely some entries in the requisition management system if you remove the '/* */'  signs..


    The last section allows you to selectively change the document status for a requisition that has already been submitted for example and set it back to 'edit' mode for the end-user, at which point it should be available for 'deletion' to the end-user.

    As usual, take a full backup or your DYNAMICS database before you play with this (Req Mgmt only uses the GP System DB, not the company DB's).

  • babubaskaran@outlook.com Profile Picture
    babubaskaran@outloo... 12 on at

    Hi Beat,

    Thanks for your kind reply, I will try this on tomorrow and let you know.

  • babubaskaran@outlook.com Profile Picture
    babubaskaran@outloo... 12 on at

    Hi Beat,

    Since it live environment I have to plan this properly and do the same.  Just wanted to confirm if I run the following script alone will bring back the requisition to original position where the user can delete the requisition within BP.

    use DYNAMICS

    declare @REQNUMB varchar(20);

    declare @CPNY int;

    select @REQNUMB = 'R-MTL00115%' --< change the Req #

    select @CPNY = 01

    update ReqMgmtDocument

       set Status = 1

       where DocumentID = 'R-MTL0008140' and Status = 2

    update ReqMgmtDocument

       set CurrentAssignedType = 1

       where DocumentID = 'R-MTL0008140' and CurrentAssignedType = 2

    Update ReqMgmtLines

       set LineStatus = 3

       where DocumentID = 'R-MTL0004885' and LineStatus = 2

  • Suggested answer
    Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,058 Moderator on at

    Hi Babu,

    It's been a while now since we switched away from BP RM to eRequester :-)... so bare with me... (actually for over 18 months).

    Below is the corrected script you should run. Take a backup of your DYNAMICS DB first before you do this (or at least from the 2 concerned tables).

    All you have to do is replace the document number for the variable @REQNUM and the Company ID for @CPNY (Since you could potentially run RM with the same # schema against different GP companies).

    use DYNAMICS

    declare @REQNUMB varchar(20);

    declare @CPNY int;

    select @REQNUMB = 'R-MTL0008140' --< change the Req #

    select @CPNY = 1 -- this has to match the Company ID from the SY01500 table

    update ReqMgmtDocument

      set Status = 1

      where DocumentID = @REQNUMB  and Status = 2 and CompanyID = @CNPY

    update ReqMgmtDocument

      set CurrentAssignedType = 1

      where DocumentID = @REQNUMB  and CurrentAssignedType = 2 and CompanyID = @CNPY

    Update ReqMgmtLines

      set LineStatus = 3

      where DocumentID = @REQNUMB  and LineStatus = 2 and CompanyID = @CNPY

  • Redbeard Profile Picture
    Redbeard 12,931 on at

    Very cool!

  • babubaskaran@outlook.com Profile Picture
    babubaskaran@outloo... 12 on at

    Thanks a million for your kind reply Beat I will try this and let you know.

  • babubaskaran@outlook.com Profile Picture
    babubaskaran@outloo... 12 on at

    Hi Beat,

    Sorry for the delay in reply.  I was travelling and holidays then travel so getting a chance now only.  I have ran the above script but still the request is not available for delete.  I went to Purchase requests under employee and can see the request but when I select it the edit request is not enabled.  Also I am not sure once I go to edit request will allow to delete the whole requisition itself since there is a button delete item and not delete requisition.

  • Suggested answer
    Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,058 Moderator on at

    Hi Babu,

    When your run this script against the DYNAMICS DB, what is the status of the entry for this requisition # ?

    print 'ReqMgmtDocument Table :'

    select * from ReqMgmtDocument where DocumentID like @REQNUMB and CompanyID = @CPNY order by DocumentID desc

    the 2 variables @REQNUMB & @CPNY have to be replaced by your values... CPNY is the CompanyID from SY01500.

    An 'open' requisition will have the status=4, a 'Saved' req will be = 1 and a 'Completed' req will = 5.

    You need to make sure that it has 1 as value and then when you open the requisition for revision (make sure to use 'Edit' and not 'View'), you click on the option "Show more tasks" at the right bottom. There you can check the 'Delete' button and click on OK.

    Hope this helps.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
mtabor Profile Picture

mtabor 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans