Extracting payroll data from GP

Hello, Everybody,

I did work with GP a while ago and remembered many tables numbers from there but I'd need to refresh my memory on it now.

I was asked to extract data from the Ms Dynamics GP 2013 and load it into ASCII text files. I'm going to do it via SQL job and SSIS that could be a usual task. I could go through GP figuring out needed tables numbers but if somebody could advise what GP tables could be involved that would be greatly appreciated.

Thanks

 


 

  • Are you working for any specific module?

    Since you know all GP tables have DEX_ROW_ID field you can find out GP tables from SQL by using following sql statement.

    I have included the inter id too so if you have multiple companies you can use it for job.


    Select TABLE_CATALOG AS INTERID,TABLE_NAME AS TableName
    From INFORMATION_SCHEMA.COLUMNS
    Where COLUMN_NAME ='DEX_ROW_ID'

  • In reply to almas:

    oops you mention in your subject, you are looking for payroll tables. :)

    Select TABLE_CATALOG AS INTERID,TABLE_NAME AS TableName,*
    From INFORMATION_SCHEMA.COLUMNS
    Where COLUMN_NAME ='DEX_ROW_ID'
    and TABLE_NAME like 'UPR%'

  • In reply to almas:

    Thank you very much. I'll definitely use this query.

    It's actually the Canadian payroll so the tables coding could be different. I found a one table from the old GP I used in my application a while ago it's started with CPY (CPY10140).

    There is also a great article on GP tables I found: However there is not much (nothing) on the payroll side.

     http://msdynamicstips.com/2012/05/20/data-flow-and-table-names/

    Nevertheless, I'm getting more understanding on the subject. now. Thanks again.

  • In reply to Alexander:

    Hmmm Canadian Payroll, actually we work with most of USA clients that's why just ignore that you are from Canada. LOL

    May be will know better about Canadian Payroll when will get chance to work with Canadian Client. ;-)

    You are right for Canadian Payroll prefix is CP, and if you are using Human Resource too then better look for it's tables too.

    Also check this link for tables detail, have information about Canadian Payroll and HR too.

    http://www.docstoc.com/docs/70799988/Excel-Template-1042

     

Related
Recommended