VIEW: APPS.FND_IMP_PISUMMARY_VL

Source

View Text - Preformatted

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 (+)

View Text - HTML Formatted

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 (+)