on 10-26-2016 7:32 AM
Good Day Experts,
Please help me to my sql query code. I want to block specific users adding A/R invoice if the customers exceeded to their commitment limit.
If @object_type = '13' And @transaction_type in ('A')
If Exists(Select T0.DocEntry
From OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
Left Join OCRD T2 On T0.CardCode = T2.CardCode
left join OCHH T3 On T0.CardCode = T3.CardCode
Left join JDT1 T4 On T0.CardCode = T4.shortname
Inner Join OUSR t5 On T0.UserSign = T5.USERID
Where T0.DocEntry = @list_of_cols_val_tab_del AND (T5.USER_CODE='manager' or T5.USER_CODE ='user2' OR T5.USER_CODE ='user3' OR T5.USER_CODE = 'user4' OR t5.USER_CODE = 'user5')
Group By T0.DocEntry,T2.DebtLine
Having(sum(T4.[Debit]- T4.[Credit])+sum(T3.[CheckSum])+sum(T0.[DocTotal]))>T2.DebtLine)
Begin
SET @error=@list_of_cols_val_tab_del--@object_type;
SET @error_message ='Exceed Commitment Limit'
Hi Romel,
This can be managed with the help of system wide configuration and user level authorization.
By selecting commitment limit and A/R invoice you can activate the limit check.
By giving No Authorization to the user you can restrict the punching of A/R invoice on violation of credit / commitment limit.
Regards
Narayani
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
IF @object_type = '13'
AND @transaction_type IN ('A')
IF EXISTS(
SELECT T0.DocEntry
FROM OINV T0
INNER JOIN INV1 T1
ON T0.DocEntry = T1.DocEntry
LEFT JOIN OCRD T2
ON T0.CardCode = T2.CardCode
LEFT JOIN OCHH T3
ON T0.CardCode = T3.CardCode
LEFT JOIN JDT1 T4
ON T0.CardCode = T4.shortname
INNER JOIN OUSR t5
ON T0.UserSign1 = T5.USERID
WHERE T0.DocEntry = @list_of_cols_val_tab_del
AND (
T5.USER_CODE = 'manager'
OR T5.USER_CODE = 'user2'
OR T5.USER_CODE = 'user3'
OR T5.USER_CODE = 'user4'
OR t5.USER_CODE = 'user5'
)
GROUP BY
T0.DocEntry,
T2.DebtLine
HAVING (
SUM(T4.[Debit] - T4.[Credit]) + SUM(T3.[CheckSum]) + SUM(T0.[DocTotal])
) > T2.DebtLine
)
BEGIN
SET @error = @list_of_cols_val_tab_del--@object_type;
SET @error_message = 'Exceed Commitment Limit'
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Sir Ken,
What's the difference my code to your replied code?
Thanks
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Sir Ken.
Thank you for your reply, by the way what's the difference of your code to my code?
thanks
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Romel,
Ideal thing to practice would be to maintain the price list and bind it to the customer. So that the price will be automatically fetched.
Because this can not be handled through authorization. Also SAP updates Last Purchase Price automatically.
If at all you want to go by this then, SPTN is the only option.
Regards
Narayani
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Romel,
I guess this is another question. If so, please explain the scenario because in this thread you mentioned about commitment limit.
Regards
Narayani
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
107 | |
12 | |
11 | |
6 | |
6 | |
4 | |
4 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.