Sage 100

Anyone tried to link an AP Invoice LINE in history to the original PO LINE that it was on? I can get as far as the PO#, however linking back to the line doesn't seem possible.

Curious - why do you need to relate to the specific line? Is the client ordering the items on multple lines and trying to measure vendor performance or is it regarding PO clearing? I wrote a PO Clearing report and was able to get the invoice number but don't recall if it was line level-I'll dig it out and look.

It is for an integration to a third party budgeting / requisition system

Right now I'm displaying the Line Key in the PO Detail as a unique field. What I'd like to do is transfer that same number to AP Invoice History Detail. It doesn't look like I can do it via UDF -- or if I can I'm overlooking it.

What I found is that the line sequence in PO Details and PO Rec History Detail both start at 1 and the line sequence in AP History Detail starts at 0, so if you want to transfer that value from PO to AP then you'll need to account for the difference in the starting sequence, easiest to add +1 to the AP sequence, i would think. I'm not sure a UDF is going to work. How do you need to integrate via ODBC or can you use an intermediary db?

I'm connecting via MS Access / ODBC.

Oh good, that makes it easier. Write a module with a function to add 1 to the AP line sequence, called it APSeqPO, set to string and left zero filled. Then add a query with the AP History Detail File, the fields you need plus the function you wrote, call it qryAPHistDetail. Create another query and add the query qryAPHistDetail and then the PO History Detail with the links of the PO number and the field with the APSeqPO. Add what ever other fields you need to this query. The second query will automatically run the first query and you should be all linked up. If you need a sample db let me know.

Doesn't this assume that all invoices are entered through PO? If some are entered through AP Invoice entry won't the line sequence be off?

I'm not so sure that this is as simple as some links. As near as I can tell: POP = Receipt History Header - Holds Link to receipt of goods line - Holds total for receive of invoice but no link to a detail line (that I can find) Total invoiced by item appears to be stored in the PO2. If there's a way to back into the detail invoiced lined (NOT received) via POP (receipt of good header) - then I'm missing it.

If you look at the PO Receipt History report - this seems to bear out my above observation. The detailed info by item # is all for receipt of goods. Receipt of invoice is only shown on the Invoice No. / PO level.

...entered through AP..."" well that does through a wrench in the works. If the users enters PO invoices in AP then you'll be hard pressed to find anyway to relate the AP entered invoice to a PO. And yes, I do see what you mean about PO Rec. History Detail not including ""I"" type records. On the AP History header you have the invoice #, PO #, source module to work with. On the AP history detail side you have invoice #, header seq and the detail seq (where AP detail line 0 = PO line 1, and AP detail line 1 = PO line 2, etc.) If you link the AP History to PO Detail (not PO History since an item may be invoiced and not received), then you'll get the PO invoiced side of the detail, but you'll first need to link the AP Hist Hdr with AP History Detail and adjust for the line sequence.

Wayne how far back do you need to go for this third party link or is the link for current and future records?

Moira we'd be happy with future records. They want to have historical data but I think they'd be ok with just going forward. And to be clear - the user is using a combination of AP and PO. In other words all the PO's do get invoiced properly through receipt of invoice. I was originally thinking the line index was a consecutive running total but can see that it is not and resets for each AP invoice. I guess I'm confused about how the AP Invoice History Detail has the same line sequence (I understand the shift of adding 1) --- and I'm assuming that once one line on a PO is entered -- even if not sequential/the first line -- that you've seen MAS pulls over ALL the PO lines int he same order and puts them into AP invoice history detail?

In my test, the first invoice for a PO had a qty invoiced for each of the three lines, first line total qty invoiced, second and third lines partially invoiced. The second invoice for the same PO had zero invoiced for the first line and remaining qty to invoice received on lines 2 and 3. In the AP History Detail, for this second invoice, all three lines came through with the qty invoiced on the first line (that had been previously completely invoiced) as zero. So, yes I think that even if all lines are not invoiced all lines are written to AP Hist Detail. A PO with multiple lines writes all lines to AP - but here's the kicker - if the PO had say 4 lines to start, was partially invoiced, then the PO lines were edited, a line added or removed/the order changed, then the invoice sequence from the first invoice to the subsequent invoices will not hold true. I'm going to check out using a UDF, as you'd first mentioned for carrying the PO line sequence through to AP History detail.

These folks are on 4.3 which is why I think I'm not seeing some of the same field. For example PO2 does not have a line sequence in 4.3. No idea how the conversion inserts numbers unless it's just sequentially by PO.

Oh - that would have been nice to know :). I did the test on 4.5 PU2. However I ran the UDF test. Added ""Line No ""(UDF) to PO Detail, PO Work, carried to PO Receipt Detail, PO Receipt/Inv Work and then to AP Invoice Detail and AP Invoice History with bus obj ref on all but the first. Ran a PO Inv receipt and all carried through. It may be that you'll want two UDF, a line no and something else, maybe a date, so that you'll be able to handle users adding/deleting lines. I'm not a BOI expert, not even novice, but would think that with BOI you'd be able to populate the PO detail UDF automagically with the line number. In version 4.3, if the udf has the same attributes, i.e., name, type it'll carry ti through but PO side and then you can setup the AP side to pull it over. Make sure if either case the the Line No Udf mask doesn't include a decimal. Why haven't they upgraded from 4.3?

We were in the midst of an upgrade and their corporate parent mandated a company wide project to gather exported data into a central budget/spending forecast module. Upgrade stopped. This project started (due date today). The main issue is that I'm only a week into this and there are about 10 tables that need to be populated, instructions aren't great so I'm not even to the point of where I've fully got my arms fully around why or how this data extract is being used -- which makes it difficult to consider potential workarounds. Throw in a few 5 to 8 people ""project management"" meetings where the typical feedback is ""isn't there anyone smarter than you who can figure this out"" (I find this is a very common question from IT led projects)...

fi they are willing to only use future records, wouldn't the workaround be to finish the upgrade so that using the UDF functionality, you could provide the data requested? Why spend time making it work in v4.3 when it will work easily in v4.5?

It's due today -- this was deadline driven. Being managed by a corporate project management team that's also managing another 12-15 sites that are feeding data.