485
I am using GP 2010. While performing Inventory Transfer Entry, I assign manual accounts from distribution button (Item Transfer Entry), due to some unavoidable reasons I have to assign manual accounts from Distribution. Entry is posted. Now I want a smartlist/report from where I can see my posted transfer entries with (run-time) manually assign accounts. Any proposed SQL View or statement to complete this task? or any other way?
*This post is locked for comments
Finally I got the solution by executing the following SQL Statement:
SELECT dbo.IV30200.BACHNUMB AS Batch_No, dbo.IV30200.DOCNUMBR AS Doc_Number, dbo.IV30200.DOCDATE AS Doc_Date,
IV30300.ITEMNMBR AS Item_Number, IV30300.TRXQTY AS Qty_Transferred, IV30300.UOFM AS Unit_of_Measure, IV30300.UNITCOST AS Unit_Cost,
IV30300.EXTDCOST AS Extended_Cost, IV30300.TRXLOCTN AS From_Site, IV30300.TRNSTLOC AS To_Site,
dbo.GL00105.ACTNUMST AS Inventory_Account, dbo.GL00100.ACTDESCR AS IV_Acct_Description, GL00105_1.ACTNUMST AS IV_Offset_Account,
GL00100_1.ACTDESCR AS IV_Offset_Acct_Description, IV30300.DOCTYPE
FROM dbo.GL00100 INNER JOIN
dbo.IV30200 INNER JOIN
dbo.IV30300 AS IV30300 ON dbo.IV30200.DOCNUMBR = IV30300.DOCNUMBR ON dbo.GL00100.ACTINDX = IV30300.IVIVINDX INNER JOIN
dbo.GL00105 ON dbo.GL00100.ACTINDX = dbo.GL00105.ACTINDX INNER JOIN
dbo.GL00100 AS GL00100_1 INNER JOIN
dbo.GL00105 AS GL00105_1 ON GL00100_1.ACTINDX = GL00105_1.ACTINDX ON IV30300.IVIVOFIX = GL00100_1.ACTINDX
WHERE (dbo.IV30200.IVDOCTYP = 3) OR
(dbo.IV30200.IVDOCTYP = 1)
Working perfectly fine for posted Transactions :)
For unposted transactions SQL Statement is in my previous Question.
Tristan, Leslie gave me a solution for un-posted transfer entries which is working fine, however for posted entries I am still looking for a solution. I just need basic information for a transfer entry with accounts attached via distribution during entry. That's it. :)
I thought Leslie was able to give this to you on a separate thread?
Anyways, this is out of regular Smartlist bounds as well as Smartlist Builder, you'll need to create an SQL View for this.
Any particular fields you'd like to show on the view?
I don't think you have an out-of-box Smartlist option to show the transfers with distribution accounts, as the distributions are stored in different table altogether.
Since you are in GP2010, the only option is to use Smartlist Builder to create a new Smartlist or to modify existing Inventory Transaction Smartlist.
You may also contact your partner or consultant to create a custom SQL view, that could be used in an Excel file. That would be totally out of GP.
#1
André Arnaud de Cal...
292,516
Super User 2025 Season 1
#2
Martin Dráb
231,321
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
