PAY Leave Liability Report (Australasia)
Description
Categories: BI Publisher
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
Description: Leave Liability Report (Australasia)
Application: Payroll
Source: Leave Liability Report (Australasia) (XML)
Short Name: PYAULLAL_XML
DB package: PAY_PYAULLAL_XMLP_PKG
Run
PAY Leave Liability Report (Australasia) and other Oracle EBS reports with Blitz Report™ on our demo environment
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) |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Payroll Action ID |
|
LOV Oracle | |
Cost Centres |
|
LOV Oracle | |
Payroll |
|
LOV Oracle |