PM & PO - Purchase Orders for Jobs

[iwells]

Hi,

I'm trying to develop a query to obtain purchase order information related to jobs and I'm wondering if anyone can shed some light on the way I'm trying to do this?

I'm attempting to use PMTRAN as my main source (this might be my issue right there) because it contains all the transactions related to all contracts and jobs. When I look for ways to join this table to other tables (POPORH1/POPORL/POPORAH/POPORAL/PORCPH1/etc) I see PMTRAN has DAYENDSEQ and DRILLDWNLK is the equivalent of PORHSEQ in POPORH1 (in my case my WHERE clause has DOCTYPE = 16 for now), but after that I'm not seeing anything else.

If I try to join PMTRAN, POPORAH and POPORAL I get the replication of records because PMTRAN contains an entry for each line item on a PO, but only provides the header sequence and not a line sequence for the record.

Perhaps I'm not seeing a field I need to use or should be joining it differently, but I'd like to know how others are performing similar queries. I guess I could do a WHERE IN / EXISTS for PORHSEQ in PMTRAN but it seems odd Sage would be 2 fields necessary to join but not a 3rd?

 

[DjangMan]

What other information are you wanting that isn't in PMTRAN? Another way to look at the data is through the 7 or 8 PJC posting journals. But PMTRAN is usually my go to table for PJC.

 

[iwells]

I'm trying to get the expected arrival date and vendor information ... everything else I need is in the table. Thought Djang?

 

[iwells]

Vendor information is there, so just the expected arrival date ...

 

[DjangMan]

Nothing jumps out at me on a way to link the individual PO lines with individual PMTRAN lines. The POPORAL table doesn't seem to have anything useful.

Can you drive your report from the PO detail line and group together the items that have the same contract, project, category and item?

 

[iwells]

That's what I'm going to have to do ... just seems like poor design on the PMTRAN table to have detail lines, but no direct link to the source of the detail lines.

 

[DjangMan]

I agree. Auditing PJC is a real challenge, too.