Project Accounting View

November 10 | By Tracey Brinkman

Tags: gp | PA | Project Accounting

By Ryan McBee

 

IF EXISTS (SELECT type,name FROM sysobjects

WHERE name = ‘PACosts’ AND type = ‘V’)

DROP VIEW PACosts

GO

——————————————————————–

CREATE VIEW PACosts AS

 

SELECT  ‘Timesheet’ AS DocumentType, ‘ ‘ as VendorInvoice, dbo.PA10001.PAPROJNUMBER AS ProjectNumber, LTRIM(RTRIM(dbo.PA10000.PATSNO)) + ‘-‘ + CONVERT(VARCHAR(10), dbo.PA10001.LNITMSEQ) AS DocumentNumber,

dbo.PA10001.PACOSTCATID AS CostCategory, ‘LABOR’ AS Item, ‘Labor’ as ItemDescription, dbo.PA10000.PAPD AS PostDate, dbo.PA10001.PAQtyQ AS Quantity, dbo.PA10001.PAUnit_of_Measure AS UofM,

dbo.PA10001.PATOTCST / dbo.PA10001.PAQtyQ AS UnitCost, 0 as Tax, dbo.PA10001.PATOTCST AS ExtendedCost, dbo.PA10000.EMPLOYID AS VendorID, ” AS TransactionDescription, ‘W’ AS TransactionTable, ” AS PONum,

dbo.PA10001.PAORIGTOTCOST AS OREXTCST

FROM    dbo.PA10000 INNER JOIN

dbo.PA10001 ON dbo.PA10000.PATSNO = dbo.PA10001.PATSNO

 

UNION ALL

 

SELECT  ‘Timesheet’ AS DocumentType, ‘ ‘ as VendorInvoice, dbo.PA30101.PAPROJNUMBER AS ProjectNumber, LTRIM(RTRIM(dbo.PA30100.PATSNO)) + ‘-‘ + CONVERT(VARCHAR(10), dbo.PA30101.LNITMSEQ) AS DocumentNumber,

dbo.PA30101.PACOSTCATID AS CostCategory, ‘LABOR’ AS Item, ‘Labor’ as ItemDescription, dbo.PA30100.PAPD AS PostDate, dbo.PA30101.PAQtyQ AS Quantity, dbo.PA30101.PAUnit_of_Measure AS UofM,

dbo.PA30101.PATOTCST / dbo.PA30101.PAQtyQ AS UnitCost, 0 as Tax, dbo.PA30101.PATOTCST AS ExtendedCost, dbo.PA30100.EMPLOYID AS VendorID, ” AS Expr1, ‘P’ AS TransactionTable, ” AS PONum,

dbo.PA30101.PAORIGTOTCOST AS OREXTCST

FROM    dbo.PA30100 INNER JOIN

dbo.PA30101 ON dbo.PA30100.PATSNO = dbo.PA30101.PATSNO

 

UNION ALL

 

SELECT  ‘Equipment’ AS DocumentType, ‘ ‘ as VendorInvoice, dbo.PA10101.PAPROJNUMBER AS ProjectNumber, dbo.PA10100.PAEQLOGNO AS DocumentNumber, dbo.PA10101.PACOSTCATID AS CostCategory, dbo.PA10101.PAEQUIPTID AS Item,

‘ ‘ as ItemDescription,  dbo.PA10100.PAPD AS PostDate, dbo.PA10101.PAQtyQ AS Quantity, dbo.PA10101.PAUnit_of_Measure AS UofM, dbo.PA10101.PAUNITCOST AS UnitCost, 0 as Tax, dbo.PA10101.PAEXTCOST AS ExtendedCost, ” AS VendorID,

dbo.PA10100.PAREFNO, ‘W’ AS TransactionTable, ” AS PONum, dbo.PA10101.OREXTCST

FROM    dbo.PA10100 INNER JOIN

dbo.PA10101 ON dbo.PA10100.PAEQLOGNO = dbo.PA10101.PAEQLOGNO

 

UNION ALL

 

SELECT  ‘Equipment’ AS DocumentType, ‘ ‘ as VendorInvoice, dbo.PA30201.PAPROJNUMBER AS ProjectNumber, dbo.PA30200.PAEQLOGNO AS DocumentNumber, dbo.PA30201.PACOSTCATID AS CostCategory, dbo.PA30201.PAEQUIPTID AS Item,

‘ ‘ as ItemDescription, dbo.PA30200.PAPD AS PostDate, dbo.PA30201.PAQtyQ AS Quantity, dbo.PA30201.PAUnit_of_Measure AS UofM, dbo.PA30201.PAUNITCOST AS UnitCost, 0 as Tax, dbo.PA30201.PAEXTCOST AS ExtendedCost, ” AS VendorID,

dbo.PA30200.PAREFNO, ‘P’ AS TransactionTable, ” AS PONum, dbo.PA30201.OREXTCST

FROM    dbo.PA30200 INNER JOIN

dbo.PA30201 ON dbo.PA30200.PAEQLOGNO = dbo.PA30201.PAEQLOGNO

 

UNION ALL

 

SELECT  ‘Misc’ AS DocumentType, ‘ ‘ as VendorInvoice, dbo.PA10201.PAPROJNUMBER AS ProjectNumber, dbo.PA10200.PAMISCLDOCNO AS DocumentNumber, dbo.PA10201.PACOSTCATID AS CostCategory, dbo.PA10201.PSMISCID AS Item,

‘ ‘ as ItemDescription, dbo.PA10200.PAPD AS PostDate, dbo.PA10201.PAQtyQ AS Quantity, dbo.PA10201.PAUnit_of_Measure AS UofM, dbo.PA10201.PAUNITCOST AS UnitCost, 0 as Tax, dbo.PA10201.PAEXTCOST AS ExtendedCost, ” AS VendorID,

dbo.PA10200.PAREFNO, ‘W’ AS TransactionTable, ” AS PONum,
dbo.PA10201.OREXTCST

FROM    dbo.PA10200 INNER JOIN

dbo.PA10201 ON dbo.PA10200.PAMISCLDOCNO = dbo.PA10201.PAMISCLDOCNO

WHERE dbo.PA10201.PAPROJNUMBER <> ”

 

UNION ALL

 

SELECT  ‘Misc’ AS DocumentType, ‘ ‘ as VendorInvoice, dbo.PA30301.PAPROJNUMBER AS ProjectNumber, dbo.PA30300.PAMISCLDOCNO AS DocumentNumber, dbo.PA30301.PACOSTCATID AS CostCategory, dbo.PA30301.PSMISCID AS Item,

‘ ‘ as ItemDescription, dbo.PA30300.PAPD AS PostDate, dbo.PA30301.PAQtyQ AS Quantity, dbo.PA30301.PAUnit_of_Measure AS UofM, dbo.PA30301.PAUNITCOST AS UnitCost, dbo.PA30301.PABilledTax as Tax, dbo.PA30301.PAEXTCOST AS ExtendedCost, ” AS VendorID,

dbo.PA30300.PAREFNO, ‘P’ AS TransactionTable, ” AS PONum, dbo.PA30301.OREXTCST

FROM    dbo.PA30300 INNER JOIN

dbo.PA30301 ON dbo.PA30300.PAMISCLDOCNO = dbo.PA30301.PAMISCLDOCNO

WHERE dbo.PA30301.PAPROJNUMBER <> ”

 

UNION ALL

 

SELECT  ‘Employee Expense’ AS DocumentType, ‘ ‘ as VendorInvoice, dbo.PA10501.PAPROJNUMBER AS ProjectNumber, dbo.PA10500.PAerdocnumber AS DocumentNumber, dbo.PA10501.PACOSTCATID AS CostCategory, dbo.PA10501.PAitemnumber AS Item,

‘ ‘ as ItemDescription, dbo.PA10500.PAPD AS PostDate, dbo.PA10501.PAQtyQ AS Quantity, dbo.PA10501.PAUnit_of_Measure AS UofM, dbo.PA10501.PAUNITCOST AS UnitCost, 0 as Tax, dbo.PA10501.PAEXTCOST – dbo.PA10501.BCKTXAMT AS ExtendedCost,

dbo.PA10501.EMPLOYID AS VendorID, dbo.PA10500.PACOMM, ‘W’ AS TransactionTable, ” AS PONum, dbo.PA10501.OREXTCST – dbo.PA10501.OBTAXAMT AS Expr1

FROM    dbo.PA10500 INNER JOIN

dbo.PA10501 ON dbo.PA10500.PAerdocnumber = dbo.PA10501.PAerdocnumber

 

UNION ALL

 

SELECT  ‘Employee Expense’ AS DocumentType, ‘ ‘ as VendorInvoice, dbo.PA30501.PAPROJNUMBER AS ProjectNumber, dbo.PA30500.PAerdocnumber AS DocumentNumber, dbo.PA30501.PACOSTCATID AS CostCategory, dbo.PA30501.PAitemnumber AS Item,

‘ ‘ as ItemDescription, dbo.PA30500.PAPD AS PostDate, dbo.PA30501.PAQtyQ AS Quantity, dbo.PA30501.PAUnit_of_Measure AS UofM, dbo.PA30501.PAUNITCOST AS UnitCost, 0 as Tax, dbo.PA30501.PAEXTCOST – dbo.PA30501.BCKTXAMT AS ExtendedCost,

dbo.PA30501.EMPLOYID AS VendorID, dbo.PA30500.PACOMM, ‘P’ AS TransactionTable, ” AS PONum, dbo.PA30501.OREXTCST – dbo.PA30501.OBTAXAMT AS OREXTCST

FROM    dbo.PA30500 INNER JOIN

dbo.PA30501 ON dbo.PA30500.PAerdocnumber = dbo.PA30501.PAerdocnumber

 

UNION ALL

 

SELECT  ‘IV Transfers’ AS DocumentType, ‘ ‘ as VendorInvoice, dbo.PA10901.PAPROJNUMBER AS ProjectNumber, dbo.PA10900.PAIV_Document_No AS DocumentNumber, dbo.PA10901.PACOSTCATID AS CostCategory, dbo.PA10901.ITEMNMBR AS Item,

‘ ‘ as ItemDescription, dbo.PA10900.PAPD AS PostDate, CASE pa10900.paiv_transfer_type WHEN 1 THEN pa10901.PAbase_qty WHEN 2 THEN –

1 * pa10901.PAbase_qty WHEN 3 THEN – 1 * pa10901.PAbase_qty END AS Quantity,

dbo.PA10901.UOFM AS UofM, dbo.PA10901.PAUNITCOST AS UnitCost, 0 as Tax,

CASE pa10900.paiv_transfer_type WHEN 1 THEN pa10901.paEXTCOST WHEN 2 THEN – 1 * pa10901.paEXTCOST WHEN 3 THEN – 1 * pa10901.paEXTCOST END AS ExtendedCost, dbo.PA10900.VENDORID AS VendorID,

 

dbo.PA10900.PACOMM, ‘W’ AS TransactionTable, ” AS PONum,

CASE pa10900.paiv_transfer_type WHEN 1 THEN pa10901.paEXTCOST WHEN 2 THEN – 1 * pa10901.paEXTCOST WHEN 3 THEN – 1 * pa10901.paEXTCOST END AS OREXTCST

FROM    dbo.PA10900 INNER JOIN

dbo.PA10901 ON dbo.PA10900.PAIV_Document_No = dbo.PA10901.PAIV_Document_No

 

UNION ALL

 

SELECT  ‘IV Transfers’ AS DocumentType, ‘ ‘ as VendorInvoice, dbo.PA30901.PAPROJNUMBER AS ProjectNumber, dbo.PA30900.PAIV_Document_No AS DocumentNumber, dbo.PA30901.PACOSTCATID AS CostCategory, dbo.PA30901.ITEMNMBR AS Item,

‘ ‘ as ItemDescription, dbo.PA30900.PAPD AS PostDate, CASE pa30900.paiv_transfer_type WHEN 1 THEN pa30901.PAbase_qty WHEN 2 THEN –

1 * pa30901.PAbase_qty WHEN 3 THEN – 1 * pa30901.PAbase_qty END AS Quantity,

dbo.PA30901.PAUnit_of_Measure AS UofM, dbo.PA30901.PAUNITCOST AS UnitCost, dbo.PA30901.TAXAMNT as Tax,

CASE pa30900.paiv_transfer_type WHEN 1 THEN pa30901.paEXTCOST WHEN 2 THEN – 1 * pa30901.paEXTCOST WHEN 3 THEN – 1 * pa30901.paEXTCOST END AS ExtendedCost, dbo.PA30900.VENDORID AS VendorID,

 

dbo.PA30900.PACOMM, ‘P’ AS TransactionTable, ” AS PONum,

CASE pa30900.paiv_transfer_type WHEN 1 THEN pa30901.paEXTCOST WHEN 2 THEN – 1 * pa30901.paEXTCOST WHEN 3 THEN – 1 * pa30901.paEXTCOST END AS OREXTCST

FROM    dbo.PA30900 INNER JOIN

dbo.PA30901 ON dbo.PA30900.PAIV_Document_No = dbo.PA30901.PAIV_Document_No

 

UNION ALL

 

SELECT  CASE dbo.POP30300.POPTYPE WHEN 1 THEN ‘SHIPMENT’ WHEN 2 THEN ‘INVOICE’ WHEN 3 THEN ‘SHIPMENT/INVOICE’ WHEN 4 THEN ‘RETURN’ WHEN 5 THEN ‘RETURN’ ELSE ‘** CORRUPT RCD?’ END AS DocumentType,

dbo.POP30300.VNDDOCNM as VendorInvoice, dbo.POP30310.ProjNum AS ProjectNumber, dbo.POP30300.POPRCTNM AS DocumentNumber, dbo.POP30310.CostCatID AS CostCategory, dbo.POP30310.ITEMNMBR AS Item,

dbo.POP30310.ITEMDESC as ItemDescription, dbo.POP30300.GLPOSTDT AS PostDate, CASE (POP30300.POPTYPE) WHEN 4 THEN POP10500.QTYRESERVED WHEN 5 THEN POP10500.QTYRESERVED ELSE (POP10500.QTYSHPPD +

POP10500.QTYINVCD) END AS Quantity, dbo.POP10500.UOFM,

dbo.POP30310.UNITCOST, dbo.POP30310.TAXAMNT as Tax, (CASE POP30300.POPTYPE WHEN 2 THEN pa31102.PAShipmentExtCost WHEN 4 THEN ABS(pa31102.PAShipmentExtCost) * –

1 WHEN 5 THEN ABS(pa31102.PAShipmentExtCost)

* – 1 ELSE pa31102.PAShipmentExtCost END) + (CASE

(SELECT  TOP 1 paincprchtxprjcst

FROM    PA41701) WHEN 1 THEN POP30310.taxamnt ELSE 0

END) – dbo.POP30300.OBTAXAMT AS ExtendedCost, dbo.POP30300.VENDORID, dbo.PA31101.PACOMM, ‘P’ AS TransactionTable,

dbo.POP30310.PONUMBER AS PONum, (CASE POP30300.POPTYPE WHEN

2 THEN pa31102.PAOrigSEC WHEN 4 THEN ABS(pa31102.PAOrigSEC) * – 1 WHEN 5 THEN ABS(pa31102.PAOrigSEC)

* – 1 ELSE pa31102.PAOrigSEC END) + (CASE

(SELECT TOP 1 paincprchtxprjcst

FROM    PA41701) WHEN 1 THEN POP30310.ortaxamt ELSE 0

END) – dbo.POP30300.OBTAXAMT AS OREXTCST

FROM    dbo.PA31101 RIGHT OUTER JOIN

dbo.PA31102 RIGHT OUTER JOIN

dbo.POP30310 ON dbo.PA31102.PAVIDN = dbo.POP30310.POPRCTNM AND dbo.PA31102.RCPTLNNM = dbo.POP30310.RCPTLNNM LEFT OUTER JOIN

dbo.POP10500 ON dbo.POP30310.POPRCTNM = dbo.POP10500.POPRCTNM AND dbo.POP30310.RCPTLNNM = dbo.POP10500.RCPTLNNM RIGHT OUTER JOIN

dbo.POP30300 ON dbo.POP30310.POPRCTNM = dbo.POP30300.POPRCTNM ON dbo.PA31101.PAVIDN = dbo.POP30300.POPRCTNM WHERE  (dbo.POP30300.VOIDSTTS <> 1)

and dbo.POP30310.ProjNum <> ‘ ‘

 

UNION ALL

 

SELECT  CASE POP10300.POPTYPE WHEN 1 THEN ‘SHIPMENT’ WHEN 2 THEN ‘INVOICE’ WHEN 3 THEN ‘SHIPMENT/INVOICE’ ELSE ‘** CORRUPT RCD?’ END AS DocumentType, dbo.POP10300.VNDDOCNM as VendorInvoice,

dbo.POP10310.ProjNum AS ProjectNumber, dbo.POP10300.POPRCTNM AS DocumentNumber, dbo.POP10310.CostCatID AS CostCategory, dbo.POP10310.ITEMNMBR AS Item, dbo.POP10310.ITEMDESC as ItemDescription, dbo.POP10300.CREATDDT AS PostDate,

POP10500_1.QTYSHPPD + POP10500_1.QTYINVCD AS Quantity, POP10500_1.UOFM, dbo.POP10310.UNITCOST, dbo.POP10310.TAXAMNT, (CASE POP10300.POPTYPE WHEN 2 THEN 0 ELSE POP10310.EXTDCOST END) + (CASE

(SELECT  TOP 1 paincprchtxprjcst

FROM    PA41701) WHEN 1 THEN POP10310.taxamnt ELSE 0

END) AS ExtendedCost, dbo.POP10300.VENDORID, dbo.PA10701.PACOMM, ‘W’ AS TransactionTable, dbo.POP10310.PONUMBER AS PONum,

(CASE POP10300.POPTYPE WHEN 2 THEN 0 ELSE POP10310.OREXTCST

END) + (CASE

(SELECT  TOP 1 paincprchtxprjcst

FROM    PA41701) WHEN 1 THEN POP10310.ortaxamt ELSE 0

END) AS OREXTCST

FROM    dbo.PA10701 RIGHT OUTER JOIN

dbo.POP10300 LEFT OUTER JOIN

dbo.POP10310 LEFT OUTER JOIN

dbo.POP10500 AS POP10500_1 ON dbo.POP10310.POPRCTNM = POP10500_1.POPRCTNM AND dbo.POP10310.RCPTLNNM = POP10500_1.RCPTLNNM ON dbo.POP10300.POPRCTNM = dbo.POP10310.POPRCTNM ON

dbo.PA10701.PAVIDN = dbo.POP10300.POPRCTNM

WHERE dbo.POP10310.ProjNum <> ‘ ‘

 

GO

——————————————————————–

GRANT SELECT ON PACosts TO DYNGRP
This view successfully works on version 10 and 2010 for Dynamics GP.  If you get stumped or have any questions regarding your Dynamics GP software, shoot me an email. I’d be happy to help.