View slot9.WORLD.TSTA.JS_CIVIL_PROCESS_PARTY_VW
Legend:
Primary key columns
Columns with indexes
Implied relationships
Excluded column relationships
< n > number of related tables
 
Column Type Size Nulls Auto Default Children Parents Comments
CP_ID number 0  √  null
CP_DEL_LOG.CP_ID Implied Constraint R
CP_TYPE_CODE varchar2 30  √  null
CP_TYPE_DESC varchar2 4000  √  null
EVENT_TYPE_CODE varchar2 30  √  null
DISPATCH_EVENT_TYPES.EVENT_TYPE_CODE Implied Constraint R
EVENT_TYPE_DESC varchar2 4000  √  null
AGENCY_CODE varchar2 30  √  null
AGENCY_CODES.AGENCY_CODE Implied Constraint R
AGENCY_DESC varchar2 40  √  null
COUNTY_CODE varchar2 30  √  null
COUNTY_DESC varchar2 4000  √  null
PLAINTIFF varchar2 100  √  null
REFERENCES varchar2 4000  √  null
FILED_DATE date 7  √  null
RECIEVED_DATE date 7  √  null
STATE_CODE varchar2 30  √  null
STATE_DESC varchar2 4000  √  null
AGENCY_ONLY varchar2 1  √  null
PARTY_ID number 38  √  null
PARTY_TYPE varchar2 12  √  null
PARTY_SUMMARY varchar2 4000  √  null
PARTY_STATUS_CODE varchar2 30  √  null
PARTY_STATUS_DESC varchar2 4000  √  null
CP_FEE_ID number 0  √  null
ATTEMPTS number 5  √  null
BILLABLE_ATTEMPTS number 5  √  null
MILEAGE number 5  √  null
MILEAGE_FEE number 12,2  √  null
SERVICE_FEE number 12,2  √  null
MISC_FEE number 12,2  √  null
FEE_COMMENT varchar2 1024  √  null
IS_BILLABLE varchar2 1  √  null
TOTAL_FEES number 0  √  null
LOG_NUM number 0  √  null
COURT_PAPER_LOGS.LOG_NUM Implied Constraint R
SERVE_DATE date 7  √  null
SERVING_PERSON varchar2 100  √  null
PERSON_SERVED varchar2 100  √  null
SERVE_LOCATION varchar2 100  √  null
STATUS_CODE varchar2 30  √  null
STATUS_DESC varchar2 4000  √  null
LOG_COMMENT varchar2 4000  √  null
ROLES_CODES varchar2 4000  √  null
ROLES_DESC varchar2 4000  √  null

Analyzed at Mon Sep 20 21:05 MDT 2021

View Definition:
SELECT CP.CP_ID ,CP.CP_TYPE_CODE ,(SELECT TYP.DESCRIPTION FROM EJS_CODES TYP WHERE TYP.CODE_TYPE = CP.CP_TYPE_CODE_TYPE AND TYP.CODE = CP.CP_TYPE_CODE) ,CP.EVENT_TYPE_CODE ,(SELECT EVT.DESCRIPTION FROM EJS_CODES EVT WHERE EVT.CODE_TYPE = CP.EVENT_TYPE_CODE_TYPE AND EVT.CODE = CP.EVENT_TYPE_CODE) ,CP.AGENCY_CODE ,(SELECT AGENCY_DESC FROM AGENCY_CODES WHERE AGENCY_CODE = CP.AGENCY_CODE) ,CP.COUNTY_CODE ,(SELECT CNTY.DESCRIPTION FROM EJS_CODES CNTY WHERE CNTY.CODE_TYPE = CP.COUNTY_CODE_TYPE AND CNTY.CODE = CP.COUNTY_CODE) ,CP.PLAINTIFF ,JOIN(CURSOR(SELECT REFERENCE_NUM||' - '||RF.DESCRIPTION FROM COURT_PAPER_REFS CPR, EJS_CODES RF WHERE RF.CODE_TYPE = CPR.REFERENCE_TYPE_CODE_TYPE AND RF.CODE = CPR.REFERENCE_TYPE_CODE AND CPR.CP_ID = CP.CP_ID),', ') ,CP.FILED_DATE ,CP.RECIEVED_DATE ,CP.STATE_CODE ,(SELECT ST.DESCRIPTION FROM EJS_CODES ST WHERE ST.CODE_TYPE = CP.STATE_CODE_TYPE AND ST.CODE = CP.STATE_CODE) ,CP.AGENCY_ONLY ,CPP.PERSON_ID ,'PERSON' ,CASE WHEN CPP.PERSON_ID IS NOT NULL THEN EJS_PERSON_PKG.GET_PERSON_SUMMARY(CPP.PERSON_ID) ELSE NULL END ,CPSD.CP_STATUS_CODE ,(SELECT STATP.DESCRIPTION FROM EJS_CODES STATP WHERE STATP.CODE_TYPE = CPSD.CP_STATUS_CODE_TYPE AND STATP.CODE = CPSD.CP_STATUS_CODE) ,CPF.CP_FEE_ID ,CPF.ATTEMPTS ,CPF.BILLABLE_ATTEMPTS ,CPF.MILEAGE ,CPF.MILEAGE_FEE ,CPF.SERVICE_FEE ,CPF.MISC_FEE ,CPF.FEE_COMMENT ,CPF.IS_BILLABLE ,NVL(CPF.MILEAGE_FEE,0) + NVL(CPF.SERVICE_FEE,0) + NVL(CPF.MISC_FEE,0) ,CPL.LOG_NUM ,CPL.SERVE_DATE ,CPL.SERVING_PERSON ,CPL.PERSON_SERVED ,CPL.SERVE_LOCATION ,CPL.CP_STATUS_CODE ,(SELECT STAT.DESCRIPTION FROM EJS_CODES STAT WHERE STAT.CODE_TYPE = CPL.CP_STATUS_CODE_TYPE AND STAT.CODE = CPL.CP_STATUS_CODE) ,CPL.LOG_COMMENT ,(SELECT STRAGG(CPPR.CPP_ROLE_CODE) FROM COURT_PAPER_PARTY_ROLES CPPR WHERE CPPR.CP_PARTY_ID = CPP.CP_PARTY_ID ) ,(SELECT STRAGG(EC.DESCRIPTION) FROM EJS_CODES EC INNER JOIN COURT_PAPER_PARTY_ROLES CPPR ON EC.CODE_TYPE = CPPR.CPP_ROLE_CODE_TYPE AND EC.CODE = CPPR.CPP_ROLE_CODE WHERE CPPR.CP_PARTY_ID = CPP.CP_PARTY_ID ) FROM COURT_PAPERS CP INNER JOIN COURT_PAPER_PARTIES CPP ON CP.CP_ID = CPP.CP_ID AND CPP.PERSON_ID IS NOT NULL LEFT OUTER JOIN COURT_PAPER_SERVICE_DETAILS CPSD ON CPSD.CP_PARTY_ID = CPP.CP_PARTY_ID LEFT OUTER JOIN COURT_PAPER_FEES CPF ON CPP.CP_ID = CPF.CP_ID AND CPSD.CP_FEE_ID = CPF.CP_FEE_ID LEFT OUTER JOIN COURT_PAPER_LOGS CPL ON CPP.CP_ID = CPL.CP_ID AND CPP.PERSON_ID = CPL.CP_PERSON_ID WHERE (LOG_NUM IS NULL OR LOG_NUM = (SELECT MAX(L.LOG_NUM) FROM COURT_PAPER_LOGS L WHERE L.CP_ID = CP.CP_ID)) UNION SELECT CP.CP_ID ,CP.CP_TYPE_CODE ,(SELECT TYP.DESCRIPTION FROM EJS_CODES TYP WHERE TYP.CODE_TYPE = CP.CP_TYPE_CODE_TYPE AND TYP.CODE = CP.CP_TYPE_CODE) ,CP.EVENT_TYPE_CODE ,(SELECT EVT.DESCRIPTION FROM EJS_CODES EVT WHERE EVT.CODE_TYPE = CP.EVENT_TYPE_CODE_TYPE AND EVT.CODE = CP.EVENT_TYPE_CODE) ,CP.AGENCY_CODE ,(SELECT AGENCY_DESC FROM AGENCY_CODES WHERE AGENCY_CODE = CP.AGENCY_CODE) ,CP.COUNTY_CODE ,(SELECT CNTY.DESCRIPTION FROM EJS_CODES CNTY WHERE CNTY.CODE_TYPE = CP.COUNTY_CODE_TYPE AND CNTY.CODE = CP.COUNTY_CODE) ,CP.PLAINTIFF ,JOIN(CURSOR(SELECT REFERENCE_NUM||' - '||RF.DESCRIPTION FROM COURT_PAPER_REFS CPR, EJS_CODES RF WHERE RF.CODE_TYPE = CPR.REFERENCE_TYPE_CODE_TYPE AND RF.CODE = CPR.REFERENCE_TYPE_CODE AND CPR.CP_ID = CP.CP_ID),', ') ,CP.FILED_DATE ,CP.RECIEVED_DATE ,CP.STATE_CODE ,(SELECT ST.DESCRIPTION FROM EJS_CODES ST WHERE ST.CODE_TYPE = CP.STATE_CODE_TYPE AND ST.CODE = CP.STATE_CODE) ,CP.AGENCY_ONLY ,CPB.BUSINESS_NUMBER ,'ORGANIZATION' ,(SELECT BUSINESS_NAME FROM BUSINESSES B WHERE BUSINESS_NUMBER = CPB.BUSINESS_NUMBER) ,CPSD.CP_STATUS_CODE ,(SELECT STATB.DESCRIPTION FROM EJS_CODES STATB WHERE STATB.CODE_TYPE = CPSD.CP_STATUS_CODE_TYPE AND STATB.CODE = CPSD.CP_STATUS_CODE) ,CPF.CP_FEE_ID ,CPF.ATTEMPTS ,CPF.BILLABLE_ATTEMPTS ,CPF.MILEAGE ,CPF.MILEAGE_FEE ,CPF.SERVICE_FEE ,CPF.MISC_FEE ,CPF.FEE_COMMENT ,CPF.IS_BILLABLE ,NVL(CPF.MILEAGE_FEE,0) + NVL(CPF.SERVICE_FEE,0) + NVL(CPF.MISC_FEE,0) ,CPL.LOG_NUM ,CPL.SERVE_DATE ,CPL.SERVING_PERSON ,CPL.PERSON_SERVED ,CPL.SERVE_LOCATION ,CPL.CP_STATUS_CODE ,(SELECT STAT.DESCRIPTION FROM EJS_CODES STAT WHERE STAT.CODE_TYPE = CPL.CP_STATUS_CODE_TYPE AND STAT.CODE = CPL.CP_STATUS_CODE) ,LOG_COMMENT ,(SELECT STRAGG(CPPR.CPP_ROLE_CODE) FROM COURT_PAPER_PARTY_ROLES CPPR WHERE CPPR.CP_PARTY_ID = CPB.CP_PARTY_ID ) ,(SELECT STRAGG(EC.DESCRIPTION) FROM EJS_CODES EC INNER JOIN COURT_PAPER_PARTY_ROLES CPPR ON EC.CODE_TYPE = CPPR.CPP_ROLE_CODE_TYPE AND EC.CODE = CPPR.CPP_ROLE_CODE WHERE CPPR.CP_PARTY_ID = CPB.CP_PARTY_ID ) FROM COURT_PAPERS CP INNER JOIN COURT_PAPER_PARTIES CPB ON CP.CP_ID = CPB.CP_ID AND CPB.BUSINESS_NUMBER IS NOT NULL LEFT OUTER JOIN COURT_PAPER_SERVICE_DETAILS CPSD ON CPSD.CP_PARTY_ID = CPB.CP_PARTY_ID LEFT OUTER JOIN COURT_PAPER_FEES CPF ON CPB.CP_ID = CPF.CP_ID AND CPSD.CP_FEE_ID = CPF.CP_FEE_ID LEFT OUTER JOIN COURT_PAPER_LOGS CPL ON CPB.CP_ID = CPL.CP_ID AND CPB.BUSINESS_NUMBER = CPL.CP_BUSINESS_ID WHERE (CPL.LOG_NUM IS NULL OR CPL.LOG_NUM = (SELECT MAX(L.LOG_NUM) FROM COURT_PAPER_LOGS L WHERE L.CP_ID = CP.CP_ID))
 
Possibly Referenced Tables/Views:
AGENCY_CODES BUSINESSES COURT_PAPER_FEES COURT_PAPER_LOGS COURT_PAPER_PARTIES COURT_PAPER_PARTY_ROLES COURT_PAPER_REFS COURT_PAPER_SERVICE_DETAILS COURT_PAPERS EJS_CODES 


Close relationships: