Warm tip: This article is reproduced from serverfault.com, please click

Convert PRPS POSID to formatted WBS Element out of SAP

发布于 2019-04-08 08:46:40

I am looking a way for converting the POSID column from the PRPS table into a formatted WBS Element like SAP show us without SAP functions.

I have found the TCJED table to get masks and I've seen an ABAP function to made the conversion but I'm not comfortable with ABAP.

Does someone know how to use the masks from TCJED table ?

Sometimes there are some 0 at the end of POSID but they don't appear on the formatted WBS Element.

I want to do this in Java or in SQL.

[Edit 1 year & 1 month later :) ]

I found the solution, here is the PL/SQL code :

mask come from tcjed table. elementtoconvert is a raw WBS or raw Proj.

if (trim(mask) = '' or mask is null or trim(elementtoconvert) ='' or elementtoconvert is null) then 
            return elementToConvert;
        end if;
        select substring(elementToConvert, 1, currentPos) into wbs_Element_formated;
        select currentPos + 1 into currentPos;

        select string_to_array(substring(mask, 2, length(mask)-1),'-') into parts;

        for i in 1 .. array_upper(parts,1) loop
            select parts[i] like 'X%' into isXGroup;

            if (currentPos > length(elementToConvert)) then
                exit;
            end if;

            select currentPos + length(parts[i]) into endIndex;
            if (endIndex > length(elementToConvert)) then 
                select regexp_replace(substring(elementToConvert, currentPos), E'\\s+', '') into groupValue;
            else
                select regexp_replace(substring(elementToConvert, currentPos, length(parts[i])), E'\\s+', '') into groupValue;
            end if;

            if (isXGroup and length(parts[i]) > length(groupValue)) then
                select groupValue || Repeat(' ', length(parts[i]) - length(groupValue)) into groupValue;        
            end if;

            select currentPos + length(parts[i]) into currentPos;   

            if isXGroup = false then 
                if trim(groupValue) = '' then
                    continue;                       
                end if;
                if (groupValue ~* ('0{' || length(parts[i]) ||'}')) then
                    if(parts[array_upper(parts,1) -1] = parts[i]) then 
                        exit;
                    end if;
                    select trim(substring(elementToConvert, currentPos)) into nextChars;
                    if(nextChars = '' or nextChars ~* ('0{' || length(parts[i]) ||'}')) then
                        exit;
                    end if;
                end if;
            end if;
            select wbs_Element_formated || '-' || groupValue into wbs_Element_formated;
        end loop;

        loop
            Exit when regexp_replace(wbs_Element_formated, E'\\s+$', '') not like '%-';
            select substring(wbs_Element_formated,1, length(wbs_Element_formated)-1)  into wbs_Element_formated;
        end loop;

        return trim(trailing ' ' from wbs_Element_formated);
Questioner
yoann
Viewed
0
yoann 2021-01-22 00:19:01

I found the solution, here is the PL/SQL code :

mask come from tcjed table. elementtoconvert is a raw WBS or raw Proj.

if (trim(mask) = '' or mask is null or trim(elementtoconvert) ='' or elementtoconvert is null) then 
            return elementToConvert;
        end if;
        select substring(elementToConvert, 1, currentPos) into wbs_Element_formated;
        select currentPos + 1 into currentPos;
        
        select string_to_array(substring(mask, 2, length(mask)-1),'-') into parts;
    
        for i in 1 .. array_upper(parts,1) loop
            select parts[i] like 'X%' into isXGroup;
            
            if (currentPos > length(elementToConvert)) then
                exit;
            end if;
        
            select currentPos + length(parts[i]) into endIndex;
            if (endIndex > length(elementToConvert)) then 
                select regexp_replace(substring(elementToConvert, currentPos), E'\\s+', '') into groupValue;
            else
                select regexp_replace(substring(elementToConvert, currentPos, length(parts[i])), E'\\s+', '') into groupValue;
            end if;
            
            if (isXGroup and length(parts[i]) > length(groupValue)) then
                select groupValue || Repeat(' ', length(parts[i]) - length(groupValue)) into groupValue;        
            end if;
            
            select currentPos + length(parts[i]) into currentPos;   
        
            if isXGroup = false then 
                if trim(groupValue) = '' then
                    continue;                       
                end if;
                if (groupValue ~* ('0{' || length(parts[i]) ||'}')) then
                    if(parts[array_upper(parts,1) -1] = parts[i]) then 
                        exit;
                    end if;
                    select trim(substring(elementToConvert, currentPos)) into nextChars;
                    if(nextChars = '' or nextChars ~* ('0{' || length(parts[i]) ||'}')) then
                        exit;
                    end if;
                end if;
            end if;
            select wbs_Element_formated || '-' || groupValue into wbs_Element_formated;
        end loop;
        
        loop
            Exit when regexp_replace(wbs_Element_formated, E'\\s+$', '') not like '%-';
            select substring(wbs_Element_formated,1, length(wbs_Element_formated)-1)  into wbs_Element_formated;
        end loop;
        
        return trim(trailing ' ' from wbs_Element_formated);