Dynamics GP
Dynamics GP
Organizer:
- Organized by
-
-
GL account transaction with Project accounting ‘project’ linked
-
GL account transaction with Project accounting ‘project’ linked
Need sql query to link the project entered in PM – invoice expense charged to a PA project. I’ve got all the GL info and ‘originating’ fields. Can anyone guide me to linking the actual project that was charged?
-
15 Replies
-
Hi Deanne,
Can you confirm what you mean when you say PM-Invoice? What screen are you using? Most payables related to project accounting start life in the Purchasing side of the house (PO’s or Receivings Transaction Entry).
I’ve written several things for clients who use project accounting, but knowing what screen your invoices originate on will help me hone in on what window (and by extension which tables) you need.
Best,
Samantha
-
-
-
-
Hi Deanne,
Kerry Hataley has given you a great starting point with the PA30XXXXXX tables – if you’re talking the receivings transactions specifically, I would look at the PA301101, PA31102 and PA31103 tables.
The PA11800 and PA11801 are my go to tables as well because they combine history information for you. Based on your replies though it sounds like your folks aren’t running the Combine History Utility (the process Kerry Hataley is talking about) to make those tables usable. I’d look into having them run that utility as well.
If that doesn’t get you what you need, there are some things you can do with different views to combine things, but bout 95% of the time the two groups of tables above will get the job done when combined with GL information.
-
-
-
-
-
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’
-
-
Deanne,
Send me a quick email at jo.deruiter@aislingdynamics.com and I’ll send you a few scripts that may help
-
-
The discussion ‘GL account transaction with Project accounting ‘project’ linked’ is closed to new replies.
