PAY Leave Liability Report (Australasia) (1)

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)