Retrieve Event Mapping configuration

event-mapping.sql

SELECT B.PTCS_SERVICEID
,C.PORTAL_OBJNAME
,C.PORTAL_URI_SEG1 AS MENUNAME
,C.PORTAL_URI_SEG2 AS PNLGRPNAME
,C.PORTAL_URI_SEG3 AS MARKET
,B.PTCS_INSTANCELBL
,NVL((SELECT X.XLATLONGNAME
      FROM PSXLATITEM X
      WHERE X.FIELDNAME = 'PTCS_ISCOMPSERVICE'
      AND X.FIELDVALUE = B.PTCS_ISCOMPSERVICE
      AND X.EFFDT = (SELECT MAX(X1.EFFDT)
                     FROM PSXLATITEM X1
                     WHERE X1.FIELDNAME = X.FIELDNAME
                     AND X1.FIELDVALUE = X.FIELDVALUE
                     AND X1.EFFDT <= TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD')
                     )),' ') AS EVENT_MAPPING_TYPE
,B.PTCS_URLTARGET
,B.RECNAME
,B.PTCS_CMPEVENT
,B.PTCS_PROCSEQ
,B.PTCS_CMPRECEVENT
,A.PACKAGEROOT || CASE WHEN A.QUALIFYPATH = ':' THEN ':' ELSE ':' || A.QUALIFYPATH || ':' END || A.APPCLASSID AS CLASSNAME
,A.PACKAGEROOT
,A.QUALIFYPATH
,A.APPCLASSID
FROM PSPTCSSRVDEFN A
,PSPTCSSRVCONF B
,PSPRSMDEFN C
WHERE A.PTCS_SERVICEURLTYP = 'UAPC'
AND B.PORTAL_NAME = '_PTCS_PTEVMAP'
AND B.PTCS_SERVICEID = A.PTCS_SERVICEID
AND C.PORTAL_NAME = 'EMPLOYEE'
AND C.PORTAL_OBJNAME = B.PORTAL_OBJNAME