1. Tables and Views
  2. PSC_PZ_COA_TMPL_ITEMS_V

PSC_PZ_COA_TMPL_ITEMS_V

Details

  • Schema: FUSION

  • Object owner: PSC_PZ

  • Object type: VIEW

Columns

Name

AGENCY_ID

TEMPLATE_ID

TEMPLATE_NAME

TEMPLATE_DESCRIPTION

TEMPLATE_ENABLED

CONDITION_ID

TMPL_COND_ENABLED

CONDITION_TYPE

CATEGORY_ID

CATEGORY_NAME

SUBCATEGORY_ID

SUBCATEGORY_NAME

CLASSIFICATION

COND_ENABLED_FLAG

PRINT_SEQ

CONDITION

CONDITION_PLAIN

COMPL_DETAILS

Query

SQL_Statement

SELECT

L.AGENCY_ID AGENCY_ID,

L.TEMPLATE_ID TEMPLATE_ID,

L.NAME TEMPLATE_NAME,

L.DESCRIPTION TEMPLATE_DESCRIPTION,

L.ENABLED_FLAG TEMPLATE_ENABLED,

I.CONDITION_ID CONDITION_ID,

I.ENABLED_FLAG TMPL_COND_ENABLED,

LOOKUP.MEANING CONDITION_TYPE,

C.CATEGORY_ID CATEGORY_ID,

CAT.NAME CATEGORY_NAME,

C.SUBCATEGORY_ID SUBCATEGORY_ID,

SUBCAT.NAME SUBCATEGORY_NAME,

C.CLASSIFICATION CLASSIFICATION,

C.ENABLED_FLAG COND_ENABLED_FLAG,

I.PRINT_SEQ PRINT_SEQ,

C.CONDITION CONDITION,

C.CONDITION_PLAIN CONDITION_PLAIN,

case substr(COMPL.COMPL_DETAIL,1, length(COMPL.COMPL_DETAIL)-2)

when '{,}' then ' '

else

substr(COMPL.COMPL_DETAIL,1, length(COMPL.COMPL_DETAIL)-2)

end

FROM

PSC_PZ_COA_TMPL_VL L,

PSC_PZ_COA_TMPL_ITEMS I,

PSC_PZ_COA_COND_VL C

left outer join PSC_PZ_COA_CAT_VL CAT on (C.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 = C.SUBCATEGORY_ID),

PSC_LOOKUPS LOOKUP,

(select c.condition_id CONDITION_ID

,'{' || LISTAGG(CY.COMPLY_PRD_ID || ','||COMPVL.NAME|| '},{') WITHIN GROUP (ORDER BY CY.COMPLY_PRD_ID || ','||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 and cy.enabled_flag='Y')

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

L.AGENCY_ID=I.AGENCY_ID AND L.TEMPLATE_ID=I.TEMPLATE_ID

AND I.CONDITION_ID=C.CONDITION_ID

AND LOOKUP.LOOKUP_TYPE ='ORA_PSC_PZ_COA_COND'

AND LOOKUP.LOOKUP_CODE=C.CONDITION_TYPE

AND C.CONDITION_ID=COMPL.CONDITION_ID

order by L.template_id,I.condition_id