Gen PCT-National Case Details

-- PCT Details

SELECT P.CASEREFERENCE AS PCTCase, C.CASEREFERENCE AS NationalCase, PCT.EVENTNUMBER AS "PCT filing number", PCT.EVENTDATE AS "PCT filing date",
FLG.EVENTNUMBER AS "Filing Number", FLG.EVENTDATE AS "Filing Date",
DEPL.EVENTNUMBER AS "Local filing Number", DEPL.EVENTDATE AS "Local filing Date",
DES.EVENTNUMBER AS "Designated Number", DES.EVENTDATE AS "Designated Date"
FROM CASES P 
LEFT JOIN CEIRELATION CR ON CR.PARENTKEY = P.CASEKEY AND CR.RELATIONTYPE = 105
    LEFT JOIN CASEEVENT PCT ON P.CASEKEY = PCT.CASEKEY AND PCT.EVENTKEY = 10
LEFT JOIN CASES C ON CR.CASEKEY = C.CASEKEY 
    LEFT JOIN CASEEVENT FLG ON FLG.CASEKEY = C.CASEKEY AND FLG.EVENTKEY = 10
    LEFT JOIN CASEEVENT DEPL ON DEPL.CASEKEY = C.CASEKEY AND DEPL.EVENTKEY = 110
    LEFT JOIN CASEEVENT DES ON DES.CASEKEY = C.CASEKEY AND DES.EVENTKEY = 103
WHERE P.CASEREFERENCE LIKE '%WO%' 
ORDER BY 1,2