Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Irfan Rasheed]

Hi,

Is there a way to get project wise payable in GP.
I have explored it, there is not such any built in report in system and I have tried to get results via SQL query, where I can get data for project wise Invoice, but stuck in extracting information to get Net payable against any specific project.

Regards.

——————————
Irfan Rasheed
Staff ERP Executive
Avanceon limited
Lahore
——————————

[Silver Subie]

Not sure how far you’ve gotten but the below queries work for me.  Keep in mind we use legal entity from Binary Stream so I’m able to run all contracts for 1 entity and thus I could easily reconcile back to A/P Aging standard report limited by one entity.  You could replace PACONTNUMBER with PAPROJNUMBER if you want to run it at project level.  This does not take into consideration partially applied document.

Use it at your own risk. 

create table #ContractAPInvoices
(
PACONTNUMBER char(11),
PAPROJNUMBER char(15),
VENDORID char(15),
VNDDOCNUM char(21),
PAVIDN char(17),
PApurordnum char(17),
itemnmbr char(31),
GLPOSTDT datetime,
VENDNAME char(65),
Amount numeric(19,2),
Paid char(1)
)

insert into #ContractAPInvoices (PACONTNUMBER,PAPROJNUMBER,VENDORID,VNDDOCNUM,PAVIDN,PApurordnum,itemnmbr,a.GLPOSTDT,VENDNAME,Amount,Paid)
select PACONTNUMBER,PAPROJNUMBER,b.VENDORID,VNDDOCNM,PAVIDN,PApurordnum,itemnmbr,a.GLPOSTDT,VENDNAME,(PABase_Qty*PABase_Unit_Cost) as Amount,’N’ from PA31102 a
left join POP30300 b on a.PAVIDN=b.POPRCTNM
where a.GLPOSTDT between ‘1/1/17’ and ‘3/3/17’ and PACONTNUMBER like ‘34%’ and b.POPTYPE<>1

update #ContractAPInvoices set Paid=’Y’ from #ContractAPInvoices a, PM30300 b where a.VNDDOCNUM=APTODCNM and a.VENDORID=b.VENDORID

select * from #contractAPInvoices where Paid=’N’——————————————-