PMTRAN - Pending POs

[iwells]

Hi,

I'm trying to write a report for our branches which shows the pending (when I say pending I mean ordered but not received) POs related to jobs.

I see PMTRAN contains much of the information I want, but not all. First off PMTRAN doesn't contain the vendor itemno, though I think I could work around that if I could get passed the next issue. I'm joining PMTRAN to POPORH1 get the expected arrival date (which is an essential requirement of the report) and then I join POPORH1 to POPORL to get the order/received amounts along with the currency it was paid in.



SELECT
PMCONTS.[CONTRACT],
PMCONTS.[DESC],
PMTRAN.DOCDATE,
PMTRAN.DOCNUM,
PMPROJS.PROJECT,
PMPROJS.[DESC],
POPORL.EXPARRIVAL,
POPORH1.VDNAME,
POPORL.VENDITEMNO,
POPORL.ITEMNO,
POPORL.ITEMDESC,
POPORL.OQORDERED,
POPORL.UNITCOST,
POPORH1.CURRENCY,
POPORL.EXTENDED,
POPORL.FCEXTENDED,
POPORH1.SCURNDECML
FROM
PMCONTS
INNER JOIN
PMPROJS
ON PMCONTS.CTUNIQ = PMPROJS.CTUNIQ AND
LEFT(PMPROJS.PROJECT, 1) = '5' AND
PMCONTS.[CONTRACT] not like 'GL%'
INNER JOIN
PMTRAN
ON PMCONTS.[CONTRACT] = PMTRAN.[CONTRACT] AND
PMPROJS.PROJECT = PMTRAN.PROJECT AND
PMTRAN.MODULE = 'PO' AND
PMTRAN.COSTREV = 1
INNER JOIN
POPORH1
ON PMTRAN.DRILLDWNLK = POPORH1.PORHSEQ AND
POPORH1.ISCOMPLETE = 0
INNER JOIN
POPORL
ON POPORH1.PORHSEQ = POPORL.PORHSEQ AND
PMTRAN.[CONTRACT] = POPORL.[CONTRACT] AND
PMTRAN.PROJECT = POPORL.PROJECT

The issue I'm encountering is the replication of records when POPORL is added to the query because there's no direct key (that I know of and that's my main issue) between POPORL and PMTRAN at the line item level. I could create the query by omitting PMTRAN totally and just drill down on the CONTRACT and PROJECT fields in POPORL, however I'm not sure if that's the right way to do this. I'm looking for some insight as to whether I should abandon PMTRAN totally or if there's a link I've overlooked which will allow me to eliminate the replication (or another dumb mistake I'm making).

 

[iwells]

Accpac 5.5 and 5.6

 

[ettienne]

Why not work only with the PO tables?
What do you need from PMTRAN?

 

[iwells]

Hi,

I guess this comes from lack of experience with the system at the moment ...

I assumed that this information would be readily available in the PJC module and that I should grab it from the PJC source (I come from a MS Dynamics GP background where there are some nice transaction tables in their PJC). I can adjust the query only slightly and use POPORH1 and POPORL only.

 

[iwells]

Thanks for the response ... the database structure is a little different than what I'm used to so sometimes I need a yes or no from someone with experience to set me on the right path. I guess in this case I expected there to be more direct relationships between PJC and PO tables along with more detail tables in PJC related to PJC based transactions.

 

[Jay Converse]

It doesn't hit PMTRAN until it's received.

 

[iwells]

I've googled for PJC workflow by Stephen Smith and haven't been able to find anything ... does one of his nice workflows exist for that module? That would save this board future noob questions on this module.

 

[ettienne]

You will find the job information in POPORL.

 

[iwells]

Ettienne ... yea that's the route I'll go and it won't take much adjustment. I was just wondering for my future questions about this module and data flow if something like the following link exists for PJC:

http://community.sageaccpac.com/blo...flow-of-the-accpac-purchase-order-module.aspx

I looked on the site, but I don't see anything. You guys likely know the flow from experience, but we don't have a lot of experience with PJC at my organization, however it's going to play a much larger role and I was looking to find something like that to increase my understand for future report requests