ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D4715351A9E05362FB09050512Y select a.* , PAY_PYAULLAL_XMLP_PKG.cf_total_valueformula(ENTITLEMENT_VALUE, ACCRUAL_VALUE) CF_total_value from( SELECT LU.MEANING ACCRUAL_CATEGORY ,F.CONCATENATED_SEGMENTS COST_CENTRE ,PER.FULL_NAME EMPLOYEE_NAME ,PER.ORDER_NAME ORDER_NAME ,PER.EMPLOYEE_NUMBER ,TO_CHAR(L.LEAVE_ACCRUAL, '999990.00') LEAVE_ACCRUAL ,TO_CHAR(L.LEAVE_ENTITLEMENT, '999990.00') LEAVE_ENTITELMENT ,round(L.ENTITLEMENT_VALUE * C.PROPORTION, :cp_precision) ENTITLEMENT_VALUE ,round(L.ACCRUAL_VALUE * C.PROPORTION, :cp_precision) ACCRUAL_VALUE ,ASG.PAYROLL_ID FROM PAY_AU_LEAVE_LIABILITY_V L ,HR_LOOKUPS LU ,PAY_PAYROLL_ACTIONS PA ,PAY_COST_ALLOCATION_KEYFLEX F ,PER_PEOPLE_F PER ,PER_ASSIGNMENTS_F ASG ,PAY_COST_ALLOCATIONS_F C WHERE LU.APPLICATION_ID = 800 AND LU.LOOKUP_TYPE = 'ABSENCE_CATEGORY' AND LU.LOOKUP_CODE = L.ACCRUAL_CATEGORY AND (C.COST_ALLOCATION_KEYFLEX_ID = :P_COST_ALLOCATION_KEYFLEX_ID OR :P_COST_ALLOCATION_KEYFLEX_ID IS NULL ) AND C.EFFECTIVE_START_DATE = (SELECT MAX(C_D.EFFECTIVE_START_DATE) FROM PAY_COST_ALLOCATIONS_F C_D WHERE C_D.COST_ALLOCATION_ID = C.COST_ALLOCATION_ID ) AND F.COST_ALLOCATION_KEYFLEX_ID = C.COST_ALLOCATION_KEYFLEX_ID AND L.ASSIGNMENT_ID = C.ASSIGNMENT_ID AND PA.PAYROLL_ACTION_ID = L.PAYROLL_ACTION_ID AND ASG.ASSIGNMENT_ID = L.ASSIGNMENT_ID AND ASG.EFFECTIVE_START_DATE = (SELECT MAX(ASG_D.EFFECTIVE_START_DATE) FROM PER_ASSIGNMENTS_F ASG_D WHERE ASG_D.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID ) AND PER.PERSON_ID = ASG.PERSON_ID AND PER.EFFECTIVE_START_DATE = (SELECT MAX(PER_D.EFFECTIVE_START_DATE) FROM PER_PEOPLE_F PER_D WHERE PER_D.PERSON_ID = PER.PERSON_ID ) AND L.PAYROLL_ACTION_ID = :P_PAYROLL_ACTION_ID AND PER.BUSINESS_GROUP_ID = :P_BUSINESS_GROUP_ID AND PER.BUSINESS_GROUP_ID = PA.BUSINESS_GROUP_ID UNION SELECT LU.MEANING ,NULL ,PER.FULL_NAME EMPLOYEE_NAME ,PER.ORDER_NAME ORDER_NAME ,PER.EMPLOYEE_NUMBER ,TO_CHAR(L.LEAVE_ACCRUAL, '999990.00') ,TO_CHAR(L.LEAVE_ENTITLEMENT, '999990.00') ,round(L.ENTITLEMENT_VALUE, :cp_precision) ,round(L.ACCRUAL_VALUE, :cp_precision) ,ASG.PAYROLL_ID FROM PAY_AU_LEAVE_LIABILITY_V L ,HR_LOOKUPS LU ,PAY_PAYROLL_ACTIONS PA ,PER_PEOPLE_F PER ,PER_ASSIGNMENTS_F ASG WHERE :P_COST_ALLOCATION_KEYFLEX_ID IS NULL AND NOT EXISTS (SELECT C.ASSIGNMENT_ID FROM PAY_COST_ALLOCATIONS_F C WHERE C.ASSIGNMENT_ID = L.ASSIGNMENT_ID ) AND LU.APPLICATION_ID = 800 AND LU.LOOKUP_TYPE = 'ABSENCE_CATEGORY' AND LU.LOOKUP_CODE = L.ACCRUAL_CATEGORY AND PA.PAYROLL_ACTION_ID = L.PAYROLL_ACTION_ID AND ASG.ASSIGNMENT_ID = L.ASSIGNMENT_ID AND ASG.EFFECTIVE_START_DATE = ( SELECT MAX(ASG_D.EFFECTIVE_START_DATE) /* FROM PER_ASSIGNMENTS_F ASG_D WHERE ASG_D.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID ) AND PER.PERSON_ID = ASG.PERSON_ID AND PER.EFFECTIVE_START_DATE = (SELECT MAX(PER_D.EFFECTIVE_START_DATE), PAY_PYAULLAL_XMLP_PKG.cf_total_valueformula(ENTITLEMENT_VALUE, ACCRUAL_VALUE) CF_total_value */ FROM PER_ASSIGNMENTS_F ASG_D WHERE ASG_D.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID ) AND PER.PERSON_ID = ASG.PERSON_ID AND PER.EFFECTIVE_START_DATE = (SELECT MAX(PER_D.EFFECTIVE_START_DATE) FROM PER_PEOPLE_F PER_D WHERE PER_D.PERSON_ID = PER.PERSON_ID ) AND PA.PAYROLL_ACTION_ID = (Select PAYROLL_ACTION_ID from Pay_Payroll_actions Where Payroll_action_id =:P_PAYROLL_ACTION_ID) AND PER.BUSINESS_GROUP_ID = :P_BUSINESS_GROUP_ID AND PER.BUSINESS_GROUP_ID = PA.BUSINESS_GROUP_ID ) a Where (Payroll_ID = :P_PAYROLL Or :P_Payroll is null) ORDER BY 1, 2, nvl(ORDER_NAME,EMPLOYEE_NAME) PAY PYAULLAL_XML US PAY Leave Liability Report (Australasia) Imported from BI Publisher Description: Leave Liability Report (Australasia) Application: Payroll Source: Leave Liability Report (Australasia) (XML) Short Name: PYAULLAL_XML DB package: PAY_PYAULLAL_XMLP_PKG BI Publisher :cp_currency_code :cp_currency_format :cp_precision :p_business_group_id :p_conc_request_id :p_cost_allocation_keyflex_id :p_payroll :p_payroll_action_id 1 -20 :p_business_group_id Number fnd_profile.value('PER_BUSINESS_GROUP_ID') US BUSINESS_GROUP_ID 2 10 :p_payroll_action_id LOV Oracle PY_NZ_ARCHIVE_PAYROLL_ACTIONS Y select action.payroll_action_id id, to_char(action.effective_date,'DD-MON-YYYY')||' ('||to_char(action.payroll_action_id)||')' value, null description from pay_payroll_actions action where action.business_group_id=fnd_global.per_business_group_id and action.action_type='X' and action.report_type='LEAVE_LIABILITY' order by action.effective_date desc,action.payroll_action_id desc Y US Payroll Action ID 3 20 :p_cost_allocation_keyflex_id LOV Oracle PY_NZ_COST_CENTRES Y select cost_allocation_keyflex_id id, concatenated_segments value, null description from pay_cost_allocation_keyflex where id_flex_num in ( select org_information7 from hr_organization_information_v where organization_id=fnd_global.per_business_group_id and org_information_context='Business Group Information' ) and concatenated_segments is not null order by value,description US Cost Centres 4 30 :p_payroll LOV Oracle PAY_SRS_PAYROLL Y select pay.payroll_id id, pay.payroll_name value, null description from pay_payrolls_f pay,fnd_sessions fnd where pay.business_group_id= fnd_global.per_business_group_id and fnd.session_id=userenv('sessionid') and fnd.effective_date between pay.effective_start_date and pay.effective_end_date order by pay.payroll_name US Payroll