Dynamics Community Forum Thread Details

WIP Report for Project accounting

Dear All,

Last week I worked on WIP reports for all projects in Project accounting module.I hope below query will helps others for those who are struggling in WIP projects wise with GL in GP.

Kindly contact me if need any help.

select JRNENTRY,TRXDATE,c.CUSTNMBR,c.PAPROJNUMBER,c.PAprojname ,SOURCDOC ,ORDOCNUM ,DEBITAMT,CRDTAMNT from GL20000
inner join (
select distinct PAVIDN,PAPROJNUMBER from PA31102) b
on b.PAVIDN=ORDOCNUM
inner join (select custnmbr,paprojname,paprojnumber from PA01201) c
on b.PAPROJNUMBER=c.PAPROJNUMBER
where SOURCDOC like 'rec%'
and TRXDATE<='2017-06-30 00:00:00.000'
and ACTINDX='69'

union

select JRNENTRY ,TRXDATE,c.CUSTNMBR,c.PAPROJNUMBER,c.PAprojname ,SOURCDOC ,ORDOCNUM ,DEBITAMT,CRDTAMNT from GL20000
inner join (
select distinct PAIV_Document_No,PAPROJNUMBER from PA30901)b
on b.PAIV_Document_No=ORDOCNUM
inner join (select custnmbr,paprojname,paprojnumber from PA01201) c
on b.PAPROJNUMBER=c.PAPROJNUMBER
where SOURCDOC like 'PA%'
and ACTINDX='69'
and TRXDATE<='2017-06-30 00:00:00.000'

union
select JRNENTRY,TRXDATE ,c.CUSTNMBR,c.PAPROJNUMBER,c.PAprojname,SOURCDOC ,ORDOCNUM ,DEBITAMT,CRDTAMNT from GL20000
inner join (
select distinct PADocnumber20,PAPROJNUMBER from PA23200)b
on b.PADocnumber20=ORDOCNUM
inner join (select custnmbr,paprojname,paprojnumber from PA01201) c
on b.PAPROJNUMBER=c.PAPROJNUMBER
where SOURCDOC like 'PA%'
and ACTINDX='69'
and TRXDATE<='2017-06-30 00:00:00.000'

Quick Links