Dynamics GP - Compass Point of Sale

When you install Compass on a workstation, you do not see any of the compass menus or windows even though you have given security access to the user accounts.

Run the compass install again, but choose "remove" to remove it entirely
Run the compass install again and tick Server and Workstation. Remember to change the directory to the gp program files directory.
Complete the installation.

Everything should work fine now.

Dynamics GP - Smartfill Install

Screen sticks the first time at "initializing dynamics gp" when you run Dynamics GP after copying the files into the program files folder.

Just wait 15-20 mins, it will finish eventually, and complete the installation.

SQL - Transpose Columns to rows

Convert column headers into row values, and expand row values into multiple rows under a single column using UNPIVOT

Great example here

http://blog.logiclabz.com/sql-server/transpose-columns-to-rows-in-sql-server-2005-using-unpivot.aspx

https://stackoverflow.com/questions/20111418/sql-server-transpose-rows-to-columns

https://blogs.oracle.com/sql/how-to-convert-rows-to-columns-and-back-again-with-sql-aka-pivot-and-unpivot

Dynamics GP - HR Tables

UPR00100 - Payroll Employee Master
UPR10301 - Transaction Entry Batch
UPR10302 - Transaction Entry Line
UPR10204 - Deduction Work (After Calculate, Before Post)
UPR10205 - Benefit Work (After Calculate, Before Post)

UPR30300 -
UPR40200 - Payroll Number Setup
UPR40300 - Payroll Department Master
UPR40301 - Payroll Position Setup
UPR40600 – Payroll TimeCode Master
UPR41700 - Payroll Supervisor Setup
UPR40800 - Benefit Setup
UPR40900 - Deduction Setup

UPR41200 - Payroll Class Setup

HR2NOT01 - HR Notes

Hrm_VacationRequest Table –DocumentStatus field

DocumentStatus = 1 means the request has been submitted and/or rejected.

DocumentStatus = 2 means the request has been approved.

DocumentStatus = 3 means the request has been aborted (likely due to it being rejected).

Who is leave assigned to (i.e. based on the Workflow Business Process where is it currently): DYNAMICS database – Hrm_WorkflowDocument Table

HR does not track the date the leave applications were created.
A trigger has to be created for that purpose.

HR does not track if a request has been rejected.
The information is stored in workflow tables here. I have attempted to use these tables, but the functionality is limited.


select* from MbfWorkItemProcess
select* from Hrm_WorkflowProcess
select* from Hrm_ActivityHostProcess
select* from dbo.MbfRunningProcess
select* from MbfProcessAlias
select* from mbfworkfloweventlog
select* from MbfWorkItem
select* from MbfWorkItemProcess
select* from MbfSystemWorkitem
select* from MbfSystemWorkItemNonStateAction
select* from MbfProviderWorkItemAction
select* from MbfClientWorkItemAction
select* from MbfProcessAlias

RV010121 - HR>Employee>Review Header
RV010221 - HR>Employee>Review Detail
TATM1030 - Employee Vacation Used / Remaining

Other HR/ Time & Attendance tables begin with TA

All BP tables begin with HRM

Hrm_Timecard – Timecard header
Hrm_TimecardDetail – Timecard daily details
Hrm_VacationRequest – Vacation request details


Employmenttype Values (UPR00100)
1 = Full Time Regular
2 = Full Time Temp
3 = Part Time Regular
4 = Part Time Temp
5 = Intern
6 = Other

Dynamics GP - Requisition Management Tables

All requisition management tables are stored in the DYNAMICS database and begin with ReqMgmt

Crystal Reports XI does not work on Windows server 2008 or any 64-bit environment

Crystal Reports XI does not work on Windows server 2008 or any 64-bit environment.

Crystal Reports XI release 2, service pack 2 or higher will work.
You can download it here
https://websmp230.sap-ag.de/sap(bD1lbiZjPTAwMQ==)/bc/bsp/spn/bobj_download/main.htm

More information on Crystal service packs
http://victoriayudin.com/2009/05/25/crystal-reports-service-packs-and-versions/

Dynamics GP - Manufacturing tables

MO Setup - Numbering - Each company's next number sequence is stored in the Dynamics db
MOPS0100 - Manufacturing Order Preference Defaults (has a company id in the table)

CT00102 - Item Account Maintenance - Costing (Additional Accounts window on Item Card)
CT40401 - Item Class Account Maintenance - Costing (Additional accounts window on Item Class)

• icivmuixi_VarMat
• icivwmixi_WipMaterial
• icivcmixi_CogsMaterial

MM010032 - Machine Definition Master
IVR10015 - Item Engineering Data
IV00102 - Item Quantities / Resource Planning
IV00115 - Manufacturing Item Information
LC010014 - Labor Code Master

SP010016 - Scheduling Preference Master

IV40700 - Site Master (Required for Work Centers)
WC010015 - Work Center Master
WC010931 - Work Center Header (you need both)

BM010415 - Manufacturing BOM Master Header
BM010115 - Manufacturing BOM Line Item Detail
BM10200 - BOM Routing Link

RT010001 - Routing Header (Primary Routing)
RT010130 - Routing Line

SC020030 - Sales Forecast Header
SC020130 - Sales Forecast Line Item Total
SC020230 - Sales Forecast Line Date breakdown

MF010030 - MPS Header
MF010130 - MPS Line Item Total
MF010230 - MPS Line Date Breakdown

MPSF1000 - MRP (Sales) Forecasting Selections

WO010032 - Manufacture Order Master

• IS010001 - MOP/SOP Link Cross reference table 
• SOITEMDUEDATE_I = In-House Due date
• SOITEMPROMISEDATE_I = Promise Date
• SOP10200.ReqShipDate = Requested Ship Date

MN010000 - MO Notes (BOM notes are connected to Item Engineering table)

PK010033 - Picklist Lines

IVIC0101 - Mfg Receipt / Inventory Adjustment Index
MOPW400 - Mfg Receipt Work
MOPW500 - Mfg Receipt Work

Make/Buy Codes

• 1-Make
• 2-Buy
• 3-Make/Buy

Fulfilment Methods

• 1-Make to stock
• 2-Make to Order-Manual
• 3-Make to Order-Silent

MO Status Codes

• 1-Quote
• 2-Open
• 3-Released
• 4-Hold
• 5-Canceled
• 6-Complete
• 7-Partially Received
• 8-Closed

Component Entry Trx Codes

• 1-Issue
• 2-Reverse Issue
• 3-Allocate
• 4-Reverse Allocate
• 5-Scrap
• 6-Reverse Scrap

MO Receipt Costs
Receiptcostsarray_1 - Total Item Costs
Receiptcostsarray_4 - Total Labor costs
Receiptcostsarray_7 - Total Machine Costs
receiptcostsarracy_10 - Total total costs

WO010213 - MOP Order History

MOP1210 - MOP Picklist Line Items
MOP1016 - MOP WIP Receipt
MOP1100 - MOP Receipt Master
MOP1110 - MOP Line Items

WR010130 - MO Routing Line

MRP0300 - MRP Exception List (Suggested MMO's, MPO's)

Full list of Manufacturing tables here
http://dyndeveloper.com/DynTable.aspx?ModuleID=MF

http://support.microsoft.com/kb/949762