Order Management - System Parameters

Oracle Release

12.1.3

 

Setup : System Parameters

 

SELECT HAOU.NAME "Operating Unit"

       ,OSPDT.NAME "Parameter"

       ,OSPA.PARAMETER_VALUE "Value"

FROM   APPS.OE_SYS_PARAMETERS_ALL   OSPA

      ,APPS.OE_SYS_PARAMETER_DEF_TL OSPDT

      ,APPS.HR_ALL_ORGANIZATION_UNITS HAOU

WHERE  1 = 1

AND    OSPA.PARAMETER_CODE = OSPDT.PARAMETER_CODE

AND    OSPDT.LANGUAGE = 'US'

AND    HAOU.ORGANIZATION_ID (+) = OSPA.ORG_ID

AND    HAOU.NAME IN (

SELECT DISTINCT NVL(PARAM_LIST, NAME)

  FROM (SELECT TRIM(REGEXP_SUBSTR(PARAMETER, '[^,]+', 1, LEVEL)) PARAM_LIST, NULL

          FROM (SELECT TRIM(DECODE(:OPERATING_UNITS, 'ALL', NULL,:OPERATING_UNITS) )PARAMETER FROM DUAL) T

        CONNECT BY REGEXP_SUBSTR(PARAMETER, '[^,]+', 1, LEVEL) IS NOT NULL) PARAM_TBL,

       (SELECT NULL, NAME FROM APPS.HR_ALL_ORGANIZATION_UNITS) BASE_TBL)

ORDER  BY HAOU.NAME

         ,OSPDT.NAME

 

Filters

NAME	POSSIBLE VALUE	FOR ALL VALUES
: OPERATING_UNITS	Comma separated list, enclosed in single quotes for e.g. ‘OU1, OU2’	NULL

 

DB Access

OWNER	OBJECT NAME	OBJECT TYPE	PRIVILEGE
ONT	OE_SYS_PARAMETERS_ALL	TABLE	SELECT
ONT	OE_SYS_PARAMETER_DEF_TL	TABLE	SELECT
HR	HR_ALL_ORGANIZATION_UNITS	TABLE	SELECT

 

Enhancement

Please help answer the two questions below –

·         Did you use this SQL in anyone of the listed EBS releases, if not mention the release you tried it on?

·         Was any information missing or incorrect from that available on front-end of your release? Please specify release # and missing information details.

 

Piyush Ohri

 

“Clouds come floating into my life, no longer to carry rain or usher storm, but to add color to my sunset sky.” - Rabindranath Tagore