VIEW: APPS.HRI_OBI_CL_JOB_FMLYFNCT_V

Source

View Text - Preformatted

SELECT DISTINCT
job.job_fmly_code || ' (' || job.job_fnctn_code || ')' fmfn_fmlyfnct_pk
,NVL(jfm.value,hri_oltp_view_message.get_unassigned_msg)
    || ' (' || NVL(jfn.value,hri_oltp_view_message.get_unassigned_msg) || ')' fmfn_fmlyfnct_name
,NVL(jfm.value,hri_oltp_view_message.get_unassigned_msg)
    || ' (' || NVL(jfn.value,hri_oltp_view_message.get_unassigned_msg) || ')' fmfn_fmlyfnct_name_unq
,job.job_fmly_code  fmfn_family_fk
,NVL(jfm.value,hri_oltp_view_message.get_unassigned_msg) fmfn_family_name
,job.job_fnctn_code  fmfn_function_fk
,NVL(jfn.value,hri_oltp_view_message.get_unassigned_msg) fmfn_function_name
,(CASE
    WHEN jfm.value IS NULL THEN
      CASE WHEN jfn.value IS NULL THEN NULL
           WHEN jfn.value = hri_oltp_view_message.get_unassigned_msg THEN NULL
           ELSE jfn.value
      END
    WHEN jfn.value IS NULL THEN
      CASE WHEN jfm.value = hri_oltp_view_message.get_unassigned_msg THEN NULL
           ELSE jfm.value
      END
    WHEN  jfm.value  = hri_oltp_view_message.get_unassigned_msg THEN
      CASE WHEN jfn.value = hri_oltp_view_message.get_unassigned_msg THEN NULL
           ELSE jfn.value
      END
    WHEN jfn.value = hri_oltp_view_message.get_unassigned_msg THEN jfm.value
    ELSE jfm.value || ' (' || jfn.value || ')'
  END) fmfn_order_by
 FROM (SELECT distinct job_fmly_code,job_fnctn_code FROM hri_cs_jobh_ct) job,
(SELECT 
id_char id
,hri_oltp_view_message.get_unassigned_msg value
,hr_general.start_of_time start_date
,hr_general.end_of_time end_date
,'2' order_by
FROM hri_unassigned) jfm,
(SELECT 
id_char id
,hri_oltp_view_message.get_unassigned_msg value
,hr_general.start_of_time start_date
,hr_general.end_of_time end_date
,'2' order_by
FROM hri_unassigned) jfn
WHERE job.job_fmly_code = jfm.id(+)
AND job.job_fnctn_code = jfn.id(+)

View Text - HTML Formatted

SELECT DISTINCT JOB.JOB_FMLY_CODE || ' (' || JOB.JOB_FNCTN_CODE || ')' FMFN_FMLYFNCT_PK
, NVL(JFM.VALUE
, HRI_OLTP_VIEW_MESSAGE.GET_UNASSIGNED_MSG) || ' (' || NVL(JFN.VALUE
, HRI_OLTP_VIEW_MESSAGE.GET_UNASSIGNED_MSG) || ')' FMFN_FMLYFNCT_NAME
, NVL(JFM.VALUE
, HRI_OLTP_VIEW_MESSAGE.GET_UNASSIGNED_MSG) || ' (' || NVL(JFN.VALUE
, HRI_OLTP_VIEW_MESSAGE.GET_UNASSIGNED_MSG) || ')' FMFN_FMLYFNCT_NAME_UNQ
, JOB.JOB_FMLY_CODE FMFN_FAMILY_FK
, NVL(JFM.VALUE
, HRI_OLTP_VIEW_MESSAGE.GET_UNASSIGNED_MSG) FMFN_FAMILY_NAME
, JOB.JOB_FNCTN_CODE FMFN_FUNCTION_FK
, NVL(JFN.VALUE
, HRI_OLTP_VIEW_MESSAGE.GET_UNASSIGNED_MSG) FMFN_FUNCTION_NAME
, (CASE WHEN JFM.VALUE IS NULL THEN CASE WHEN JFN.VALUE IS NULL THEN NULL WHEN JFN.VALUE = HRI_OLTP_VIEW_MESSAGE.GET_UNASSIGNED_MSG THEN NULL ELSE JFN.VALUE END WHEN JFN.VALUE IS NULL THEN CASE WHEN JFM.VALUE = HRI_OLTP_VIEW_MESSAGE.GET_UNASSIGNED_MSG THEN NULL ELSE JFM.VALUE END WHEN JFM.VALUE = HRI_OLTP_VIEW_MESSAGE.GET_UNASSIGNED_MSG THEN CASE WHEN JFN.VALUE = HRI_OLTP_VIEW_MESSAGE.GET_UNASSIGNED_MSG THEN NULL ELSE JFN.VALUE END WHEN JFN.VALUE = HRI_OLTP_VIEW_MESSAGE.GET_UNASSIGNED_MSG THEN JFM.VALUE ELSE JFM.VALUE || ' (' || JFN.VALUE || ')' END) FMFN_ORDER_BY
FROM (SELECT DISTINCT JOB_FMLY_CODE
, JOB_FNCTN_CODE
FROM HRI_CS_JOBH_CT) JOB
, (SELECT ID_CHAR ID
, HRI_OLTP_VIEW_MESSAGE.GET_UNASSIGNED_MSG VALUE
, HR_GENERAL.START_OF_TIME START_DATE
, HR_GENERAL.END_OF_TIME END_DATE
, '2' ORDER_BY FROM HRI_UNASSIGNED) JFM
, (SELECT ID_CHAR ID
, HRI_OLTP_VIEW_MESSAGE.GET_UNASSIGNED_MSG VALUE
, HR_GENERAL.START_OF_TIME START_DATE
, HR_GENERAL.END_OF_TIME END_DATE
, '2' ORDER_BY FROM HRI_UNASSIGNED) JFN WHERE JOB.JOB_FMLY_CODE = JFM.ID(+) AND JOB.JOB_FNCTN_CODE = JFN.ID(+)