How to watch movies on your iPad from your PC over your network for free

1) Curse Apple for not having this functionality by default.
2) Download and install Bonjour on your PC
3) Download /Install Boxee on iPad and BoxeeMediaManager on your PC


or buy AirVideo Free on iPad and AirVideo free server on your PC for $3

SQL 2008 Change "Edit top 200 rows" to Open Table or Edit All

In SQL Server Management Console

Tools > Options > SQL Server Object Explorer > Commands

Change the value in options to 0 for any of the commands and you can open the entire table when you select them from the right-click menu.

Run Batch File from SQL or SQL from Batch File

Batch File from SQL
------------------------------------------------------------------------------
Enable xp_cmdshell
Option is in surface area manager, or run script

EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE

Use the xp_cmdshell command
xp_cmdshell '"C:\dbs\IM.bat"'




SQL from batch file

Pre-Requisites:
SQL Management Studio must be installed on the machine you are trying to execute the batch file from.
OR
Install the command line utilities only if you do not want to install Management Studio.
http://www.microsoft.com/en-gb/download/details.aspx?id=36433
-------------------------------------------------------------------
sqlcmd -S localhost\dbinstance -U sa -P password -i "C:\Files\CreateDB.sql"

Dynamics GP - Remove ALL Session and Record Locks (Your previous transaction-level posting session has not finished process. Please allow time for it to finish.)

Also resolves issue:
ERROR: Your previous transaction-level posting session has not finished process. Please allow time for it to finish.
After running the script, check the dynamics..sy00500 table for the offending batch, and delete it.



Will clear out locks for anyone who is not logged in

delete from DYNAMICS..ACTIVITY where USERID not in (select loginame from master..sysprocesses)delete from tempdb..DEX_SESSION where session_id not in (select SQLSESID from DYNAMICS..ACTIVITY)
delete from tempdb..DEX_LOCK where session_id not in (select SQLSESID from DYNAMICS..ACTIVITY)
delete from DYNAMICS..SY00800 where USERID not in (select USERID from DYNAMICS..ACTIVITY)
delete from DYNAMICS..SY00801
----------------------------------------------------------------------------------------
--View locks with this

select * from DYNAMICS..ACTIVITY
select * from DYNAMICS..SY00800
select * from DYNAMICS..SY00801
select *  from tempdb..DEX_SESSION
select * from tempdb..DEX_LOCK


--------------------------------------------------------------------------------------
--This script will clear out ALL locks and disconnect all users.
--Do not use this if people are actually logged into the system doing work.


delete from DYNAMICS..ACTIVITY
delete from tempdb..DEX_SESSION
delete from tempdb..DEX_LOCK
delete from DYNAMICS..SY00800
delete from DYNAMICS..SY00801

Dynamics GP - SQL Trigger - Create a trigger to update RM00101 with last payment date from RM00103

USE [AW]
GO

/****** Object:  Trigger [LastPmtDate]    Script Date: 11/14/2011 14:59:33 ******/
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trg_LastPmtDate]'))
DROP TRIGGER [dbo].[trg_LastPmtDate]
GO

USE [AW]
GO

/****** Object:  Trigger [dbo].[LastPmtDate]    Script Date: 11/14/2011 14:59:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[trg_LastPmtDate]
   ON  [dbo].[RM00103]
   AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;

update RM00101 set RM00101.CCRDXPDT = inserted.LASTPYDT
from INSERTED
where RM00101.CUSTNMBR = inserted.custnmbr


END

GO

price group header not found for item class group

The company has not been correctly converted from Standard pricing to Extended pricing.

1) Go to Tools>Setup>Sales>Extended Pricing
2) Enable Standard pricing
3) Logout, Login
4) Enable Extended Pricing
5) Logout, Login

Dynamics GP Restore a copy of live data into a test company

1) Backup live
2) Create test company
3) Restore over test
4) Run this

/******************************************************************************/
/* Description: */
/* Updates any table that contains a company ID or database name value */
/* with the appropriate values as they are stored in the DYNAMICS.dbo.SY01500 table */
/* */
/******************************************************************************/

if not exists(select 1 from tempdb.dbo.sysobjects where name = '##updatedTables')
  create table [##updatedTables] ([tableName] char(100))
truncate table ##updatedTables

declare @cStatement varchar(255)

declare G_cursor CURSOR for
select
case
when UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID')
  then 'update '+a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '+ cast(b.CMPANYID as char(3))
else
  'update '+a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '''+ db_name()+''''
end
from INFORMATION_SCHEMA.COLUMNS a, DYNAMICS.dbo.SY01500 b, INFORMATION_SCHEMA.TABLES c
where UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID','INTERID','DB_NAME','DBNAME', 'COMPANYCODE_I')
  and b.INTERID = db_name() and a.TABLE_NAME = c.TABLE_NAME and c.TABLE_CATALOG = db_name() and c.TABLE_TYPE = 'BASE TABLE'

set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
   insert ##updatedTables select
substring(@cStatement,8,patindex('%set%',@cStatement)-9)
   Exec (@cStatement)
  FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE G_cursor

select [tableName] as 'Tables that were Updated' from ##updatedTables

5) Then run this

sp_changedbowner 'DYNSA'

--------------------------------------------------------------------------------

View
KB871973
for more information

SSAS How to calculate Variance, YTD, Prior Year in MDX

To use any of this code, go into your cube, click on Calculations Tab, then Script View, and just paste it in there. Or you can build it the long way through the form
 view.


SIMPLE VARIANCE
Example: Difference of Actual sales (A Value) from Budgeted Sales (B Value)
You run into problem with zero values on the Budget side if you're dividing by the budget to get your variance percentage.
--------------------------------------------------------------------------------

CREATE MEMBER CURRENTCUBE.[Measures].[B Var Value PC]
 AS case

when [Measures].[B Value] = 0
and [Measures].[A Value] > 0
then 1

when [Measures].[B Value] = 0
and [Measures].[A Value] < 0
then -1

when [Measures].[B Value] = 0
and [Measures].[A Value] = 0
then 0

else [Measures].[B Var Value]/[Measures].[B Value] end,
FORMAT_STRING = "Percent",
NON_EMPTY_BEHAVIOR = { [A Value] },
VISIBLE = 1;
--------------------------------------------------------------------------------

YTD CALCULATIONS
YTD Calculations require that your time periods are set up properly first.
Ensure that your Time dimension has a hierarchy for at least
Year>Month
You must also define your attribute relationships
Key>Month>Year

Go to Time dimension>Attribute Relationships>Right click Year Attribute>Properties
Ensure Type is set to Years
This defines the year level for this time hierarchy.

Now your YTD function will work without any errors
The YTD will represent all periods within the yr-month hierarchy up to the currentmember
The SUM will keep adding across each period to get a running total.

-----------------------------------------------------------------------------

CREATE MEMBER CURRENTCUBE.[Measures].[A YTD Case]
 AS sum(ytd([View B Time].[yr-month].currentmember),[Measures].[A Value]),
VISIBLE = 1;
-----------------------------------------------------------------------------

PRIOR YEAR YTD
To calculate a prior Year YTD uses almost the same formula.
Instead of using the currentmember, we have to use the PARALLELPERIOD of the currentmember one year ago.
PARALLELPERIOD(Year Set Containing your year numbers, 1 year ago, Month set containing the months.the currentmember year)

-------------------------------------------------------------------------------

CREATE MEMBER CURRENTCUBE.[Measures].[A PYYTD Case]
 AS sum(YTD(
PARALLELPERIOD(
[View B Time].[Yr-Month].[Year Num],
1,
[View B Time].[Yr-Month].currentmember
)
),
[Measures].[A Value]
),
VISIBLE = 1  ;
---------------------------------------------------------------------------------

MDX - Basics

Basic Syntax of an MDX Query


select
{
([View Time].[Year Num].&[2010]),
( [View Time].[Year Num].&[2011])
}
on columns,
([View B Items].[Item Brand].members)
on rows
from budget
where [Measures].[Sales]

Shows columns: 2010, 2011
Rows: All members of Item Brand
Values: Total Sales for each brand for each year

Dynamics GP Links and Resources

Developer Info
http://blogs.msdn.com/b/developingfordynamicsgp/archive/2008/07/15/developer-articles.aspx

GP 2013 Service Packs, Hotxies, Patches, Updates
https://mbs.microsoft.com/partnersource/downloads/servicepack/mdgp2013_patchreleases.htm?printpage=false&sid=2th1yrqua13zbfg4q2jwc2v2&stext=gp 2013 hotfix

Dynamics GP Report Writer - Barcodes

Under report definition, format options, untick text report

Create a formula field, use constant * cat strip(your field value) cat  *.
The * is required to identify the barcode beginning and end.
Strip is needed to remove the trailing blank spaces after your field value
Change the font type to your barcode font Tools>Drawing Options (Font name is 3 of 9 or Code 128)

Regular Code128 font will never work, it requires encoding through an encoder

How to Import Excel 2010 xlsx to SQL 2008 R2 64-bit

First of all:
You cannot connect a 32-bit office to a 64-bit SQL. Period.

You need:
64-bit OS
Windows Server 2008 64-bit
Office 2010 64-bit
2010 64-bit OLEDB Drivers
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d


Go to SQL Management Studio,
Log in as SA
 run this
------------------------------------------------------------
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO


USE [master]
GO

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
GO

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
GO

(Original Post)
------------------------------------------------------------

Save your excel file as C:\db\myfile.xlsx
Run this
-------------------------------------------------------------
SELECT * FROM OPENROWSET ( 'Microsoft.ACE.OLEDB.12.0',
'Excel 12.0; Database=C:\db\myfile.xlsx;HDR=Yes', 'Select * from [sheet1$]' )
or
'Excel 12.0; Database=C:\db\myfile.xlsx;HDR=Yes', 'Select * from [sheet1 - table 1$]' )
--------------------------------------------------------------

For 32-Bit Excel, and SQL use
--------------------------------------------------------------
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;HDR=YES;Database=C:\temp\test.xls',
    'select * from [sheet1$]')

Database: Path and name of your excel file
HDR: Yes/No if to include headers or not
Sheet1$: Replace with the sheet name you want to access in your excel file (leave the $ on the end)

You should be able to access your excel file.

Dynamics GP SSRS: The deployment has exceeded the maximum request length allowed by the target server.

Go to 

c:\Program Files\Microsoft SQL Server\(SRS Instance)\Reporting Services\ReportServer\web.config 

Find

<httpRuntime executionTimeout="9000" />

Change it to (<httpRuntime executionTimeout="9000" maxRequestLength="20960"/>).

Related Error:
"an error occurred when invoking the authorization extension"

You will get this error when attempting to connect to your SSRS if you have mistyped, or badly edited your web.config file.
Review the line and ensure it is exactly

<httpRuntime executionTimeout="9000" maxRequestLength="40960"/>

SQL E-mail Setup - Not sending e-mail

The service account that sql is running under must have access to the exchange server

This same service should be used to run all sql services

Dynamics GP Management Reporter SQL Account Security

Security settings for new SQL accounts to have access to Management Reporter.

Must create Dynamics GP user, and then use that user credentials to access Mgt Reporter Designer

SSRS Web Config File Location

Where is the SSRS Web.config file?

SSRS Web config file location

C:\Program Files\Microsoft SQL Server\MSRS10_50.SQL2008R2\Reporting Services\ReportServer

or for SQL2012

C:\Program Files\Microsoft SQL Server\MSRS11.SQL2012\Reporting Services\ReportServer


Open the web.config file
Find
<httpRuntime executionTimeout="9000">


Replace it with
<httpRuntime executionTimeout="9000" maxRequestLength="40960"/>


Restart ssrs

How to Install GP2010R2

1) Run Install
2) Choose New Instance
3) After install is complete, run GP Utilities
4) Allow utilities to upgrade all Databases
5) Run install again to install Business analyzer and other addons

Render SSRS Report from URL

Here is a sample of the URL required to render the Trial Balance Detail sample report for Dynamics GP in Reporting Services

http://servername/ReportServer?/two/financial/trialbalancedetail&rs:Command=Render&rs:Format=PDF&I_tHistoryYear=0&I_iYear=2017&SortBy=ACTNUMBR_1

Installing Management Reporter for GP 2010

Confirm whether or not the Application Service is running correctly:
http://MRservername:MRport/SecurityService.svc
If you get any response, the service is running.

When installing the Management Reporter Server, ensure you use a specific account to run the service. DO NOT USE NETWORK SERVICE.
Whatever account you use to run the service MUST HAVE ACCESS TO THE SQL DATABASES: ManagementReporter, Dynamics, all CompanyDB's

Most of these issues can be solved if you use a specific domain account to run SQL. Use the same account to run MR.

Installing SRS Reports for GP10

Error: retrieving the com class factory for component with clsid


http://community.dynamics.com/product/gp/f/32/p/33662/95991.aspx

Need to download 64-bit components for GP, then run GP SRS Wizard

How to Deploy SSRS Reports for Dynamics GP 2010 +

Before beginning, make sure you have entered the registration keys for your Dynamics GP. If you do not, the modules will not be detected, and no reports will be deployed.

1) Run the setup from the Dynamics GP installation disk
2) Install SQL Server Reporting Services Wizard
3) Find the wizard in your Program Files, run the wizard
4) Enter server, username, and password
5) Select company, and all reports
6) Enter report server URL http:\\localhost\reportserver
7) Click Finish

Right click on GP and click run as>administrator
Log in as sa
1) Go to Tools>Setup>System>Reporting Tools setup
2) Enter Report Server URL http://localhost/ReportServer/reportservice2005.asmx
(For 2013+ the address is   http://localhost/ReportServer/)
3) Report Manager URL http://localhost/Reports/Pages/Folder.aspx
(For 2013+ the address is   http://localhost/Reports/)

(replace localhost with your report server name)

4) Click OK

And you're done.
SRS Reports should now be deployed to your SRS manager, and they should be visible within GP's report lists.

Also check out
http://support.microsoft.com/kb/954242
It has a list of common issues and solutions


Related errors:
You continue to get the "the sql server reporting services information is not valid" even though you are using the correct address.

• Right click on GP and click run as>administrator

Go to 

c:\Program Files\Microsoft SQL Server\(SRS Instance)\Reporting Services\ReportServer\web.config 

Find

<httpRuntime executionTimeout="9000" />

Change it to (<httpRuntime executionTimeout="9000" maxRequestLength="20960"/>).

T-SQL How to combine multiple rows into a single row over a group

The problem:
My Data looks like this.

But i want it to look like this:

How do i do it?

Like this...

SELECT TOP 100 Percent p1.grp,

          ( SELECT comment + ',' 

              FROM combine p2

             WHERE p2.grp = p1.grp

             ORDER BY comment

               FOR XML PATH('') , TYPE).value('.[1]', 'nvarchar(max)' ) AS AllComm

      FROM combine p1

     GROUP BY grp ;

Original source and other techniques found here

http://www.projectdmx.com/tsql/rowconcatenate.aspx

Dynamics GP Manufacturing - MRP, Suggested MO and Sub Assembly

Dynamics GP CAN suggest MO's for all Sub-Assemblies, as well as PO's for all raw materials required. However, it will not do them all at the same time, and can only suggest one level at a time.

1) Go to Cards>Item>Item Maintenance
Click on the flyout in the top right hand corner, go to Item Engineering Data
Tick the "Calculate MRP" box

2) Ensure that the following options are configured for each item/site combination.

In the Cards>Inventory> Item Resource Planning Maintenance Window

FOR EACH SITE that uses the item,

(including work centers if your sub-assemblies are moving through work centers)

ensure the following

• "Calculate MRP for this Item/Site" is ticked
• Order Policy is set to Lot for Lot
• Replenishment Method is Make or Buy

If you need to do this for many items, you can use the 

Cards>Inventory> Site Resource Planning Maintenance Window instead.

Simply choose your site and set the options and click save to update all items within that site.

3) Create your first MO of your final product you want to kick off the sub-assembly MRP suggestions
You should use Backward Infinite for the scheduling since you are starting at the final product, and all sub-assemblies should occur before assembly of your final product.

4) Run your MRP regeneration to calculate the Suggested MO's.

Ensure that the time span you have selected includes the due date of the MO.

4) To view the Suggested MO's go to

MRP Quantities Query, hit refresh

or MRP Pegging Inquiry, hit refresh

5) Double click on your Suggested MO and transfer it to a real MO.

The MRP will only suggest MO's for a single level down into the Subassemblies.

eg.

A requires B requires C

MO for A, then run MRP = Suggested MO for B ONLY

Transfer Suggested MO for B to MO, the run MRP again = Suggested MO for C ONLY

Dynamics GP - Weekly / Monthly Maintenance Procedures

Maintenance should be run on a Weekly / Monthly Basis, or as soon as possible after a power failure, or loss of connection to the server for any reason.

1.       Log into SQL Server Management Studio. Open Query Analyzer

a.     These only need to be done in the event of a power failure, or errors concerning damaged tables.

b.    DBCC CHECKDB (‘MASTER’)

c.     DBCC CHECKDB (‘MODEL’)

d.    DBCC CHECKDB (‘DYNAMICS’)

e.     DBCC CHECKDB (‘<database name>’)

f.      Run each line separately to perform maintenance on the tables of each company to fix any minor structural errors in the database

g.    Repeat for each database

DO NOT run this script if users are doing transactions or posting in ANY company

2.       To ensure all users are out of the system, you can run the following script from the SQL Server Management Studio->Query Analyzer before logging in

1.    DELETE FROM [DYNAMICS].[dbo].[ACTIVITY]

2.    DELETE FROM [DYNAMICS].[dbo].[SY00800]

3.    DELETE FROM [DYNAMICS].[dbo].[SY00801]

4.    DELETE FROM [tempdb].[dbo].[DEX_LOCK]

5.    DELETE FROM [tempdb].[dbo].[DEX_SESSION]

a.     Run this script if you experience errors concerning locked records, or batches or records in use, or errors saying other users are logged in

b.    DO NOT run this script if users are doing transactions or posting in ANY company

3.       Before running any maintenance, ensure all users are logged out, and you are logged in as SA

a.     Check by going to Tools->Utilities->System->User Activity

b.    Ensure that sa is the only user present

4.       GP->Maintenance->Sql

a.     This process optimizes the databases to improve database performance.

b.    Company may become sluggish or slow if not run regularly

c.     Select Company

d.    Tick Recompile and Update Statistics

e.     Click Process

f.      Repeat for each active company including dynamics

g.    Usually only takes a few minutes

5.       GP->Maintenance->Check Links

a.     Select Series

b.    Click “All” to add all categories

c.     Repeat this for all series

d.    Most important series are Financial, Sales, Purchasing, Inventory, Company, System

e.     Click “OK”

f.      This process can take a long time, you may want to leave it overnight as long as it doesn’t clash with the backup schedule.

g.    Print and review all reports to understand the impact of the check links

6.       GP->Tools->Utilities->Sales->Reconcile

a.     You may select other options or leave the defaults to do all records

b.    Will fix minor errors in SOP

7.       GP->Tools->Utilities->Purchasing->Reconcile

a.     You may select other options or leave the defaults to do all records

8.       GP->Tools->Utilities->Inventory->Reconcile

a.     You may select other options or leave the defaults to do all records

b.    Will fix errors with allocated inventory

9.       GP->Tools->Utilities->Financial->Reconcile

a.     Select  options. Year is easiest.

10.   Monthly / Weekly Smartlists

a.     It is a good practice to keep excel exports of customer , vendor and inventory balances, as well as open transactions as reference on a monthly basis

11.   Monthly / Weekly Reports

a.     List all reports that need to be printed and archived in excel or pdf on a fixed schedule.

b.    Name of person/position who is responsible for running the report

c.     Name of person/position it should be delivered to

12.   Physical Backup

a.     A Backup Schedule should be set up in SQL to do a full backup of all databases as often as required

b.    Daily backups with a two week rotation meet most company’s needs

c.     To set up a backup schedule, connect to:

i.                SQL Server Management Studio

ii.              Expand Management

iii.            Right Click Maintenance Plans

iv.            Start Maintenance Plan Wizard

1.       Choose backup or maintenance tasks

2.       Choose which databases they will be performed on

3.       Schedule those tasks

d.    File backups  should be done on all shared folders containing all dictionary files, and any customizations or additional reports

e.     File backups should also be done on the Program Files>Microsoft Dynamics GP folders on the server and on each user’s workstation

i.                Backup of the Program Files>Microsoft Dynamics GP folders on each user’s workstation is only necessary if users have customizations specific to their workstation

f.      Ensure a physical backup is completed after maintenance

g.    Restore this backup to your test company to ensure it is working

h.    Log into SQL Server Management Studio

ii.              Right click on TEST database

iii.            Tasks>Restore>Database

iv.            Tick from device, choose your backup file to restore

v.              Tick Restore on the file

vi.            Click options on the left bar under “Select a Page”

vii.          Tick “overwrite the existing database”

viii.        Under the “Restore As” paths, ensure that they are pointing to the correct path and filenames of the database files you wish to overwrite (Eg. If you are restoring LIVE into TEST, ensure the path and filenames for pointing to TESTDAT.mdf and TESTLOG.ldf)

13.   Business Intelligence Backups

a.     Export copies of all customized reports in SSRS as rdl files

b.    Backup Analysis Cubes

c.     Backup any other customized reports done in any third party software

14.   Standard server maintenance

a.     Scan disk

b.    Defragment

c.     Diagnostics

Dynamics GP View - Payables-Receiving Distribution Detail

The following view will produce a list of all Check Payments, the Invoices they were applied to, the Receivings Transactions associated with the invoices, the distributions on the receivings transactions, and the line item distribution breakdown of the total distribution.


CREATE VIEW Pay_Rcv_Dist_Dtl
as

 SELECT DISTINCT *  
 FROM
 (SELECT * FROM
(SELECT VENDORID,
     CASE DocTYPE
         WHEN 6 THEN 'Payment'
END Doc_Type, DOCTYPE,

DOCDATE PMTDATE, VCHRNMBR PMTVCHR, DOCNUMBR CHQNUMBR, DOCAMNT PMTAMT,
          VOIDED, TRXSORCE PMTSORCE, CHEKBKID, PSTGDATE PMTPSTGDATE, PYENTTYP, CARDNAME, CURNCYID
      FROM PM30200
      UNION
      SELECT VENDORID,
      CASE DocTYPE
         WHEN 6 THEN 'Payment'
END Doc_Type, DOCTYPE,
      DOCDATE, VCHRNMBR, DOCNUMBR, DOCAMNT,
          VOIDED, TRXSORCE, CHEKBKID, PSTGDATE, PYENTTYP, CARDNAME, CURNCYID
      FROM PM20000) P

INNER JOIN
     (SELECT VENDORID V1, VENDNAME FROM PM00200) V
     ON P.VENDORID = V.V1

LEFT OUTER JOIN
     (SELECT VENDORID V2, VCHRNMBR VCH1, DOCTYPE DOC1, APTVCHNM, APTODCTY,
          APTODCNM INVNUMBR, APTODCDT INVDATE, ApplyToGLPostDate, APPLDAMT
      FROM PM10200
      UNION
      SELECT VENDORID, VCHRNMBR, DOCTYPE, APTVCHNM, APTODCTY,
          APTODCNM, APTODCDT, ApplyToGLPostDate, APPLDAMT
      FROM PM30300) PA
     ON P.PMTVCHR = PA.VCH1
     AND P.VENDORID = PA.V2
     AND P.DOCTYPE = PA.DOC1
     WHERE (P.DOCTYPE = 6)
     ) PMT
    
 LEFT OUTER JOIN
 --INVOICES
 (SELECT VCHRNMBR INVVCH, DOCAMNT INVAMT FROM
(SELECT VENDORID,
      CASE DOCTYPE
         WHEN 1 THEN 'Invoice'
         WHEN 2 THEN 'Finance Charge'
         WHEN 3 THEN 'Misc Charge'
 END INVTYPE,
DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR, DOCAMNT,
          VOIDED, TRXSORCE, CHEKBKID, PSTGDATE, PYENTTYP, CARDNAME
      FROM PM30200
      UNION
      SELECT VENDORID,
      CASE DOCTYPE
         WHEN 1 THEN 'Invoice'
         WHEN 2 THEN 'Finance Charge'
         WHEN 3 THEN 'Misc Charge'
 END INVTYPE,
      DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR, DOCAMNT,
          VOIDED, TRXSORCE, CHEKBKID, PSTGDATE, PYENTTYP, CARDNAME
      FROM PM20000) P
     WHERE (P.DOCTYPE in (1,2,3))
     ) INV
    
     ON INV.INVVCH = PMT.APTVCHNM

--RECEIVINGS HDR
    
LEFT OUTER JOIN
     (SELECT POPRCTNM, VNDDOCNM, VCHRNMBR RCVVCH
     FROM POP30300
     ) RCVHDR
     ON INV.INVVCH = RCVHDR.RCVVCH

--RECEIVINGS DOCUMENT DISTRIBUTIONS
LEFT OUTER  JOIN
     (SELECT POPRCTNM RCTDIST, CRDTAMNT, DEBITAMT, A.ACTINDX, ACTNUMST, ACTNUMBR_1, ACTNUMBR_2,ACTNUMBR_3,ACTNUMBR_4,ACTDESCR,
     CASE DISTTYPE
         WHEN 1 THEN 'Cash'
         WHEN 2 THEN 'Payable'
         WHEN 3 THEN 'Discount Available'
         WHEN 4 THEN 'Discount Taken'
         WHEN 5 THEN 'Finance Charge'
         WHEN 6 THEN 'Purchase'
         WHEN 7 THEN 'Trade Disc.'
         WHEN 8 THEN 'Misc. Charge'
         WHEN 9 THEN 'Freight'
         WHEN 10 THEN 'Taxes'
         WHEN 11 THEN 'Writeoffs'
         WHEN 12 THEN 'Other'
         WHEN 13 THEN 'GST Disc'
         WHEN 14 THEN 'PPS Amount'
         WHEN 16 THEN 'Round'
         WHEN 17 THEN 'Realized Gain'
         WHEN 18 THEN 'Realized Loss'
         WHEN 19 THEN 'Due To'
         WHEN 20 THEN 'Due From'
        
         ELSE ''
         END Distribution_Type, DISTTYPE
     FROM POP30390 A
     INNER JOIN (SELECT ACTINDX, ACTNUMBR_1, ACTNUMBR_2,ACTNUMBR_3,ACTNUMBR_4, ACTDESCR FROM GL00100) GL ON  A.ACTINDX = GL.ACTINDX
INNER JOIN (SELECT ACTINDX, ACTNUMST FROM GL00105) GL2 ON  GL.ACTINDX  = GL2.ACTINDX
     ) RCVDIST
     ON RCVDIST.RCTDIST = RCVHDR.POPRCTNM

--RECEIVINGS LINE ITEM
    
LEFT OUTER JOIN
     (SELECT POPRCTNM RCTLINE, PONUMBER, ITEMNMBR, EXTDCOST, ITEMDESC, INVINDX
     FROM POP30310
     ) RCVLN
     ON RCVHDR.POPRCTNM  = RCVLN.RCTLINE
     AND RCVLN.INVINDX = RCVDIST.ACTINDX

WHERE VOIDED = 0 and DISTTYPE < 21