SELECT A.OBJNAME OBJECT_NAME,(A.DELIVERED+B.CUSTOMIZED) TOTAL_OBJECTS, B.CUSTOMIZED CUSTOMIZED,A.DELIVERED DELIVERED,CASE (A.DELIVERED+B.CUSTOMIZED) WHEN 0 THEN 0 ELSE ROUND(((B.CUSTOMIZED/(A.DELIVERED+B.CUSTOMIZED))*100),2) END CUSTOM_PERCENT FROM (SELECT CASE RECTYPE WHEN 0 THEN 'SQL Record' WHEN 1 THEN 'SQL View' WHEN 2 THEN 'Derived Record' WHEN 3 THEN 'Sub Record' WHEN 4 THEN 'Stored Procedure' WHEN 5 THEN 'Dynamic View' WHEN 6 THEN 'Query View' ELSE 'Temporary Record' END AS OBJNAME,COUNT(*) AS DELIVERED FROM PSRECDEFN WHERE LASTUPDOPRID = 'PPLSOFT' GROUP BY RECTYPE UNION SELECT 'Record Field' AS OBJNAME,COUNT(*) AS DELIVERED FROM PSRECFIELD WHERE LASTUPDOPRID = 'PPLSOFT' UNION SELECT 'Fields' AS OBJNAME,COUNT(*) AS DELIVERED FROM PSDBFIELD WHERE LASTUPDOPRID = 'PPLSOFT' UNION SELECT 'Field Format' AS OBJNAME,COUNT(*) AS DELIVERED FROM PSFMTDEFN WHERE LASTUPDOPRID = 'PPLSOFT' UNION SELECT 'Translates' AS OBJNAME,COUNT(*) AS DELIVERED FROM PSXLATITEM WHERE LASTUPDOPRID = 'PPLSOFT' UNION SELECT 'Pages' AS OBJNAME,COUNT(*) AS DELIVERED FROM PSPNLDEFN WHERE LASTUPDOPRID = 'PPLSOFT' UNION SELECT 'Menu' AS OBJNAME,COUNT(*) AS DELIVERED FROM PSMENUDEFN WHERE LASTUPDOPRID = 'PPLSOFT' UNION SELECT 'Component' AS OBJNAME,COUNT(*) AS DELIVERED FROM PSPNLGRPDEFN WHERE LASTUPDOPRID = 'PPLSOFT' UNION SELECT 'PeopleCode' AS OBJNAME,COUNT(*) AS DELIVERED FROM PSPCMPROG WHERE LASTUPDOPRID = 'PPLSOFT' UNION SELECT 'PS Queries' AS OBJNAME,COUNT(*) AS DELIVERED FROM PSQRYDEFN WHERE LASTUPDOPRID = 'PPLSOFT' UNION SELECT 'Tree Structure' AS OBJNAME,COUNT(*) AS DELIVERED FROM PSTREESTRCT WHERE LASTUPDOPRID = 'PPLSOFT' UNION SELECT 'Trees' AS OBJNAME,COUNT(*) AS DELIVERED FROM PSTREEDEFN WHERE LASTUPDOPRID = 'PPLSOFT' UNION SELECT 'Style' AS OBJNAME,COUNT(*) AS DELIVERED FROM PSSTYLEDEFN WHERE LASTUPDOPRID = 'PPLSOFT' UNION SELECT 'Business Process' AS OBJNAME,COUNT(*) AS DELIVERED FROM PSBUSPROCDEFN WHERE LASTUPDOPRID = 'PPLSOFT' UNION SELECT 'Activities' AS OBJNAME,COUNT(*) AS DELIVERED FROM PSACTIVITYDEFN WHERE LASTUPDOPRID = 'PPLSOFT' UNION SELECT 'Process Definition' AS OBJNAME, COUNT(*) AS DELIVERED FROM PS_PRCSDEFN WHERE LASTUPDOPRID = 'PPLSOFT' UNION SELECT 'Process Server' AS OBJNAME,COUNT(*) AS DELIVERED FROM PS_SERVERDEFN WHERE LASTUPDOPRID = 'PPLSOFT' UNION SELECT 'Process Types' AS OBJNAME,COUNT(*) AS DELIVERED FROM PS_PRCSTYPEDEFN WHERE LASTUPDOPRID = 'PPLSOFT' UNION SELECT 'Process Jobs' AS OBJNAME,COUNT(*) AS DELIVERED FROM PS_PRCSJOBDEFN WHERE LASTUPDOPRID = 'PPLSOFT' UNION SELECT 'Process Recurrances' AS OBJNAME,COUNT(*) AS DELIVERED FROM PS_PRCSRECUR WHERE LASTUPDOPRID = 'PPLSOFT' UNION SELECT 'Process Control' AS OBJNAME,COUNT(*) AS DELIVERED FROM PS_SCHDLDEFN WHERE LASTUPDOPRID = 'PPLSOFT' UNION SELECT 'Business Interlinks' AS OBJNAME,COUNT(*) AS DELIVERED FROM PSIODEFN WHERE LASTUPDOPRID = 'PPLSOFT' UNION SELECT CASE SQLTYPE WHEN '0' THEN 'SQL Object' WHEN '1' THEN 'App. Engine SQL' WHEN '2' THEN 'Record SQL' ELSE 'Transformation SQL' END AS OBJNAME,COUNT(*) AS DELIVERED FROM PSSQLDEFN WHERE LASTUPDOPRID = 'PPLSOFT' GROUP BY SQLTYPE UNION SELECT 'File Layouts' AS OBJNAME,COUNT(*) AS DELIVERED FROM PSFLDDEFN WHERE LASTUPDOPRID = 'PPLSOFT' UNION SELECT 'Business Components' AS OBJNAME,COUNT(*) AS DELIVERED FROM PSBCDEFN WHERE LASTUPDOPRID = 'PPLSOFT' UNION SELECT 'Application Engine Program' AS OBJNAME,COUNT(*) AS DELIVERED FROM PSAEAPPLDEFN WHERE LASTUPDOPRID = 'PPLSOFT' UNION SELECT 'Application Engine Section' AS OBJNAME,COUNT(*) AS DELIVERED FROM PSAESECTDEFN WHERE LASTUPDOPRID = 'PPLSOFT' UNION SELECT 'Message Nodes' AS OBJNAME,COUNT(*) AS DELIVERED FROM PSMSGNODEDEFN WHERE LASTUPDOPRID = 'PPLSOFT' UNION SELECT 'Message Channel' AS OBJNAME,COUNT(*) AS DELIVERED FROM PSCHNLDEFN WHERE LASTUPDOPRID = 'PPLSOFT' UNION SELECT 'Message Definition' AS OBJNAME,COUNT(*) AS DELIVERED FROM PSMSGDEFN WHERE LASTUPDOPRID = 'PPLSOFT' UNION SELECT 'Approval Rule Set' AS OBJNAME,COUNT(*) AS DELIVERED FROM PS_APPR_RULE_HDR WHERE LASTUPDOPRID = 'PPLSOFT' UNION SELECT 'Image & HTML Catalog' AS OBJNAME,COUNT(*) AS DELIVERED FROM PSCONTDEFN WHERE LASTUPDOPRID = 'PPLSOFT' UNION SELECT 'Style Sheets' AS OBJNAME,COUNT(*) AS DELIVERED FROM
PSSTYLSHEETDEFN WHERE LASTUPDOPRID = 'PPLSOFT' UNION SELECT 'Subscription' AS OBJNAME,COUNT(*) AS DELIVERED FROM PSSUBDEFN WHERE LASTUPDOPRID = 'PPLSOFT' UNION SELECT 'URL Definition' AS OBJNAME,COUNT(*) AS DELIVERED FROM PSURLDEFN WHERE LASTUPDOPRID = 'PPLSOFT' UNION SELECT 'Portal Registry' AS OBJNAME,COUNT(*) AS DELIVERED FROM PSPRSMDEFN WHERE LASTUPDOPRID = 'PPLSOFT' UNION SELECT 'Portal' AS OBJNAME,COUNT(*) AS DELIVERED FROM PSPRDMDEFN WHERE LASTUPDOPRID = 'PPLSOFT' UNION SELECT 'Application Package' AS OBJNAME,COUNT(*) AS DELIVERED FROM PSPACKAGEDEFN WHERE LASTUPDOPRID = 'PPLSOFT' UNION SELECT 'Mobile Page' AS OBJNAME,COUNT(*) AS DELIVERED FROM PSMPDEFN WHERE LASTUPDOPRID = 'PPLSOFT' UNION SELECT 'Problem Definition' AS OBJNAME,COUNT(*) AS DELIVERED FROM PSOPTPRBTYPE WHERE LASTUPDOPRID = 'PPLSOFT' UNION SELECT 'User Attributes' AS OBJNAME,COUNT(*) AS DELIVERED FROM PSUSERATTR WHERE LASTUPDOPRID = 'PPLSOFT' UNION SELECT 'Roles' AS OBJNAME,COUNT(*) AS DELIVERED FROM PSROLEDEFN WHERE LASTUPDOPRID = 'PPLSOFT' UNION SELECT 'Permission List' AS OBJNAME,COUNT(*) AS DELIVERED FROM PSCLASSDEFN WHERE LASTUPDOPRID = 'PPLSOFT' ) A, (SELECT CASE RECTYPE WHEN 0 THEN 'SQL Record' WHEN 1 THEN 'SQL View' WHEN 2 THEN 'Derived Record' WHEN 3 THEN 'Sub Record' WHEN 4 THEN 'Stored Procedure' WHEN 5 THEN 'Dynamic View' WHEN 6 THEN 'Query View' ELSE 'Temporary Record' END AS OBJNAME,COUNT(*) AS CUSTOMIZED FROM PSRECDEFN WHERE LASTUPDOPRID <> 'PPLSOFT' GROUP BY RECTYPE UNION SELECT 'Record Field' AS OBJNAME,COUNT(*) AS CUSTOMIZED FROM PSRECFIELD WHERE LASTUPDOPRID <> 'PPLSOFT' UNION SELECT 'Fields' AS OBJNAME,COUNT(*) AS CUSTOMIZED FROM PSDBFIELD WHERE LASTUPDOPRID <> 'PPLSOFT' UNION SELECT 'Field Format' AS OBJNAME,COUNT(*) AS CUSTOMIZED FROM PSFMTDEFN WHERE LASTUPDOPRID <> 'PPLSOFT' UNION SELECT 'Translates' AS OBJNAME,COUNT(*) AS CUSTOMIZED FROM PSXLATITEM WHERE LASTUPDOPRID <> 'PPLSOFT' UNION SELECT 'Pages' AS OBJNAME,COUNT(*) AS CUSTOMIZED FROM PSPNLDEFN WHERE LASTUPDOPRID <> 'PPLSOFT' UNION SELECT 'Menu' AS OBJNAME,COUNT(*) AS CUSTOMIZED FROM PSMENUDEFN WHERE LASTUPDOPRID <> 'PPLSOFT' UNION SELECT 'Component' AS OBJNAME,COUNT(*) AS CUSTOMIZED FROM PSPNLGRPDEFN WHERE LASTUPDOPRID <> 'PPLSOFT' UNION SELECT 'PeopleCode' AS OBJNAME,COUNT(*) AS CUSTOMIZED FROM PSPCMPROG WHERE LASTUPDOPRID <> 'PPLSOFT' UNION SELECT 'PS Queries' AS OBJNAME,COUNT(*) AS CUSTOMIZED FROM PSQRYDEFN WHERE LASTUPDOPRID <> 'PPLSOFT' UNION SELECT 'Tree Structure' AS OBJNAME,COUNT(*) AS CUSTOMIZED FROM PSTREESTRCT WHERE LASTUPDOPRID <> 'PPLSOFT' UNION SELECT 'Trees' AS OBJNAME,COUNT(*) AS CUSTOMIZED FROM PSTREEDEFN WHERE LASTUPDOPRID <> 'PPLSOFT' UNION SELECT 'Style' AS OBJNAME,COUNT(*) AS CUSTOMIZED FROM PSSTYLEDEFN WHERE LASTUPDOPRID <> 'PPLSOFT' UNION SELECT 'Business Process' AS OBJNAME,COUNT(*) AS CUSTOMIZED FROM PSBUSPROCDEFN WHERE LASTUPDOPRID <> 'PPLSOFT' UNION SELECT 'Activities' AS OBJNAME,COUNT(*) AS CUSTOMIZED FROM PSACTIVITYDEFN WHERE LASTUPDOPRID <> 'PPLSOFT' UNION SELECT 'Process Definition' AS OBJNAME,COUNT(*) AS CUSTOMIZED FROM PS_PRCSDEFN WHERE LASTUPDOPRID <> 'PPLSOFT' UNION SELECT 'Process Server' AS OBJNAME,COUNT(*) AS CUSTOMIZED FROM PS_SERVERDEFN WHERE LASTUPDOPRID <> 'PPLSOFT' UNION SELECT 'Process Types' AS OBJNAME,COUNT(*) AS CUSTOMIZED FROM PS_PRCSTYPEDEFN WHERE LASTUPDOPRID <> 'PPLSOFT' UNION SELECT 'Process Jobs' AS OBJNAME,COUNT(*) AS CUSTOMIZED FROM PS_PRCSJOBDEFN WHERE LASTUPDOPRID <> 'PPLSOFT' UNION SELECT 'Process Recurrances' AS OBJNAME,COUNT(*) AS CUSTOMIZED FROM PS_PRCSRECUR WHERE LASTUPDOPRID <> 'PPLSOFT' UNION SELECT 'Process Control' AS OBJNAME,COUNT(*) AS CUSTOMIZED FROM PS_SCHDLDEFN WHERE LASTUPDOPRID <> 'PPLSOFT' UNION SELECT 'Business Interlinks' AS OBJNAME,COUNT(*) AS CUSTOMIZED FROM PSIODEFN WHERE LASTUPDOPRID <> 'PPLSOFT' UNION SELECT CASE SQLTYPE WHEN '0' THEN 'SQL Object' WHEN '1' THEN 'App. Engine SQL' WHEN '2' THEN 'Record SQL' ELSE 'Transformation SQL' END AS OBJNAME,COUNT(*) AS CUSTOMIZED FROM PSSQLDEFN WHERE LASTUPDOPRID <> 'PPLSOFT' GROUP BY SQLTYPE UNION SELECT 'File Layouts' AS OBJNAME,COUNT(*) AS CUSTOMIZED FROM PSFLDDEFN WHERE LASTUPDOPRID <> 'PPLSOFT' UNION SELECT 'Business Components' AS
OBJNAME,COUNT(*) AS CUSTOMIZED FROM PSBCDEFN WHERE LASTUPDOPRID <> 'PPLSOFT' UNION SELECT 'Application Engine Program' AS OBJNAME,COUNT(*) AS CUSTOMIZED FROM PSAEAPPLDEFN WHERE LASTUPDOPRID <> 'PPLSOFT' UNION SELECT 'Application Engine Section' AS OBJNAME,COUNT(*) AS CUSTOMIZED FROM PSAESECTDEFN WHERE LASTUPDOPRID <> 'PPLSOFT' UNION SELECT 'Message Nodes' AS OBJNAME,COUNT(*) AS CUSTOMIZED FROM PSMSGNODEDEFN WHERE LASTUPDOPRID <> 'PPLSOFT' UNION SELECT 'Message Channel' AS OBJNAME,COUNT(*) AS CUSTOMIZED FROM PSCHNLDEFN WHERE LASTUPDOPRID <> 'PPLSOFT' UNION SELECT 'Message Definition' AS OBJNAME,COUNT(*) AS CUSTOMIZED FROM PSMSGDEFN WHERE LASTUPDOPRID <> 'PPLSOFT' UNION SELECT 'Approval Rule Set' AS OBJNAME,COUNT(*) AS CUSTOMIZED FROM PS_APPR_RULE_HDR WHERE LASTUPDOPRID <> 'PPLSOFT' UNION SELECT 'Image & HTML Catalog' AS OBJNAME,COUNT(*) AS CUSTOMIZED FROM PSCONTDEFN WHERE LASTUPDOPRID <> 'PPLSOFT' UNION SELECT 'Style Sheets' AS OBJNAME,COUNT(*) AS CUSTOMIZED FROM PSSTYLSHEETDEFN WHERE LASTUPDOPRID <> 'PPLSOFT' UNION SELECT 'Subscription' AS OBJNAME,COUNT(*) AS CUSTOMIZED FROM PSSUBDEFN WHERE LASTUPDOPRID <> 'PPLSOFT' UNION SELECT 'URL Definition' AS OBJNAME,COUNT(*) AS CUSTOMIZED FROM PSURLDEFN WHERE LASTUPDOPRID <> 'PPLSOFT' UNION SELECT 'Portal Registry' AS OBJNAME,COUNT(*) AS CUSTOMIZED FROM PSPRSMDEFN WHERE LASTUPDOPRID <> 'PPLSOFT' UNION SELECT 'Portal' AS OBJNAME,COUNT(*) AS CUSTOMIZED FROM PSPRDMDEFN WHERE LASTUPDOPRID <> 'PPLSOFT' UNION SELECT 'Application Package' AS OBJNAME,COUNT(*) AS CUSTOMIZED FROM PSPACKAGEDEFN WHERE LASTUPDOPRID <> 'PPLSOFT' UNION SELECT 'Mobile Page' AS OBJNAME,COUNT(*) AS CUSTOMIZED FROM PSMPDEFN WHERE LASTUPDOPRID <> 'PPLSOFT' UNION SELECT 'Problem Definition' AS OBJNAME,COUNT(*) AS CUSTOMIZED FROM PSOPTPRBTYPE WHERE LASTUPDOPRID <> 'PPLSOFT' UNION SELECT 'User Attributes' AS OBJNAME,COUNT(*) AS CUSTOMIZED FROM PSUSERATTR WHERE LASTUPDOPRID <> 'PPLSOFT' UNION SELECT 'Roles' AS OBJNAME,COUNT(*) AS CUSTOMIZED FROM PSROLEDEFN WHERE LASTUPDOPRID <> 'PPLSOFT' UNION SELECT 'Permission List' AS OBJNAME,COUNT(*) AS CUSTOMIZED FROM PSCLASSDEFN WHERE LASTUPDOPRID <> 'PPLSOFT' ) B WHERE A.OBJNAME = B.OBJNAME ORDER BY A.OBJNAME
