Getting the Origin Type from A Journal Entry - Solution

Former Member — Tue, 09 Jul 2019 18:45:03 GMT

Hello,

As I'm not of sufficient level on this to make a blog post, I thought I'd post this as a question so others can make use of my solution.

As you know there isn't a simple way to get the Origin code via SQL command when reading the ODJT table. Below is a Scalar Value Function you can use to automate getting those codes. Of course, you could also modify this to be used in a query, if you really wanted to.

This will create a on your SQL server. You will need to change the USE statement to match your database name.

USE [CHANGE_TO_YOUR_DATABASE]
GO /****** Object:  UserDefinedFunction [dbo].[GetTransType]    Script Date: 2019-07-09 11:36:06 AM ******/
SET
   ANSI_NULLS 
   ON 
   GO 
   SET
      QUOTED_IDENTIFIER 
      ON 
      GO CREATE function [dbo].[GetTransType](@tt_variable nvarchar(60)) RETURNS varchar(2) with execute as caller as 
      begin
         -- GetTransType will return the two letter mnemonic of journal entry
         -- for the field TransID
         -- USAGE:  SELECT dbo.GetTransType(T0.TransID) as 'Origin' from ODJT T0
         return 
         CASE
            WHEN
               @tt_variable = '-3' 
            THEN
               'BC' 
            WHEN
               @tt_variable = '-2' 
            THEN
               'OB' 
            WHEN
               @tt_variable = '13' 
            THEN
               '13' 
            WHEN
               @tt_variable = '14' 
            THEN
               'CN' 
            WHEN
               @tt_variable = '15' 
            THEN
               'DN' 
            WHEN
               @tt_variable = '16' 
            THEN
               'RE' 
            WHEN
               @tt_variable = '18' 
            THEN
               'PU' 
            WHEN
               @tt_variable = '19' 
            THEN
               'PU' 
            WHEN
               @tt_variable = '20' 
            THEN
               'PD' 
            WHEN
               @tt_variable = '21' 
            THEN
               'PR' 
            WHEN
               @tt_variable = '24' 
            THEN
               'RC' 
            WHEN
               @tt_variable = '25' 
            THEN
               'DP' 
            WHEN
               @tt_variable = '30' 
            THEN
               'JE' 
            WHEN
               @tt_variable = '46' 
            THEN
               'PS' 
            WHEN
               @tt_variable = '57' 
            THEN
               'CP' 
            WHEN
               @tt_variable = '58' 
            THEN
               'ST' 
            WHEN
               @tt_variable = '59' 
            THEN
               'SI' 
            WHEN
               @tt_variable = '60' 
            THEN
               'SO' 
            WHEN
               @tt_variable = '67' 
            THEN
               '67' 
            WHEN
               @tt_variable = '69' 
            THEN
               'IF' 
            WHEN
               @tt_variable = '76' 
            THEN
               'DD' 
            WHEN
               @tt_variable = '162' 
            THEN
               'MR' 
            WHEN
               @tt_variable = '182' 
            THEN
               'BT' 
            WHEN
               @tt_variable = '202' 
            THEN
               'PW' 
            WHEN
               @tt_variable = '203' 
            THEN
               'DT' 
            ELSE
               '' 
         end
      end;

Re: Getting the Origin Type from A Journal Entry - Solution

Vitaliy-R — Tue, 09 Jul 2019 18:56:27 GMT

Hi Gord. it would be helpful if you add as well tag of SAP products that this post is related to. Cheers!

Re: Getting the Origin Type from A Journal Entry - Solution

msicard — Mon, 19 Jun 2023 15:42:43 GMT

This would have made more sense if you had the correct table. It's OJDT not ODJT.

For 13 the Origin should be 'IN' not 13, 19 should be 'PC' not 'PU'.

https://answers.sap.com/questions/7839724/help-to-list-the-origin-code-from-je-in-sbo.html

topic Re: Getting the Origin Type from A Journal Entry - Solution in Application Development and Automation Discussions

Getting the Origin Type from A Journal Entry - Solution

Re: Getting the Origin Type from A Journal Entry - Solution

Re: Getting the Origin Type from A Journal Entry - Solution