Dynamics GP
Dynamics GP
Organizer:
- Organized by
-
-
Reply To: GL account transaction with Project accounting ‘project’ linked
-
Hi Deanne:
Here is a script that I use to report on everything project. The piece to report on the link between project and Purchase Invoices is towards the bottom, but here is that snippet.
The entire script will report on ALL types of project transactions even linking them to the Journal Entry Number, but here is the Purchase piece of it:
SELECT PH.[Customer Number]
,CUS.[Customer Name]
,PH.[Contract Number]
,PH.[Contract ID]
,PH.[Project ID]
,PH.[Project Name]
,PH.[Project Number]
,PH.[Project Class ID]
,PH.[Project Type]
,PH.[Accounting Method]
,PH.[Status]
,PH.[Department]
,PH.[Estimator ID]
,PH.[Project Manager ID]
,PH.[Business Manager ID]
,PH.[Location ID]
,PH.[Salesperson ID]
,’Purchasing’ as ‘Transaction Type’
,ATR.[Journal Entry]
,RCT.[PAVIDN]’Transaction Number’
,RCT.[ITEMNMBR]’Master Record’
,POP.VENDNAME ‘Master Record Detail’
,POP.REFRENCE ‘Reference’
,POP.VNDDOCNM AS ‘Vendor Doc Number’
,” as ‘Submodule Reference’
,POL.PONUMBER as ‘PO Number’
,CASE POP.POPTYPE
WHEN 1 THEN ‘Shipment’
WHEN 2 THEN ‘Invoice’
WHEN 3 THEN ‘Shipment/Invoice’
WHEN 4 THEN ‘Return’
WHEN 5 THEN ‘Return w/ Credit’
WHEN 6 THEN ‘IV Return’
WHEN 7 THEN ‘IV Return w/ Credit’
WHEN 8 THEN ‘In-Transit Inventory’
END as ‘Receipt Type’
,APP.APFRDCNM ‘Receipt Paid with Check Number’
,APP.DOCDATE as ‘Check Paid Date’
,POP.[GLPOSTDT] ‘ Transaction Date’
,RCT.[PACOSTCATID] ‘Cost Category’
,AC.[Account Number] AS ‘COGS Account’
,AC.[Account Description] AS ‘COGS ACCT Desc’
,CASE WHEN POP.POPTYPE IN (5,4,6,7) THEN RCT.[PABase_Qty]*-1 ELSE RCT.[PABase_Qty] END AS ‘Quantity’
,RCT.[UOMSCHDL] as ‘U of M’
,CASE WHEN POP.POPTYPE IN (5,4,6,7) THEN RCT.[PABase_Unit_Cost]*-1 ELSE RCT.[PABase_Unit_Cost] END ‘UNITCOST’
,RCT.[PATOTALOVERH]’Total Overhead’
,CASE WHEN POP.POPTYPE IN (5,4,6,7) THEN RCT.[PAShipmentExtCost]*-1 ELSE RCT.[PAShipmentExtCost] END AS ‘Extended Cost’
,CASE WHEN POP.POPTYPE IN (5,4,6,7) THEN RCT.[PAShipmentExtCost]*-1 ELSE RCT.[PAShipmentExtCost] END ‘Total Cost’
,CASE WHEN POP.POPTYPE IN (5,4,6,7) THEN POP.[SUBTOTAL]*-1 ELSE POP.[SUBTOTAL]END AS ‘Total of Invoice’
,RCT.[PAORIGBILLRATE]’Billing Rate Per Unit’
,RCT.[PAORIACCRREV]’Recognized Revenue’
,RCT.[PABilled_QtyN]’Billed Quanitity’
,RCT.[PA_Billed_Profit_Amount_] ‘Billed Profit’
,RCT.[PABilled_Amount]’Total Billed’
,RCT.[PABilledProfitPercentage] as ‘Billed Frieght’
,RCT.[PABilled_Discount] ‘Billing Discount’
,RCT.[PABilling_StatusN]’Billing Status’
FROM [PAProjects] PH LEFT JOIN
[Customers] CUS ON PH.[Customer Number]=CUS.[Customer Number] LEFT JOIN
[PA31102] RCT ON PH.[Project Number]=RCT.[PAPROJNUMBER] LEFT JOIN
[PA31101] RCH ON RCT.[PAVIDN]=RCH.PAVIDN LEFT JOIN
[POP30300] POP ON RCT.PAVIDN=POP.POPRCTNM LEFT JOIN
[POP30310] POL ON RCT.PAVIDN=POL.POPRCTNM AND RCT.RCPTLNNM=POL.RCPTLNNM AND RCT.ITEMNMBR=POL.ITEMNMBR LEFT JOIN
(SELECT * FROM PM30300 WHERE APTODCNM IN (SELECT TOP 1 (APTODCNM) FROM PM30300 GROUP BY APTODCNM))
as APP ON POP.VNDDOCNM =APP.APTODCNM and POP.VENDORID=APP.VENDORID LEFT JOIN
–[PM30300] APP ON POP.VNDDOCNM =APP.APTODCNM and POP.VENDORID=APP.VENDORID LEFT JOIN
[Accounts] AC on RCT.PACogs_Idx=AC.[Account Index]LEFT JOIN
[AccountTransactions] ATR ON ATR.[Journal Entry]=(select TOP 1 ATR.[Journal Entry] WHERE RCT.PAVIDN=ATR.[Originating Document Number] AND RCT.PACogs_Idx=ATR.[Account Index] )
WHERE RCT.[PAVIDN] IS NOT NULL
AND POP.[GLPOSTDT] IS NOT NULL
and POP.POPRCTNM <> ‘RCT100153’
