Sage 200 help

Sage 200 help

Didn't find your answer?

Hi,

I would really appreciate some help. I need a nominal ledger listing extract with the PO numbers where they exist by the nominal entres.

I have done a dump of NLPostedNominalTran and I thought I'd add the PO to the right of the data dump I but can't find a common field in that table with anything else. I have tried PLPostedSupplierTran,, POPOrderReturn,POPOrderReturnLine. I can't find any links in a manual I have either.

Perhaps I am over labouring this but I can't find a report, it's driving me crazy, can anyone help?

Thanks

Matt

Replies (10)

Please login or register to join the discussion.

itassolutions logo
By ITAS
11th Jul 2015 20:55

links
Hi Matt

I will check it tomorrow for you but from memory you need to go via the PL.

You connect URN with the PLPOSTEDSUPPLIERTRANS then link that to the POPORDERRETURNS table with the PLSUPPLIERSECONDREF , I know that is definitely the case for SOP and I think it is the same for POP. We also have a custom report with it on that we did for..so I will dig it out tomorrow too.

Hope this helps

Warm regards

Hannah

Thanks (0)
Replying to lionofludesch:
avatar
By MDK45
13th Jul 2015 15:14

Hi Hannah,

Thank you for your reply. I tried this as I thought the suppliertran table would be the mirror image of customertran but it isn't. The POP order return line table has no URN.

Thanks

 

Mat

Thanks (0)
itassolutions logo
By ITAS
13th Jul 2015 10:21

Update

Hi Matt, 

When I pulled up the custom report we made, we actually had to create a custom view to connect this. 

It is done by using the POPinvcreditlinearch. It is done at a line level because a single PL invoice can be related to multiple lines from multiple purchase orders. And the postings in the nominal aren't linked directly to the POP invoice line (which makes it fun!)

The client we did this for only had a single PO on each PL invoice, so we wrote a view that basically took the first line that was related and linked it to the PO/PL invoice. 

You have a couple of options here:

1. When entering the PO you can enter the PO no. into the second reference (and then use that to link) but that is dependant on your data entry skills. 

2. Limit yourself to a single PO on each PL invoice and create view above (or ask your Business Partner to do it). Remember this will only allow you to do it in excel ODBC, unless they add it into report designer for you. 

3. Ask them to create you a view that connects all the tables (then get them to concatenate those that are duplicates) 

I hope this helps - sorry I couldn't give you an easy answer!

Warm regards

Hannah 

Sign up to Sage 200 Buzz - Regular Sage 200 hints and tips, ebooks and free reports - straight to your inbox!

itas - Award winning supplier of Sage 200, ERP, CRM, Business Intelligence and Manufacturing solutions.

http://itassolutions.co.uk | @itassolutions

 

 

Thanks (0)
avatar
By MDK45
13th Jul 2015 15:24

Hi Hannah,

 

I just dumped the POPInvcreditlinearch table into Excel and there's nothing in it. I'm not sure what to do. It looks like I need a view. Like you say I thought there would be a definitive link between the PO lines and the nominal ledger entries (like the sales ledger), I find this confusing that you can't link the two when they seem to be a logical sequence order of events.

I also cant see a second ref box in the PO generation, our POs are generated back to back automatically so this would be a way forward although not by analysing the past which I need to do for commssions.

Thanks for your help.

 

Matthew

Thanks (0)
itassolutions logo
By ITAS
13th Jul 2015 15:44

POP invoice line arch

Hi Matthew, 

If you look in SQL, is there any data in POPInvCredLineArch table?

That table is only populated when you post an invoice against the PO. The second reference is generated when you post the invoice, it pops up the standard invoice screen, in which you can then enter the POP order number into the second ref screen. 

The reason there isnt a direct link is because the invoice nominal postings are actually generated by the PLInvoice when you invoice the PO and not the POP order itself. Semantics but the joys of the Sage 200 data structure. It goes

1. Create POP order

2. Invoice POP order (create PL invoice) 

3. PLInvoice then posts to the nominal 

It might be worth having a chat with your support provider, they can jump on and have a look at your tables directly. It's pretty hard to give an exact solution, when I cant see what you are looking at, Sage 200 can be used in so many different ways. 

Warm regards

Hannah 

Sign up to Sage 200 Buzz - Regular Sage 200 hints and tips, ebooks and free reports - straight to your inbox!

itas - Award winning supplier of Sage 200, ERP, CRM, Business Intelligence and Manufacturing solutions.

http://itassolutions.co.uk | @itassolutions

 

Thanks (1)
avatar
By MDK45
14th Jul 2015 09:16

Hi,

There isn't anything populated in POPInvCredLineArch. The second reference field is interesting thank you, I'll sit with an invoice being posted to see what happens.

Thanks

 

Matt

Thanks (0)
avatar
By MDK45
15th Jul 2015 07:15

Thanks hannah, i have now got purchase ledger populating the second ref field with the po so i have a link between PO and purchase ledger/nominal ledger going forward. I still have the problem of joining the pos to the nominal history historically to calculate commissions and profitability, any ideas? Thank you very much for your help. As for sage support we dont have a good relationship currently....

Thanks (0)
itassolutions logo
By ITAS
15th Jul 2015 13:34

historical

Hi Matt, 

Firstly for the historical ones, you have two options:

 

1. go back through and manually adjust the second reference in the PL

2. Get your sage support to build you a view to pull it out (normally chargeable) 

 

What profitability reporting are you trying to do? Is there a reason you dont use the profitability reporting in SOP?

 

Re your support provider, it maybe worth having a chat with them to see if you can rebuild that relationship. A good support provider can really help you maximise your use of Sage 200. 

If that doesn't work then feel free to send me an email on [email protected] and I would be happy to talk support options with you. 

Warm regards

Hannah

Sign up to Sage 200 Buzz - Regular Sage 200 hints and tips, ebooks and free reports - straight to your inbox!

itas - Award winning supplier of Sage 200, ERP, CRM, Business Intelligence and Manufacturing solutions.

http://itassolutions.co.uk | @itassolutions

Thanks (0)
avatar
By MDK45
15th Jul 2015 19:36

Can i do option 1 if the invoice has been posted already? yes, option two could be a 'goer' hopefully. Not using the profitability report as that reports the po cost rather than the invoice cost booked i thought? I may email you privately re the support issue?

Thanks (0)
itassolutions logo
By ITAS
17th Jul 2015 11:41

amend the invoice

Hi Matt, 

Yes you can amend the invoice via the PL. Simply go to amend details and edit the references.

Yes it does, but do you get a big variance in PO/PI if not, it may be worth doing an exception report. i.e. when the PI cost exceeds the PO.

Just a thought!  

Hope this helps 

Warm regards

Hannah 

Thanks (1)