Function to convert SAP R/3 material master maintenance status

I have lately work with SAP R/3 material master data creating reports and analysing data in Oracle database. One attribute that the end users are very interested is maintenance status. This attribute basically tells in witch departments the material is extended.

The end users of course like see "human" readable format of this data. I did need figure out how convert easily the maintenance status letters to descriptions.

For this I did create table to hold the maintenance status and descriptions. Next very simple function to fetch and return values. Inside my function I did use APEX_UTIL.TABLE_TO_STRING function.

REM Table to hold maintenance status and description
CREATE TABLE PSTAT_DESC(
  PSTAT       VARCHAR2(1 BYTE) NOT NULL ENABLE,
  DESCRIPTION VARCHAR2(80 BYTE) NOT NULL ENABLE,
  CONSTRAINT PSTAT_DESC_PK PRIMARY KEY (PSTAT) ENABLE
) ORGANIZATION INDEX;

REM Insert  data
Insert into PSTAT_DESC (PSTAT,DESCRIPTION) values ('A','Work scheduling');
Insert into PSTAT_DESC (PSTAT,DESCRIPTION) values ('B','Accounting');
Insert into PSTAT_DESC (PSTAT,DESCRIPTION) values ('C','Classification');
Insert into PSTAT_DESC (PSTAT,DESCRIPTION) values ('D','MRP');
Insert into PSTAT_DESC (PSTAT,DESCRIPTION) values ('E','Purchasing');
Insert into PSTAT_DESC (PSTAT,DESCRIPTION) values ('F','Production resources/tools');
Insert into PSTAT_DESC (PSTAT,DESCRIPTION) values ('G','Costing');
Insert into PSTAT_DESC (PSTAT,DESCRIPTION) values ('K','Basic data');
Insert into PSTAT_DESC (PSTAT,DESCRIPTION) values ('L','Storage');
Insert into PSTAT_DESC (PSTAT,DESCRIPTION) values ('P','Forecasting');
Insert into PSTAT_DESC (PSTAT,DESCRIPTION) values ('Q','Quality management');
Insert into PSTAT_DESC (PSTAT,DESCRIPTION) values ('S','Warehouse management');
Insert into PSTAT_DESC (PSTAT,DESCRIPTION) values ('V','Sales');
Insert into PSTAT_DESC (PSTAT,DESCRIPTION) values ('X','Plant stocks');
Insert into PSTAT_DESC (PSTAT,DESCRIPTION) values ('Z','Storage location stocks');
COMMIT;

REM Compile package
create or replace
PACKAGE PSTAT_UTIL AS 
--------------------------------------------------------------------------------
  FUNCTION pstat_to_desc(
    p_pstat IN VARCHAR2,
    p_sep   IN VARCHAR2 DEFAULT ':'
  ) RETURN VARCHAR2;
--------------------------------------------------------------------------------
  FUNCTION pstat_to_table(
    p_pstat IN VARCHAR2
  ) RETURN APEX_APPLICATION_GLOBAL.vc_arr2;
--------------------------------------------------------------------------------
END;
/
create or replace
PACKAGE BODY PSTAT_UTIL AS
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
  FUNCTION pstat_to_desc(
    p_pstat IN VARCHAR2,
    p_sep   IN VARCHAR2 DEFAULT ':'
  ) RETURN VARCHAR2
  AS
    l_arr APEX_APPLICATION_GLOBAL.vc_arr2;
  BEGIN
  --
    l_arr := pstat_to_table(p_pstat);
    --
    RETURN APEX_UTIL.TABLE_TO_STRING(l_arr, p_sep);
  --
  END;
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
  FUNCTION pstat_to_table(
    p_pstat IN VARCHAR2
  ) RETURN APEX_APPLICATION_GLOBAL.vc_arr2
  AS
    l_tmp VARCHAR2(1);
    l_len INTEGER;
    l_arr APEX_APPLICATION_GLOBAL.vc_arr2;
  BEGIN
  --
    l_len :=  LENGTH(p_pstat);
    --
    FOR i IN 1 .. l_len
    LOOP
    --
      l_tmp := NULL;
      l_tmp := SUBSTR(p_pstat,i,1);
      BEGIN
        SELECT description
        INTO l_arr(i)
        FROM pstat_desc
        WHERE pstat = l_tmp
        ;
      EXCEPTION WHEN NO_DATA_FOUND THEN
        l_arr(i) := '* Value "' || l_tmp || '" not found *';
      END;
    END LOOP;
    --
    RETURN l_arr;
  --
  END;
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
END;
/

This is very good example that APEX API:s are useful, even you are not creating APEX application.

Here is example query using this function