PT Sterling Tulus Cemerlang
www.sterling-team.com

Phone
+62-877-8655-5055
+62 (21) 580-6055

Email
sales-enquiry@sterling-team.com

Address
Sampoerna Strategic Square
South Tower 18th
Jl Jend. Sudirman kav 45-46
Jakarta 12930 - Indonesia

400+ FREE Tips for you from Sterling Tulus Cemerlang (STEM) - SAP Indonesia Gold Partner, and Become SAP B1 Super User

Inventory Audit Report Not Match with G/L Account Balance

SHARE ON

There’s always a common problem when you find your Inventory Cumulative Balance doesn’t match with your Inventory G/L Account Balance. So, the answer is you post a manual Journal Entries on SAP, and create using the Inventory G/L accounts.

At the point where you manually adjust the inventory accounts then SAP will no longer spit out the inventory audit report with the exact same balances as the GL accounts.

Now, we will help you to check directly on your database, manual Journal Entries to inventory G/L accounts, with these simple queries and another prevention method:

  • Query checking:
     SELECT T1.Account, sum(T1.Debit-T1.Credit) Balance
     FROM OJDT T0
     INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId
     WHERE T0.TransType = 30 and T1.Account in (SELECT distinct InvntAct FROM OINM)
     and T0.RefDate = '[%0]'
     GROUP BY T1.Account
  • Setup Block Manual Posting:
    please see the complete information on here
    Or Create SP Transaction Notification, you can paste this query into SQL Management Studio or HANA studio:

-SQL VERSION-

IF (@transaction_type = ‘A’ OR @transaction_type = ‘U’) AND @object_type = ’30’
BEGIN
 IF EXISTS(
 SELECT 
 T0.[TransId] 
 FROM
 OJDT T0 
 INNER JOIN JDT1 T1 ON T0.[TransId] = T1.[TransId] 
 INNER JOIN OITB T2 ON T1.[Account] = T2.[BalInvntAc]
 WHERE 
 T1.[TransType] = 30 
 AND T0.[TransId] = @list_of_cols_val_tab_del) 
 AND (SELECT S0.[DspIITMDoc] FROM ADM1 S0
 ) = ‘Y’
 OR
 EXISTS(
 SELECT 
 T0.[TransId] 
 
 FROM
 OJDT T0 
 INNER JOIN JDT1 T1 ON T0.[TransId] = T1.[TransId] 
 INNER JOIN OWHS T2 ON T1.[Account] = T2.[BalInvntAc]
 
 WHERE 
 T1.[TransType] = 30 
 AND T0.[TransId] = @list_of_cols_val_tab_del) 
 AND (SELECT S0.[DspIITMDoc] FROM ADM1 S0
 ) = ‘Y’
 BEGIN
 SELECT @error = -10, @error_message = ‘Direct Journal Entries to Inventory Accounts Not Permitted [Journal Entry – Account Code] [Message 60110-30]’
 

 END
END

 


-HANA VERSION-

IF (:transaction_type = 'A' OR :transaction_type = 'U') AND :object_type = '30' THEN
DECLARE JDT_EXISTS BIGINT = 0;
SELECT X."TransId" INTO JDT_EXISTS FROM 
 (
 SELECT T0."TransId"
 FROM 
 "OJDT" T0 
 INNER JOIN "JDT1" T1 ON T0."TransId" = T1."TransId"
 INNER JOIN "OITB" T2 ON T1."Account" = T2."BalInvntAc"
 WHERE 
 T1."TransType" = 30 
 AND T0."TransId" = :list_of_cols_val_tab_del)
 AND (SELECT S0."DspIITMDoc" FROM "ADM1" S0) = 'Y'
 
 UNION ALL
SELECT T0."TransId"
 FROM 
 "OJDT" T0 
 INNER JOIN "JDT1" T1 ON T0."TransId" = T1."TransId" 
 INNER JOIN "OWHS" T2 ON T1."Account" = T2."BalInvntAc" 
 WHERE 
 T1."TransType" = 30 
 AND T0."TransId" = :list_of_cols_val_tab_del) 
 AND (SELECT S0."DspIITMDoc" FROM "ADM1" S0) = 'Y'
 
 UNION ALL 
 
 SELECT 0 AS "TransId" FROM DUMMY
 ) X LIMIT 1;
IF :JDT_EXISTS <> 0 THEN 
 error:= -10;
 error_message:= 'Direct Journal Entries to Inventory Accounts Not Permitted [Journal Entry – Account Code] [Message 60110-30]'
 END IF;
END IF;
Run Your Business With The Piece of Mind Using SAP Business One

This article was written by PT. Sterling Tulus Cemerlang, SAP Business One GOLD Partner in Indonesia.
More details about SAP Business One,
email to: sales-enquiry@sterling-team.com or call +6221-5806336 or wa +6287786555055

, , ,