Displays HRMS details

SELECT * FROM FND_PRODUCT_GROUPS


Family Pack

SELECT GREATEST(NVL((SELECT DECODE(BUG_NUMBER,'2968701','HRMS_PF.F') FROM ad_bugs
WHERE BUG_NUMBER = (SELECT MAX(BUG_NUMBER) FROM AD_BUGS where BUG_NUMBER = '2968701')),'A'),
(SELECT DECODE(SUBSTR(I.PATCH_LEVEL,9,1)
, 'H', 'HRMS_PF.A', 'I', 'HRMS_PF.B', 'J', 'HRMS_PF.C'
, 'K', 'HRMS_PF.D', 'L', 'HRMS_PF.E', 'M', 'HRMS_PF.G'
, 'N', 'HRMS_PF.H', 'O', 'HRMS_PF.I', 'P', 'HRMS_PF.J', 'Unknown')
FROM FND_APPLICATION_ALL_VIEW V, FND_PRODUCT_INSTALLATIONS I
WHERE V.APPLICATION_ID = I.APPLICATION_ID
AND (V.APPLICATION_ID = '800'))) hrmsPF FROM DUAL;


HR Status

SELECT L.MEANING HRStatus
FROM FND_APPLICATION_ALL_VIEW V, FND_PRODUCT_INSTALLATIONS I, FND_LOOKUPS L
WHERE (V.APPLICATION_ID = I.APPLICATION_ID)
AND (V.APPLICATION_ID = '800')
AND (L.LOOKUP_TYPE = 'FND_PRODUCT_STATUS')
AND (L.LOOKUP_CODE = I.Status);


Payroll Status

SELECT L.MEANING PayStatus
FROM FND_APPLICATION_ALL_VIEW V, FND_PRODUCT_INSTALLATIONS I, FND_LOOKUPS L
WHERE (V.APPLICATION_ID = I.APPLICATION_ID)
AND (V.APPLICATION_ID = '801')
AND (L.LOOKUP_TYPE = 'FND_PRODUCT_STATUS')
AND (L.LOOKUP_CODE = I.Status);


Applications List

SELECT V.APPLICATION_NAME app
, to_char(V.APPLICATION_ID) appId
, L.MEANING appStatus
, DECODE(I.PATCH_LEVEL, NULL, '11i.' v.APPLICATION_SHORT_NAME '.?', I.PATCH_LEVEL) patch
FROM FND_APPLICATION_ALL_VIEW V, FND_PRODUCT_INSTALLATIONS I, FND_LOOKUPS L
WHERE (V.APPLICATION_ID = I.APPLICATION_ID)
AND (V.APPLICATION_ID IN
('0', '50', '178', '275', '453', '800', '801', '802', '803', '804', '805', '808', '809', '810', '8301', '8302', '8303'))
AND (L.LOOKUP_TYPE = 'FND_PRODUCT_STATUS')
AND (L.LOOKUP_CODE = I.Status )
ORDER BY 1;


HR Global

SELECT ap.patch_name patchNumber
, 'hrglobal.drv' patchName
, pr.end_date appliedDate
FROM ad_applied_patches ap
, ad_patch_drivers pd
, ad_patch_runs pr
, ad_patch_run_bugs prb
, ad_patch_run_bug_actions prba
, ad_files f
WHERE f.file_id = prba.file_id
AND prba.executed_flag = 'Y'
AND prba.patch_run_bug_id = prb.patch_run_bug_id
AND prb.patch_run_id = pr.patch_run_id
AND pr.patch_driver_id = pd.patch_driver_id
AND pd.applied_patch_id = ap.applied_patch_id
AND f.filename = 'hrglobal.drv'
AND pr.end_date = (SELECT max(pr.end_date)
FROM ad_applied_patches ap
, ad_patch_drivers pd
, ad_patch_runs pr
, ad_patch_run_bugs prb
, ad_patch_run_bug_actions prba
, ad_files f
WHERE f.file_id = prba.file_id
AND prba.executed_flag = 'Y'
AND prba.patch_run_bug_id = prb.patch_run_bug_id
AND prb.patch_run_id = pr.patch_run_id
AND pr.patch_driver_id = pd.patch_driver_id
AND pd.applied_patch_id = ap.applied_patch_id
AND f.filename = 'hrglobal.drv');


Legislations

SELECT DECODE(legislation_code
,null,'Global'
,legislation_code) legCode
, DECODE(application_short_name
, 'PER', 'Human Resources'
, 'PAY', 'Payroll'
, 'GHR', 'Federal Human Resources'
, 'CM', 'College Data'
, application_short_name) appName
, LAST_UPDATE_DATE appliedDate
FROM hr_legislation_installations
WHERE status = 'I'
ORDER BY legislation_code;


Invalid Objects

SELECT owner, object_type, object_name
FROM dba_objects
WHERE status != 'VALID'
AND object_type != 'UNDEFINED'
ORDER BY 1, 2, 3;