PSC_PZ_COA_COND_ITEMS

Details

Schema: FUSION
Object owner: PSC_PZ
Object type: VIEW

Columns

Name
TRANSACTION_KEY AGENCY_ID CONDITION_ID CONDITION CONDITION_PLAIN CATEGORY_NAME CATEGORY_ID SUBCATEGORY_NAME SUBCATEGORY_ID CLASSIFICATION CONDITION_TYPE CONDITION_TYPE_ID COMPL_DETAIL COMPL_DETAILID

Name

TRANSACTION_KEY

AGENCY_ID

CONDITION_ID

CONDITION

CONDITION_PLAIN

CATEGORY_NAME

CATEGORY_ID

SUBCATEGORY_NAME

SUBCATEGORY_ID

CLASSIFICATION

CONDITION_TYPE

CONDITION_TYPE_ID

COMPL_DETAIL

COMPL_DETAILID

Query

SQL_Statement
SELECT COND.AGENCY_ID \|\| '_'\|\|COND.CONDITION_ID TRANSACTION_KEY, COND.AGENCY_ID AGENCY_ID, COND.CONDITION_ID CONDITION_ID, COND.CONDITION CONDITION, COND.CONDITION_PLAIN CONDITION_PLAIN, CAT.NAME CATEGORY_NAME, CAT.CATEGORY_ID CATEGORY_ID, SUBCAT.NAME SUBCATEGORY_NAME, SUBCAT.SUBCATEGORY_ID SUBCATEGORY_ID, COND.CLASSIFICATION CLASSIFICATION, LOOKUP.MEANING CONDITION_TYPE, LOOKUP.LOOKUP_CODE CONDITION_TYPE_ID, COMPL.COMPL_DETAIL COMPL_DETAIL, COMPL2.COMPL_DETAIL COMPL_DETAILID FROM PSC_PZ_COA_COND_VL COND left outer join PSC_PZ_COA_CAT_VL CAT on (COND.CATEGORY_ID= CAT.CATEGORY_ID) left outer join PSC_PZ_COA_SUBCAT_VL SUBCAT on (CAT.CATEGORY_ID=SUBCAT.CATEGORY_ID AND SUBCAT.SUBCATEGORY_ID = COND.SUBCATEGORY_ID), PSC_LOOKUPS LOOKUP, (select c.condition_id CONDITION_ID ,LISTAGG(COMPVL.COMPLY_PRD_ID,',') WITHIN GROUP (ORDER BY COMPVL.NAME ) as COMPL_DETAIL from PSC_PZ_COA_COND_VL C left outer join PSC_PZ_COA_COND_COMPLY CY on (C.CONDITION_ID=CY.CONDITION_ID AND C.AGENCY_ID=CY.AGENCY_ID) left outer join PSC_PZ_COA_COMPLY_VL COMPVL on ( COMPVL.COMPLY_PRD_ID=CY.COMPLY_PRD_ID and cy.enabled_flag='Y') group by c.condition_id) COMPL2, (select c.condition_id CONDITION_ID ,LISTAGG(COMPVL.NAME,',') WITHIN GROUP (ORDER BY COMPVL.NAME ) as COMPL_DETAIL from PSC_PZ_COA_COND_VL C left outer join PSC_PZ_COA_COND_COMPLY CY on (C.CONDITION_ID=CY.CONDITION_ID AND C.AGENCY_ID=CY.AGENCY_ID) left outer join PSC_PZ_COA_COMPLY_VL COMPVL on ( COMPVL.COMPLY_PRD_ID=CY.COMPLY_PRD_ID and cy.enabled_flag='Y') group by c.condition_id) COMPL where LOOKUP.LOOKUP_TYPE ='ORA_PSC_PZ_COA_COND' AND LOOKUP.LOOKUP_CODE=COND.CONDITION_TYPE AND COMPL.CONDITION_ID=COND.CONDITION_ID AND COMPL.CONDITION_ID=COMPL2.CONDITION_ID

SQL_Statement

SELECT

COND.AGENCY_ID || '_'||COND.CONDITION_ID TRANSACTION_KEY,

COND.AGENCY_ID AGENCY_ID,

COND.CONDITION_ID CONDITION_ID,

COND.CONDITION CONDITION,

COND.CONDITION_PLAIN CONDITION_PLAIN,

CAT.NAME CATEGORY_NAME,

CAT.CATEGORY_ID CATEGORY_ID,

SUBCAT.NAME SUBCATEGORY_NAME,

SUBCAT.SUBCATEGORY_ID SUBCATEGORY_ID,

COND.CLASSIFICATION CLASSIFICATION,

LOOKUP.MEANING CONDITION_TYPE,

LOOKUP.LOOKUP_CODE CONDITION_TYPE_ID,

COMPL.COMPL_DETAIL COMPL_DETAIL,

COMPL2.COMPL_DETAIL COMPL_DETAILID

FROM PSC_PZ_COA_COND_VL COND

left outer join PSC_PZ_COA_CAT_VL CAT on (COND.CATEGORY_ID= CAT.CATEGORY_ID)

left outer join PSC_PZ_COA_SUBCAT_VL SUBCAT on (CAT.CATEGORY_ID=SUBCAT.CATEGORY_ID AND SUBCAT.SUBCATEGORY_ID = COND.SUBCATEGORY_ID),

PSC_LOOKUPS LOOKUP,

(select c.condition_id CONDITION_ID

,LISTAGG(COMPVL.COMPLY_PRD_ID,',') WITHIN GROUP (ORDER BY COMPVL.NAME ) as COMPL_DETAIL

from

PSC_PZ_COA_COND_VL C

left outer join PSC_PZ_COA_COND_COMPLY CY on (C.CONDITION_ID=CY.CONDITION_ID AND C.AGENCY_ID=CY.AGENCY_ID)

left outer join PSC_PZ_COA_COMPLY_VL COMPVL on ( COMPVL.COMPLY_PRD_ID=CY.COMPLY_PRD_ID and cy.enabled_flag='Y')

group by c.condition_id) COMPL2,

(select c.condition_id CONDITION_ID

,LISTAGG(COMPVL.NAME,',') WITHIN GROUP (ORDER BY COMPVL.NAME ) as COMPL_DETAIL

from

PSC_PZ_COA_COND_VL C

left outer join PSC_PZ_COA_COND_COMPLY CY on (C.CONDITION_ID=CY.CONDITION_ID AND C.AGENCY_ID=CY.AGENCY_ID)

left outer join PSC_PZ_COA_COMPLY_VL COMPVL on ( COMPVL.COMPLY_PRD_ID=CY.COMPLY_PRD_ID and cy.enabled_flag='Y')

group by c.condition_id) COMPL

where LOOKUP.LOOKUP_TYPE ='ORA_PSC_PZ_COA_COND'

AND LOOKUP.LOOKUP_CODE=COND.CONDITION_TYPE

AND COMPL.CONDITION_ID=COND.CONDITION_ID

AND COMPL.CONDITION_ID=COMPL2.CONDITION_ID