View: FND_IMP_PISUMMARY_VL

Product:	FND - Application Object Library
Description:	FND_IMP_PISUMMARY_VL
Implementation/DBA Data:	APPS.FND_IMP_PISUMMARY_VL

View Text

SELECT A.BUG_NO
, A.SNAPSHOT_ID
, A.NEW
, A.UPGRADE
, A.OVERWRITE
, A.NOT_APPLIED
, A.NEW+A.UPGRADE+A.OVERWRITE INSTALL_CNT
, A.NEW+A.UPGRADE+A.OVERWRITE+A.NOT_APPLIED FILES_CNT
, D.AFFECTED
, B.APP_CNT
, C.RESP_CNT
, C.PATH_CNT
, A.IS_FLAGGED_FILE
, A.PATCH_ID
FROM (SELECT BUG_NO
, SNAPSHOT_ID
, SUM(DECODE(TYPEID
, 'NEW'
, 1
, 0)) NEW
, SUM(DECODE(TYPEID
, 'UPGRADE'
, 1
, 0)) UPGRADE
, SUM(DECODE(TYPEID
, 'OVERWRITE'
, 1
, 0)) OVERWRITE
, SUM(DECODE(TYPEID
, 'NOT APPLIED'
, 1
, 0)) NOT_APPLIED
, SUM(DECODE(TYPEID || IS_FLAGGED_FILE
, 'UPGRADEY'
, 1
, 'OVERWRITEY'
, 1
, 0)) IS_FLAGGED_FILE
, PATCH_ID
FROM FND_IMP_PSMASTER2 P GROUP BY BUG_NO
, PATCH_ID
, SNAPSHOT_ID) A
, (SELECT BUG_NO
, SNAPSHOT_ID
, COUNT(DISTINCT(DEP_FILENAME)) AFFECTED
, PATCH_ID
FROM FND_IMP_AFFECTEDFILES GROUP BY BUG_NO
, PATCH_ID
, SNAPSHOT_ID) D
, (SELECT BUG_NO
, SNAPSHOT_ID
, COUNT(UNIQUE(APP_SHORT_NAME)) APP_CNT
, PATCH_ID
FROM FND_IMP_PSMASTER2 P
WHERE TYPEID IN ('NEW'
, 'UPGRADE'
, 'OVERWRITE') GROUP BY BUG_NO
, PATCH_ID
, SNAPSHOT_ID) B
, (SELECT BUG_NO
, SNAPSHOT_ID
, COUNT(DISTINCT(RESPONSIBILITY_ID)) RESP_CNT
, COUNT(*) PATH_CNT
, PATCH_ID
FROM FND_IMP_MENU_DEP_SUMMARY2 GROUP BY BUG_NO
, PATCH_ID
, SNAPSHOT_ID ) C
WHERE A.BUG_NO = B.BUG_NO (+)
AND (A.BUG_NO = C.BUG_NO OR C.BUG_NO IS NULL)
AND A.PATCH_ID = B.PATCH_ID(+)
AND A.PATCH_ID = C.PATCH_ID(+)
AND A.SNAPSHOT_ID = C.SNAPSHOT_ID (+)
AND A.SNAPSHOT_ID = B.SNAPSHOT_ID (+)
AND A.BUG_NO = D.BUG_NO (+)
AND A.PATCH_ID = D.PATCH_ID(+)
AND A.SNAPSHOT_ID = D.SNAPSHOT_ID (+)

Columns