Authorisation issue for Purchasing

[former_member459477]

I want to set authorisation for a user to post AP invoices if the total amount is the same as the PO but not otherwise.

I have set the authorisation for AP invoice to 'Full Authorisation' and the Goods Receipt PO to 'Read only' and found I have to set the Max.Discount to 0.00 in order to prevent any discrepency between the amount of the PO and the amount of the actual invoice being reconciled by a discount appearing in the 'Discount%' field when the amount of the actual invoice is entered in the 'Total Payment Due' field.

However I want the user to be able to post the invoice if there is a discount recorded on the PO which has been copied through to the Goods Receipt PO and to the A/P invoice but this doesn't seem to be allowed unless the Max.Discount is set to a % which encompasses this discount - and if I do that the system then automatically fills in a discount in the Discount% field on the AP invoice if the total amount of the actual invoice is different to the amount of the PO.

I'd be grateful for any advice on resolving this so that the user can post an invoice only if the amount matches the Goods Receipt PO - even if there is a discount already there.

[Former Member · Accepted solution]

Hi Andrew,

That is my suggestion in previous post. Your requirement has to be by your own query. If you have question to make query working, let me know.

Thanks,

Gordon

[former_member459477 · Accepted solution]

Thanks Gordon - I'm afraid I do need some help with the query!

I've been looking at the query generator and can see how to pick out the fields and tables - I've found that table 'PDN1' (Goods Receipt PO-Rows) has a field 'DiscPrcnt' (Discount % per row) but I'm not sure where to go from here. Can you advise in more detail how to get the query right and then implement it?

[former_member583013 · Accepted solution]

The Query based Approval Procedure will only work if you copy a Goods Receipt completely to an A/P Invoice, which I hope is the case.

SELECT 'TRUE' FROM [dbo].[OPDN] T0 
WHERE ISNULL(T0.DiscSum,0) != $[$42.0.Number] AND T0.DocEntry = $[PCH1.BaseEntry]

[former_member459477 · Accepted solution]

Hi Suda

Thanks for your response. I'm afraid I'm still struggling with this, do I need to customise the query you advised above?

I get the following message:

1) [Microsoft][SQL Native Client][SQL Server] Incorrect syntax near '$42.0.Number'

2) [Microsoft][SQL Native Client][SQL Server] Statement 'Service Contracts' (OCTR) (S) could not be prepared

Karen

Edited by: Andrew Birt on Oct 7, 2008 12:27 PM

[former_member583013 · Accepted solution]

The query I provided should be saved as is.

You will follow the steps that you did for setting up the Approval Template but in the Terms Tab

Check User defined and from the bottom window..double click on the first row of the Query Name column and link the query you have saved.

Now test your transaction

[former_member459477 · Accepted solution]

Hi again Suda,thanks for your patience - unfortunately I'm still struggling with this,

I have saved the Query and in the Approval Templates Setup window,on the terms tab I selected 'when the following terms apply' and on the first line of Queries area I linked to the Query.

However when I sign on as the user who is subject to the approval it appears not to have changed anything:

-when I set her Discount to 0.00% in Authorizations she cannot enter either invoice - even the one with discount copied through from the Goods Receipt PO..

error appears: [A/P Invoice-Rows-Discount% Per row]You are not permitted eto perform this action[Message 200-30]

-when I set her Discount level to '100%' in 'Authorisations' she can enter any invoice whether the discount is at row level as copied from the Goods Receipt PO but also when the amount of the actual invoice total is different to the total amount shown on the PO and an amount appears in the discount box when she changes the total amount on the APInvoice to match the actual invoice.(Although the bottom bar flashes Red momentarily before adding the APinvoice..)

I must be doing something wrong or missing a step somewhere - can you suggest where I'm going wrong???

[former_member583013 · Accepted solution]

I do not quite following what you are saying by However when I sign on as the user who is subject to the approval it appears not to have changed anything:

What I have given you is the Query for the Approval Procedure..What is that you are trying to do with the Authorizations. these are completely two different things.

[former_member459477 · Accepted solution]

Suda,

Thanks so much for your patience - unfortunately I had not created the Approval Template correctly but I have now done so and the User query is running as you said, your help is much appreciated.

Karen

[Former Member]

This query will not run under query manager. It is just like a FMS query runnable only under active form.

Thanks,

Gordon

[former_member186095]

Hi,

you could also use store procedure transaction notification where it is a good feature from B1 to make a validation like your current issue.

When it is applied in the store procedure section in the company database, it will work like your requirement.

Here is a solution sample:

IF @transaction_type IN (N'A', N'U') AND (@Object_type = N'20')
begin 
if exists (SELECT T0.BaseEntry, SUM(T0.Quantity) FROM [dbo].[PDN1] T0 INNER 
JOIN [dbo].[POR1] T1 ON T1.DOCENTRY = 
T0.BASEENTRY
WHERE T0.BaseType = 22 AND T0.ItemCode = T1.ItemCode AND T0.BaseLine = T1.LineNum
and T0.DOCENTRY = @list_of_cols_val_tab_del
GROUP BY T0.BaseEntry
HAVING (SUM(T0.Quantity) > SUM(T1.Quantity)) or sum(t0.quantity) > sum(t0.BaseOpnQty))
begin
select @Error = 10, @error_message = 'GRPO quantity is over PO quantity'
end
end

The above SP_TN will block any GRPO creation if the quantity is over quantity PO.

Discuss with technical consultant or partner's technical consultant for further improvement of the query so that suitable for your requirement.

Rgds,

[Former Member]

Hi Andrew,

If you need authorization for AP Invoice by any conditions, then that is not available in the current version. You may achieve it by SDK if you have time and resource to do so.

Instead of authorization, approval by user query can be used in your case to reaach your goal.

Thanks,

Gordon

[former_member459477]

Gordon,

Thanks for your suggestion of using approval by user query to achieve this.

I am attempting to implement this and so far I have done the following and would be grateful if you could advise if I am barking up the right tree:

1) I went to Admin>Approval Procedures>Approval Stages and set up an Approval stage called 'Accounts Admin' which is set to require one approver out of a choice of 2 users

2) I am now attempting to set up an Approval Template called 'A/P Invoice discount'

I have entered the user in the 'Originator' tab and ticked A/P Invoice in the 'Documents' tab.

I have selected the 'Accounts Admin' approval stage I created in step 1 in the 'Stages' tab.

On the 'Terms' tab I have selected the radio button 'When the following applies' and ticked 'Discount %' from the list of terms but looking at the drop-down options in the 'Ratio' column it looks like I cannot use this to allow acceptance of a discount which was entered on the original Purchase Order but NOT acceptance of a discount automatically calculated and put in by SAP on the A/P Invoice when the user enters a Total amount which is different to the PO and Goods Receipt PO..

Am I right in thinking I need to use the bottom section on the 'Terms' tab and create a Query somewhere else to select here?

Many thanks

Karen