Getting the Origin Type from A Journal Entry - Solution

[Former Member]

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;

[Vitaliy-R]

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

[msicard]

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