Jo deRuiter
Forum Replies Created
-
Hi Canadian Guy,
There are already fields for these ID’s.
In Purchasing the Document Number is the Vendor’s Invoice Number and the Voucher Number is YOUR key field for that purchasing document – you should rarely be changing the Voucher Number unless you were specifically asked to.
In Sales, the Invoice Number is YOUR invoice number to the customer and is 100% unique. The PO Number field in Sales (whether Receivables or Sales) is good for Customers PO Number, which is generally what you are getting from a Customer)
If you are trying to use a separate source-systems invoice information, if it is done right, you can use those source-system numbers as your sales-invoice numbers already – you can use it in the integration to replace the default invoice numbers in GP.
Otherwise, let me advise against a developer just for User Defined (UDF) Fields in GP – the best product there is for this is eOne’s Extender and you can create tons of custom fields and report on those as well.
-
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’
