Fixed Assets History Report (Version 2)

After my post  Fixed Assets History Report , I have re-created the view trying to be more efficient and here you go :
SELECT     dbo.FA00100.ASSETINDEX, dbo.FA00100.ASSETID, dbo.FA00100.ASSETDESC, dbo.FA00100.ASSETCLASSID, dbo.FA00100.ACQDATE,
                      dbo.FA00100.Acquisition_Cost, dbo.FA00902.BOOKINDX, dbo.FA00902.GLINTBTCHNUM, dbo.GL20000.JRNENTRY, dbo.FA00902.AMOUNT,
                      dbo.FA00902.TRANSACCTTYPE, dbo.FA00902.SOURCDOC, dbo.PM00200.VENDNAME, dbo.FA01400.VENDORID, dbo.GL20000.TRXDATE,
                      dbo.FA00902.FAYEAR, dbo.FA00902.FAPERIOD
FROM         dbo.FA00100 INNER JOIN
                      dbo.FA00902 ON dbo.FA00100.ASSETINDEX = dbo.FA00902.ASSETINDEX INNER JOIN
                      dbo.GL20000 ON dbo.FA00902.GLINTBTCHNUM = dbo.GL20000.ORGNTSRC AND dbo.FA00902.GLINTACCTINDX = dbo.GL20000.ACTINDX INNER JOIN
                      dbo.FA01400 ON dbo.FA00100.ASSETINDEX = dbo.FA01400.ASSETINDEX INNER JOIN
                      dbo.PM00200 ON dbo.FA01400.VENDORID = dbo.PM00200.VENDORID
WHERE     (dbo.FA00902.BOOKINDX =
                          (SELECT     CORPBOOKINDX
                             FROM         dbo.FA49900)) AND (dbo.FA00902.GLINTBTCHNUM IS NOT NULL) AND (dbo.FA00902.AMOUNT > 0)
 
 Hope that helps

Edit : I thought that i should also post the conditions and some more explanations :

• If you summarize the financails from fixed assets this SQL script will not work.
• Some fileds are missing comparing to the below first script , like Assets Class Description.
• GL posting from Fixed Assets and Posting Fixed Assets Journals from GL should be performed (2 Posts should be completed before running the script. other wise records will not be retrieved.)
• As Mark mentioned, is it "problematic" .

Dynamics GP Copy Company Tool

After i have created a new Dynamics GP Company , i used The Copy Company Tool , which copies master files and setup tables from an old company to a new one as below. (It seems i just discovered something now while trying to capture a screen shot , As i can see it doesn’t detect 64 Bit ODBC i am running Windows 7 64 which i use for Dynamics GP, you should define normal ODBC) .

 

After running through the process everything went smoothly , I logged to Dynamics GP and Opened Inquiry –> Inventory –> Item. I found some quantities there . It seems it just copied the tables fields, So directly i run a Inventory reconcile but quantities are still there . weird isn’t it ?

Note : I only run reconcile on Inventory and it didn’t work, so you better try to reconcile the others first.

Any way , i created SQL scripts for resetting and below they are :

AR Reset Script	AP Reset Script
UPDATE [TWO].[dbo].[RM00103]    SET [CUSTBLNC] = 0       ,[AGPERAMT_1] = 0       ,[AGPERAMT_2] = 0       ,[AGPERAMT_3] = 0       ,[AGPERAMT_4] = 0       ,[AGPERAMT_5] = 0       ,[AGPERAMT_6] = 0       ,[AGPERAMT_7] = 0       ,[LPYMTAMT] = 0       ,[LSTTRXAM] = 0       ,[LSTFCHAM] = 0       ,[UPFCHYTD] = 0       ,[TDTKNYTD] = 0       ,[TDTKNLYR] = 0       ,[TDTKNLTD] = 0       ,[TDISAYTD] = 0       ,[RETAINAG] = 0       ,[TNSFCYTD] = 0       ,[UNPSTDSA] = 0       ,[UNPSTDCA] = 0       ,[UNPSTOSA] = 0       ,[UNPSTOCA] = 0       ,[NCSCHPMT] = 0       ,[TTLSLYTD] = 0       ,[TTLSLLTD] = 0       ,[TTLSLLYR] = 0       ,[TCOSTYTD] = 0       ,[TCOSTLTD] = 0       ,[TCOSTLYR] = 0       ,[TCSHRYTD] = 0       ,[TCSHRLTD] = 0       ,[TCSHRLYR] = 0       ,[TFNCHYTD] = 0       ,[TFNCHLTD] = 0       ,[TFNCHLYR] = 0       ,[FNCHCYTD] = 0       ,[FNCHLYRC] = 0       ,[TBDDTYTD] = 0       ,[TBDDTLYR] = 0       ,[TBDDTLTD] = 0       ,[TWVFCYTD] = 0       ,[TWVFCLTD] = 0       ,[TWVFCLYR] = 0       ,[TWROFYTD] = 0       ,[TWROFLTD] = 0       ,[TWROFLYR] = 0       ,[WROFSLIF] = 0       ,[WROFSLYR] = 0       ,[WROFSYTD] = 0       ,[HIBALLYR] = 0       ,[HIBALYTD] = 0       ,[HIBALLTD] = 0       ,[LSTSTAMT] = 0       ,[ONORDAMT] = 0       ,[TTLRTYTD] = 0       ,[TTLRTLTD] = 0       ,[TTLRTLYR] = 0	UPDATE [TWO].[dbo].[PM00201]    SET [HIESTBAL] = 0       ,[CURRBLNC] = 0       ,[NOINVYTD] = 0       ,[NOINVLIF] = 0       ,[NOINVLYR] = 0       ,[NOPINYTD] = 0       ,[NOPILIFE] = 0       ,[AMBLDTYD] = 0       ,[AMBLDLIF] = 0       ,[AMBLDLYR] = 0       ,[AMTPDYTD] = 0       ,[AMTPDLIF] = 0       ,[AMTPDLYR] = 0       ,[TEN99AYTD] = 0       ,[TEN99ALIF] = 0       ,[TEN99ALYR] =0       ,[DISAVYTD] = 0       ,[DISAVLIF] = 0       ,[DISAVLYR] = 0       ,[DISTKYTD] = 0       ,[DISTKNLF] = 0       ,[DISTKLYR] = 0       ,[DISLSYTD] = 0       ,[DISLSTLF] = 0       ,[DISLSLYR] = 0       ,[FINCHLIF] = 0       ,[FINCHLYR] = 0       ,[FINCHYTD] = 0       ,[WROFSYTD] = 0       ,[WROFSLIF] = 0       ,[WROFSLYR] = 0       ,[RTRNSYTD] = 0       ,[RTRNSLIF] = 0       ,[RTRNSLYR] = 0       ,[TRDTKLIF] = 0       ,[TRDTLYR] = 0       ,[TRDTYTD] = 0       ,[NFNCHLIF] = 0       ,[NFNCHLYR] = 0       ,[NFNCHYTD] = 0       ,[RTNGOWED] = 0       ,[LSTCHAMT] = 0       ,[LSTINVAM] = 0       ,[CURUNPBN] = 0       ,[UNPDFNCH] = 0       ,[DYCHTCLR] = 0       ,[AVGDTPYR] = 0       ,[AVDTPLIF] = 0       ,[ACCRDINV] = 0       ,[ONORDAMT] = 0       ,[WITHYTD] = 0       ,[WITHLYR] = 0
BR Reset Script	INV Reset Script
UPDATE [TWO].[dbo].[CM00100]    SET [Last_Reconciled_Balance] = 0       ,[CURRBLNC] = 0       ,[Reconcile_In_Progress] = 0       ,[Deposit_In_Progress] = 0       ,[ADPVADLR] = 0	Update     IV00102 Set       [QTYRQSTN] = 0       ,[QTYONORD] = 0       ,[QTYBKORD] = 0       ,[QTY_Drop_Shipped] = 0       ,[QTYINUSE] = 0       ,[QTYINSVC] = 0       ,[QTYRTRND] = 0       ,[QTYDMGED] = 0       ,[QTYONHND] = 0       ,[ATYALLOC] = 0       ,[QTYCOMTD] = 0       ,[QTYSOLD] = 0       ,[LSORDQTY] = 0       ,[LRCPTQTY] = 0

 

Hope that helps

Edit : Please refer to This, it is so important to read.

Dynamics GP Utilities hang up while trying to update some companies

Before few days , I have received an issue which needed to be solved.The issue was Dynamics GP utilities hang up while trying to update companies as below screenshot shows .

After running the SQL profiler , Dynamics GP was like going into infinite loop or something like that looking for those databases which actually are not exist. The issue started by deleting some companies in Dynamics and the SQL databases , but for some reason records were available in dbo.dynamics.db_upgrade.

So to solve the issue , just delete those records in db_upgrade which related to already deleted companies or databases.

Hope that helps.

Fixed Assets History Report

As we all know , after you do the year end closing to Fixed Assets in Dynamics the automatic printed report can’t be re-printed.
so i have tried to create some sort of SQL script to retrieve a detailed data for the Assets Depreciation (Asset ID , Asset Description , Year , Period , Depreciation General Journal Number , General Journal Date , Vendor ID , Vendor Name (If the asset was purchased of course) . Purchase document number ,..etc)  i did that although it has some limitations which are :

1. It only applies for cooperate book which is in the Fixed Assets Company Setup.
   
2. Transactions should be posted from Fixed Assets to General Ledger and posted from General Ledger .
   
3. If summarize financial process for a specific year , this year transactions will not appear for sure.
   
4. If the automatic created fixed assets general journal batch changed in General Ledger it will not be detected in the query.
   
5. This query detects depreciation journals only (Can be modified by removing filters).

The query will be posted tomorrow , after testing.
Edit : This is the script
USE [TWO] GO /****** Object:  View [dbo].[FIXED_ASSETS_HISTORY]    Script Date: 09/23/2010 06:53:38 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER VIEW [dbo].[FIXED_ASSETS_HISTORY] AS SELECT     dbo.FA00100.ASSETINDEX AS [Asset Index], dbo.FA00100.ASSETID AS [Asset ID], dbo.FA00100.ASSETDESC AS [Asset Description], dbo.FA00902.BOOKINDX,                       dbo.FA00902.FAYEAR AS Year, dbo.FA00902.FAPERIOD AS Period, dbo.FA01400.Acquisition_Cost AS [Acquisition Cost], dbo.FA00902.AMOUNT AS Amount,                       dbo.FA00902.SOURCDOC, dbo.GL20000.JRNENTRY AS [Journal Entry Number], dbo.GL20000.TRXDATE AS [Journal Entry Date],                       dbo.FA01400.VENDORID AS [Vendor ID], dbo.PM00200.VENDNAME AS [Vendor Name], dbo.FA00902.TRANSACCTTYPE,                       dbo.FA00902.GLINTBTCHNUM AS [General Ledger Batch Number], dbo.FA40201.ASSETCLASSIDDESC AS [Asset ClassDescription],                       dbo.FA00100.ACQDATE AS [Acquisition Date], dbo.FA00100.ASSETCLASSID AS [Asset Class ID] FROM         dbo.FA00100 INNER JOIN                       dbo.FA00902 ON dbo.FA00100.ASSETINDEX = dbo.FA00902.ASSETINDEX INNER JOIN                       dbo.FA40201 ON dbo.FA00100.ASSETCLASSID = dbo.FA40201.ASSETCLASSID LEFT OUTER JOIN                       dbo.GL20000 ON dbo.FA00902.GLINTBTCHNUM = dbo.GL20000.ORTRXSRC LEFT OUTER JOIN                       dbo.FA01400 ON dbo.FA00100.ASSETINDEX = dbo.FA01400.ASSETINDEX LEFT OUTER JOIN                       dbo.PM00200 ON dbo.FA01400.VENDORID = dbo.PM00200.VENDORID RIGHT OUTER JOIN                       dbo.FA00400 ON dbo.GL20000.ACTINDX = dbo.FA00400.DEPREXPACCTINDX AND dbo.FA00100.ASSETINDEX = dbo.FA00400.ASSETINDEX WHERE     (dbo.FA00902.BOOKINDX =                           (SELECT     CORPBOOKINDX                              FROM         dbo.FA49900)) AND (dbo.FA00902.GLINTBTCHNUM IS NOT NULL) AND (dbo.FA00902.TRANSACCTTYPE = 2)
So you can do the math on Crystal Report to calculate depreciation amount for an asset or asset class for a year …etc , accumulated deprecation till specific period , Netbook value …etc
Hope this helps.

Thank you Mohammad

This is a special thanks for my colleague Mohammad R. Daoud for supporting my new blog.

Thanks again Mohammad.

Don’t marry a Software Engineer… :x

If you want to marry a Software Engineer , You should read this before :

Husband: (Get home late from office) Good night darling, I am now logged in
Wife: Do you bring the souvenirs that I asked you?
Husband: Bad command or filename

Wife: But I already told you this morning!
Husband: Syntax error. Abort?

Wife: Then, how about to buy a new television?
Husband: Variable not found…

Wife: Ok then, if that so, I want your credit card. I would like to go shop alone.
Husband: Sharing violation. Access denied…

Wife: Do you love computer more than me? Or you just play joke with me?
Husband: Too many parameters…

Wife: It’s a big mistake to married with an idiot person like you.
Husband: Data type mismatch.

Wife: You are useless.
Husband: It’s by default

Wife: How about your wages?
Husband: File in use … Please try again later.

Wife: If that so, what is my position in this family !!!???
Husband: Unknown virus…

Manual Payment : Cash account is missing

While i was browsing Dynamics GP forum ,  a question was asked “ In Manual Payment module the bank account distribution is not automatically appearing on the distribution ” , in other words , Why is the Cash account in the Manual Payment Distribution is Empty even the Checkbook is linked to a General Ledger Account (since that filed is required) ?
The Question is screenshot :
  It shows the Checkbook Account is linked already with General Ledger Account.
  The same checkbook was selected.
  The account is missing in the Manual Payment Distribution Window.

How to Solve it :

• Be sure NOT to select Cash Account from Vendor and leaves the Cash Account Blank as below :

Please read below paragraph as it was taken (Copy and Paste) from Dynamics GP Help :
“
Use Cash Account From
Mark Checkbook or Vendor to indicate the Cash account you use most often for this vendor's transactions. If you mark Vendor, enter the Cash account for the vendor in the Cash field. If you mark Checkbook, the Cash account assigned to the checkbook you used for the transaction appears as the default account.
If you use Bank Reconciliation, we recommend you use the Cash account from the checkbook so you can easily reconcile the Cash account for a checkbook entered in the Checkbook Maintenance window to the Cash account balance in General Ledger.
If you are using European Electronic Funds Transfers, the cash in transit account assigned to the checkbook is used, regardless of which option is marked in this field
“
Even if you select the above (Not recommended) , and the Cash Accounts in the Posting Account –> Purchasing was filled up , Dynamics GP will FILL up the Cash Account in the Manual Payment Distribution.


So to have an Empty Cash Account in the Manual Payment Distribution Entry 2 conditions should be true.

1. Select Cash account from vendor accounts and the cash account should be blank.
   
2. Cash account in the Purchasing posting account should be blank.

Hope this helps

Edit : If the above is not working , please check and complete Payment Document Management setup

Checkbook Balance Inquiry

An e-mail was received with only the below screen shot (Screenshots were recreated on a Fabrikam Inc.) for testing and for this post)

Three things were found in this screen shot :

1. Since in the Checkbook Setup , every checkbook is linked to one General Ledger Account , Current Checkbook Balance and Current Account Balance fields should be matched but they are not.
   
2. The Checkbook Balance after the first transaction is not correct.
   
3. The totals for the transactions in not correct it should be 1300 (As per screen shot above)

After searching and looking inside , reason for the above case was found , updating CURRBLNC field in CM00100 directly by SQL script , 3rd party product…etc , and it seems the form above taking as “Copy and Paste” the currblnc field and do the math for the transactions total if they match then no problem everyone is happy , but if not it will adjust the first transaction balance.
Hope this helps.

A good question : How can i know if my iPhone is factory unlocked or not ?

A friend of mine called me , he got an 3Gs iPhone used one with 16 GB.

The phone was JB with spirit since it has 3.1.3 Firmware. (most probably) , and he wanted to reset it to factory settings ,the answer was NO, Don’t do that because you don’t know the origin for the iphone.

Explanation :

If you don’t have the official sim carrier and your iphone is NOT a factory unlocked , your little toy will lock and since there is no hactivation tool for 3Gs new bootroom and you will through it to the Oops (Garbage).

So How can i know from where my Iphone came from and is it factory unlocked or not ?

This is the How to :

• Go to Settings –> General –> About
• Scorlldown for Model as below screenshot and get the number :

• Search for your model number in the below list :

Belgium, Mobistar ————————— MB489NF/A MB496NF/A MB500NF/A MC131NF/A MC132NF/A MC133NF/A MC134NF/A	CzechRepublic, O2 ————————— MB489CZ/A MB496CZ/A MB500CZ/A MC131CZ/A MC132CZ/A MC133CZ/A MC134CZ/A	CzechRepublic, T-Mobile ————————— MB490CZ/A MB497CZ/A MB501CZ/A MC139CZ/A MC140CZ/A MC141CZ/A MC142CZ/A
Greece, Vodafone ————————— MB489GR/A MB496GR/A MB500GR/A MC131GR/A MC132GR/A MC133GR/A MC134GR/A	Hong Kong, 3 ————————— MB489ZP/A MB496ZP/A MB500ZP/A MC131ZP/A MC132ZP/A MC133ZP/A MC134ZP/A	Italy, TIM ————————— MB489T/A MB496T/A MB500T/A MC131T/A MC132T/A MC133T/A MC134T/A
Macau, 3 ————————— MB489ZP/A MB496ZP/A MB500ZP/A MC131ZP/A MC132ZP/A MC133ZP/A MC134ZP/A	New Zealand, Vodafone ————————— MB489X/A MB496X/A MB500X/A MC131X/A MC132X/A MC133X/A MC134X/A	Singapore, SingTel ————————— MB489ZA/A MB496ZA/A MB500ZA/A MC131ZA/A MC132ZA/A MC133ZA/A MC134ZA/A
Saudi Arabia, Mobily ————————— MB489AB/A MB496AB/A MB500AB/A MC131AB/A MC132AB/A MC133AB/A MC134AB/A	UAE, etisalat ————————— MB489AB/A MB496AB/A MB500AB/A MC131AB/A MC132AB/A MC133AB/A MC134AB/A	South Africa, Vodacom ————————— MB489SO/A MB496SO/A MB500SO/A MC131SO/A MC132SO/A MC133SO/A MC134SO/A
Turkey, TurkCell ————————— MB489TU/A MB496TU/A MB500TU/A MC131TU/A MC132TU/A MC133TU/A MC134TU/A	Russia, MegaFon ————————— MB489RS/A MB496RS/A MB500RS/A MC131RS/A MC132RS/A MC133RS/A MC134RS/A	Russia, MTS ————————— MB489RS/A MB496RS/A MB500RS/A MC131RS/A MC132RS/A MC133RS/A MC134RS/A
Luxembourg, Vox Mobile ————————— MB489NF/A MB496NF/A MB500NF/A MC131NF/A MC132NF/A MC133NF/A MC134NF/A	Luxembourg, LUXGSM ————————— MB489FB/A MB496FB/A MB500FB/A MC131FB/A MC132FB/A MC133FB/A MC134FB/A	Usa , AT&T ————————— MC126LL/A MC137LL/A MC150LL/A MC161LL/A MC167LL/A MC178LL/A MC185LL/A
Luxembourg, Tango ————————— MB489FB/A MB496FB/A MB500FB/A MC131FB/A MC132FB/A MC133FB/A MC134FB/A	Russia, Beeline ————————— MB489RS/A MB496RS/A MB500RS/A MC131RS/A MC132RS/A MC133RS/A MC134RS/A	Taiwan,Chunghwa Telecom ————————— MB489TA/A MB496TA/A MB500TA/A MC131TA/A MC132TA/A MC133TA/A MC134TA/A
Slovakia, Orange ————————— MB489SL/A MB496SL/A MB500SL/A MC131SL/A MC132SL/A MC133SL/A MC134SL/A	Italy, Vodafone ————————— MB489T/A MB496T/A MB500T/A MC131T/A MC132T/A MC133T/A MC134T/A	CzechRepublic, Vodafone ————————— MB489CZ/A MB496CZ/A MB500CZ/A MC131CZ/A MC132CZ/A MC133CZ/A MC134CZ/A
Jordan ,Orange —————————MB489AB/A MB496AB/A MB500AB/A MC131AB/A MC132AB/A MC133AB/A MC134AB/A	UAE, DU —————————MB489AB/A MB496AB/A MB500AB/A MC131AB/A MC132AB/A MC133AB/A MC134AB/A	Egypt,Mobnil —————————MB489AB/A MB496AB/A MB500AB/A MC131AB/A MC132AB/A MC133AB/A

• After determining the country , Go to apple website : (http://support.apple.com/kb/HT1937).
• Select the region and look for Locked to carrier field .

Note : Some Countries and Carries may have same model number.

Hope this helps