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;

How to find a locking action

On occasion, when rolling back an assignment action and a locking error occurs, it might not always be obvious which assignment action is responsible for the lock. In these circumstances, the following sql may be of assistance:

select ppa.action_type,
ppa.effective_date,
ppa.payroll_action_id,
paa.assignment_action_id
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_interlocks pai
where ppa.payroll_action_id = paa.payroll_action_id and
paa.assignment_action_id = pai.locking_action_id and
pai.locked_action_id = &lockedid

/ e.g. where &lockedid is the assignment_action_id of the action you are trying to remove. In this case, a Quickpay is being locked out by a Pre-Payment run.

BEE

1) BEE process needs a specific mask for input with format Data. Use the followed command "to_char(start_date,'yyyy/mm/dd')"

2) If inputs of an Element have been defined with a default value, the BEE Transfer process doesn't automatically put the default values.
You need to specify all input values with a default when there is no data in the old system

3) The BEE Transfer process doesn't put a value for input values defined as NOT USER ENTERABLE

4) If inputs of an Element have been defined with a Quick Code, you have to create the BEE Lines with value equal to the meaning in the QC and not the code

Magnetic Transfer

select 'Magnetic Transfer' a_type, org.name company, tp.period_name , lk.bank_name_alt, lk.bank_name bank_name, lk.bank_branch_name_alt branch_name, ea.segment1 bsb, ea.segment2 account_nr, pp.value, ea.segment3 account_name, per.last_name || ', ' || per.first_name full_name, per.employee_number
from pay_payroll_actions pa,
pay_assignment_actions_v aa,
pay_pre_payments pp,
pay_personal_payment_methods_f pm,
pay_external_accounts ea,
ap_bank_branches lk,
per_time_periods tp,
per_assignments_f ass,
per_people_x per,
hr_all_organization_units org,
pay_payrolls_x pay
where 1 = 1
and tp.period_name = nvl(:p_period_name, tp.period_name)
and nvl(pa.date_earned,pa.effective_date) between tp.start_date and tp.end_date
and tp.payroll_id = pa.payroll_id
and pay.payroll_id = tp.payroll_id
AND pay.payroll_name = nvl(:p_payroll_name, pay.payroll_name)
and pa.action_type = 'M'
and pa.payroll_action_id = aa.payroll_action_id
and pp.pre_payment_id = aa.pre_payment_id
and pp.personal_payment_method_id = pm.personal_payment_method_id
and :p_period_end_date between pm.effective_start_date and pm.effective_end_date
and pm.external_account_id = ea.external_account_id (+)
and ea.segment1 = lk.bank_number (+)||'-'||lk.bank_num (+)
and ass.assignment_id = aa.assignment_id
and :p_period_end_date between ass.effective_start_date and ass.effective_end_date
and per.person_id = ass.person_id
and org.organization_id (+)= pa.business_group_id
and decode(:p_business_group,'ALL', org.name, :p_business_group) = org.name