Absence Management Plans
SELECT TO_CHAR( AAPF.EFFECTIVE_START_DATE,'MM/DD/RR' ) EFFECTIVE_START_DATE
, DECODE( TO_CHAR( AAPF.EFFECTIVE_END_DATE ,'MM/DD/RRRR' )
, '12/31/4712', NULL
, TO_CHAR( AAPF.EFFECTIVE_END_DATE ,'MM/DD/RR' )
) EFFECTIVE_END_DATE
, HR_GENERAL.DECODE_LOOKUP('ANC_ENTL_METHOD', AAPF.ENTL_METHOD_CD) PLAN_TYPE
, HR_GENERAL.DECODE_LOOKUP('ANC_SETUP_OBJ_MGMT', AAPF.PLAN_MGMT_CD ) MANAGEMENT
, FTV.TERRITORY_SHORT_NAME LEGISLATION
, AAPFT.NAME PLAN
, AAPFT.DESCRIPTION
, HR_GENERAL.DECODE_LOOKUP('ANC_DURATION_UOM', AAPF.PLAN_UOM ) PLAN_UOM
, (SELECT FLV.MEANING
FROM FND_LOOKUP_VALUES FLV
WHERE FLV.LOOKUP_CODE = AAPF.WS_CATEGORY_CD
AND FLV.LOOKUP_TYPE = 'ZMM_SR_SCHEDULE_CATEGORY'
AND FLV.LANGUAGE = USERENV('LANG')
AND FLV.ENABLED_FLAG = 'Y'
AND :P_AS_OF_DATE BETWEEN FLV.START_DATE_ACTIVE AND NVL(FLV.END_DATE_ACTIVE,SYSDATE)) ALTERNATIVE_SCHEDULE_CATEGORY
, (SELECT MEANING
FROM FND_LOOKUP_VALUES FLV
WHERE FLV.LANGUAGE = 'US'
AND FLV.LOOKUP_TYPE = 'ORA_ANC_PLAN_LEG_GROUPING'
AND FLV.LOOKUP_CODE = AAPF.LEG_GROUPING_CD
AND FLV.ENABLED_FLAG = 'Y'
AND :P_AS_OF_DATE BETWEEN FLV.START_DATE_ACTIVE AND FLV.END_DATE_ACTIVE) LEG_GROUP_CODE
, PLDGV.NAME LEGISLATIVE_DATA_GROUP
, HR_GENERAL.DECODE_LOOKUP('ANC_SETUP_OBJ_STATUS', AAPF.PLAN_STATUS ) STATUS
, HR_GENERAL.DECODE_LOOKUP('YES_NO',AAPF.STATUTORY_FLAG) ENABLE_CONCURRENT_ENTITLEMENT
, HR_GENERAL.DECODE_LOOKUP('ANC_PROCESSING_LEVEL', AAPF.PROCESSING_LEVEL_CD ) PROCESSING_LEVEL
, (SELECT FLV.MEANING
FROM FND_LOOKUP_VALUES FLV
WHERE FLV.LANGUAGE = 'US'
AND FLV.LOOKUP_CODE = AAPF.EXPIRATION_TERM_CD
AND FLV.ENABLED_FLAG = 'Y'
AND :P_AS_OF_DATE BETWEEN FLV.START_DATE_ACTIVE AND FLV.END_DATE_ACTIVE) EARNED_TIME_EXPIRATION_RULE
, ( SELECT FFV.BASE_FORMULA_NAME
FROM FF_FORMULAS_VL FFV
WHERE FFV.FORMULA_ID = AAPF.ABSENCE_DURATION_FORMULA_ID
AND SYSDATE BETWEEN FFV.EFFECTIVE_START_DATE AND FFV.EFFECTIVE_END_DATE
) CONVERSION_FORMULA
, AAPF.TRANSFR_PLAN_CATEGORY PLAN_CATEGORY
, DECODE(AAPF.ENTL_METHOD_CD, 'Q',HR_GENERAL.DECODE_LOOKUP('ANC_ENTL_PLAN_PERIOD', AAPF.PLAN_PERIOD_TYPE),
HR_GENERAL.DECODE_LOOKUP('ANC_ACC_PLAN_PERIOD', AAPF.PLAN_PERIOD_TYPE)) TYPE
, AAPF.ROLL_PERIOD_DURATION TERM_DURATION
, HR_GENERAL.DECODE_LOOKUP('ANC_ROLL_BACKWARD', AAPF.ROLL_BACKWARD_END_RULE ) START_RULE
, HR_GENERAL.DECODE_LOOKUP('ANC_SETUP_OBJ_UOM', AAPF.PERIOD_UOM ) TERM_DURATION_UOM
, HR_GENERAL.DECODE_LOOKUP('ANC_OVERLAP', AAPF.OVERLAP_CD ) OVERLAP_RULE
, AAPF.CALENDAR_START_MONTH START_MONTH
, AAPF.CALENDAR_START_DAY START_DAY
, NULL CALENDAR -- Need to identify, no field availble in the table
,( SELECT FLV.MEANING
FROM FND_LOOKUP_VALUES FLV
WHERE FLV.LANGUAGE = 'US'
AND FLV.LOOKUP_CODE = AAPF.ACC_EMP_BAL_DISPLAY
AND FLV.ENABLED_FLAG = 'Y'
AND :P_AS_OF_DATE BETWEEN FLV.START_DATE_ACTIVE AND FLV.END_DATE_ACTIVE) WORKER_BALANCE_DISPLAY
,( SELECT FLV.MEANING
FROM FND_LOOKUP_VALUES FLV
WHERE FLV.LANGUAGE = 'US'
AND FLV.LOOKUP_CODE = AAPF.ACC_MGR_BAL_DISPLAY
AND FLV.ENABLED_FLAG = 'Y'
AND :P_AS_OF_DATE BETWEEN FLV.START_DATE_ACTIVE AND FLV.END_DATE_ACTIVE) MANAGER_BALANCE_DISPLAY
, AAPF.INFORMATION_CATEGORY LEGISLATIVE_CONTEXT_SEGMENT
, AAPF.ATTRIBUTE_CATEGORY DESCRIPTIVE_CONTEXT_SEGMENT
, HR_GENERAL.DECODE_LOOKUP('ANC_QUA_ENRL_START',AAPF.ENROLLMENT_START_RULE ) QUALIFICATION_DATE_RULE
, HR_GENERAL.DECODE_LOOKUP('ANC_SETUP_OBJ_UOM_WITHOUT_HRS',AAPF.WAIT_PERIOD_DUR_UOM) WAITING_PERIOD
, HR_GENERAL.DECODE_LOOKUP('ANC_ENRL_START',AAPF.ENROLLMENT_START_RULE) ENROLLMENT_START_RULE
,( SELECT FFV.FORMULA_NAME
FROM FF_FORMULAS_VL FFV
WHERE FFV.FORMULA_ID = AAPF.ENROLLMENT_START_FORMULA_ID
AND :P_AS_OF_DATE BETWEEN FFV.EFFECTIVE_START_DATE AND FFV.EFFECTIVE_END_DATE
) ENRL_START_DATE_FORMULA
,( SELECT FFV.FORMULA_NAME
FROM FF_FORMULAS_VL FFV
WHERE FFV.FORMULA_ID = AAPF.ENROLLMENT_END_FORMULA_ID
AND :P_AS_OF_DATE BETWEEN FFV.EFFECTIVE_START_DATE AND FFV.EFFECTIVE_END_DATE
) ENRL_END_DATE_FORMULA
, HR_GENERAL.DECODE_LOOKUP('ANC_ENRL_END',AAPF.ENROLLMENT_END_RULE) ENROLLMENT_END_RULE
, HR_GENERAL.DECODE_LOOKUP('ANC_ENTL_END',AAPF.ENTITLEMENT_END_RULE) ENTITLEMENT_END_RULE
, HR_GENERAL.DECODE_LOOKUP('YES_NO',AAPF.EMP_ENRL_NEGATIVE_BALANCE_FLAG) ON_EMP_TERMI_NEGATIVE_ACC_BAL
, HR_GENERAL.DECODE_LOOKUP('YES_NO',AAPF.EMP_ENRL_POSITIVE_BALANCE_FLAG) ON_EMP_TERMI_POSITIVE_ACC_BAL
, HR_GENERAL.DECODE_LOOKUP('YES_NO',AAPF.TRANSFR_POSITIVE_BALANCE_FLAG) ON_LOSS_PLAN_TRANSFER_BAL
, HR_GENERAL.DECODE_LOOKUP('YES_NO',AAPF.ENROLL_POSITIVE_BALANCE_FLAG) ON_LOSS_PLAN_ELIG_DISB_POS_BAL
, HR_GENERAL.DECODE_LOOKUP('YES_NO', AAPF.ENROLL_NEGATIVE_BALANCE_FLAG) ON_LOSS_PLAN_ELIG_REC_NEG_BAL
, HR_GENERAL.DECODE_LOOKUP('YES_NO',AAPF.EMP_ENRL_TERMINATE_ENTL_FLAG) ON_EMP_TERMI_PAID_ENTL
, HR_GENERAL.DECODE_LOOKUP('YES_NO',AAPF.ENROLL_TERMINATE_ENTL_FLAG) ON_LOSS_PLAN_ELIG_TERMI_ENTL
, HR_GENERAL.DECODE_LOOKUP ('ORA_ANC_TRANSFER_LMT_RULE' , AAPF.TRANSFR_LMT_RULE ) LIMIT_RULE
, AAPF.TRANSFR_LMT_FLAT_AMT "LIMIT"
, ( SELECT FFV.FORMULA_NAME
FROM FF_FORMULAS_VL FFV
WHERE FFV.FORMULA_ID = AAPF.TRANSFR_LMT_FORMULA_ID
AND :P_AS_OF_DATE BETWEEN FFV.EFFECTIVE_START_DATE AND FFV.EFFECTIVE_END_DATE
) LIMIT_FORMULA
, HR_GENERAL.DECODE_LOOKUP ('ANC_PRORATION' ,AAPF.TRANSFR_LMT_PRORATE_RULE) LIMIT_PRORATION_RULE
,( SELECT FFV.FORMULA_NAME
FROM FF_FORMULAS_VL FFV
WHERE FFV.FORMULA_ID = AAPF.TRANSFR_LMT_PRORATE_FORMULA_ID
AND :P_AS_OF_DATE BETWEEN FFV.EFFECTIVE_START_DATE AND FFV.EFFECTIVE_END_DATE
) LIMIT_PRORATION_FORMULA
, AAPF.TRANSFR_PLAN_CATEGORY TRF_PLAN_CATEGORY
, ( SELECT FFV.FORMULA_NAME
FROM FF_FORMULAS_VL FFV
WHERE FFV.FORMULA_ID = AAPF.TRANSFR_TARGET_FORMULA_ID
AND :P_AS_OF_DATE BETWEEN FFV.EFFECTIVE_START_DATE AND FFV.EFFECTIVE_END_DATE
) TARGET_PLAN_FORMULA
, HR_GENERAL.DECODE_LOOKUP ( 'YES_NO' , AAPF.RE_INSTATEMENT_FLAG) ALLOW_PRIOR_BAL_REINSTATEMENT
, AAPF.RE_ENRL_BAL_LMT BAL_REINSTATEMENT_LMT
, AAPF.TIME_FRAME_LMT TIME_FRM_LMT
, HR_GENERAL.DECODE_LOOKUP ('ORA_ANC_TMEFRAME_LIMIT' , AAPF.TIME_FRAME_UOM) TIME_FRM_UOM
, APEPF.ELIG_PROFILE_SEQUENCE ELIGIBLITY_SEQUENCE
, BEP.NAME ELIGIBILITY_PROFILE
, APEPF.REQUIRED_FLAG REQUIRED
, TO_CHAR( APEPF.EFFECTIVE_START_DATE,'MM/DD/RR' ) ELIG_EFFECTIVE_START_DATE
, DECODE( TO_CHAR( APEPF.EFFECTIVE_END_DATE ,'MM/DD/RRRR' )
, '12/31/4712', NULL
, TO_CHAR( APEPF.EFFECTIVE_END_DATE ,'MM/DD/RR' )
) ELIG_EFFECTIVE_END_DATE
, HR_GENERAL.DECODE_LOOKUP('ANC_DEFN_TYPE',AAPF.ENTL_DEFINITION_TYPE) ENTITLEMENT_DEFINITION_TYPE
, ( SELECT FFV.FORMULA_NAME
FROM FF_FORMULAS_VL FFV
WHERE FFV.FORMULA_ID = AAPF.ENTL_FORMULA_ID
AND :P_AS_OF_DATE BETWEEN FFV.EFFECTIVE_START_DATE AND FFV.EFFECTIVE_END_DATE
) ENTITLEMENT_FORMULA
, HR_GENERAL.DECODE_LOOKUP('ANC_QUA_ENT_PLN_START_DATE',AAPF.ENTITLEMENT_START_DATE) ENTITLEMENT_START_DATE
, AEBF.ENTL_BAND_SEQUENCE QUAL_BRAND_SEQUENCE -- Need to identify, There will multiple records
, AEBF.ENTL_INPUT_EXPRESSION QUAL_BRAND_EXPRESSION_BUILDER -- Need to identify, There will multiple records
, TO_CHAR( AEBF.EFFECTIVE_START_DATE,'MM/DD/RR' ) QUAL_BRAND_EFF_START_DATE -- Need to identify, There will multiple records
, DECODE( TO_CHAR( AEBF.EFFECTIVE_END_DATE ,'MM/DD/RRRR' )
, '12/31/4712', NULL
, TO_CHAR( AEBF.EFFECTIVE_END_DATE ,'MM/DD/RR' )
) QUAL_BRAND_EFF_END_DATE -- Need to identify, There will multiple records
, AEBDEF.ENTL_BAND_DTL_SEQUENCE QUAL_DETAILS_SEQUENCE -- Need to identify, There will multiple records
, AEBDEFT.BAND_NAME QUAL_DETAILS_DETAIL_NAME -- Need to identify, There will multiple records
, AEBDEF.NO_OF_DAYS QUAL_DETAILS_DURATION -- Need to identify, There will multiple records
, AEBDEF.PAY_RATE_FACTOR QUAL_DETAILS_PAYMENT_PERCENT -- Need to identify, There will multiple records
, ( SELECT FFV.FORMULA_NAME
FROM FF_FORMULAS_VL FFV
WHERE FFV.FORMULA_ID = AEBDEF.ENTL_DTL_OUTPUT_FORMULA_ID
AND :P_AS_OF_DATE BETWEEN FFV.EFFECTIVE_START_DATE AND FFV.EFFECTIVE_END_DATE
) QUAL_DETAILS_ENTL_FORMULA -- Need to identify, There will multiple records
, TO_CHAR( AEBDEF.EFFECTIVE_START_DATE,'MM/DD/RR' ) QUAL_DETAILS_EFF_START_DATE -- Need to identify, There will multiple records
, DECODE( TO_CHAR( AEBDEF.EFFECTIVE_END_DATE ,'MM/DD/RRRR' )
, '12/31/4712', NULL
, TO_CHAR( AEBDEF.EFFECTIVE_END_DATE ,'MM/DD/RR' )
) QUAL_DETAILS_EFF_END_DATE -- Need to identify, There will multiple records
, HR_GENERAL.DECODE_LOOKUP('YES_NO',AAPF.OTHER_ADJUSTMENT_FLAG) OTHER_ADJUSTMENTS
/*, HR_GENERAL.DECODE_LOOKUP('ANC_ABS_PLAN_OTHER_REASONS',AAPF.OTHER_REASONS) OTHER_ADJUSTMENT_REASON */ -- Need to identify the logic pick the list of values in multiple selection
--, AAPF.OTHER_REASONS REASON
,CASE WHEN LENGTH(AAPF.OTHER_REASONS)= 18
THEN
HR_GENERAL.DECODE_LOOKUP('ANC_ABS_PLAN_OTHER_REASONS','CE')
|| ',' ||
HR_GENERAL.DECODE_LOOKUP('ANC_ABS_PLAN_OTHER_REASONS','CP')
|| ',' ||
HR_GENERAL.DECODE_LOOKUP('ANC_ABS_PLAN_OTHER_REASONS','HB')
|| ',' ||
HR_GENERAL.DECODE_LOOKUP('ANC_ABS_PLAN_OTHER_REASONS','HC')
|| ',' ||
HR_GENERAL.DECODE_LOOKUP('ANC_ABS_PLAN_OTHER_REASONS','HS')
WHEN LENGTH(AAPF.OTHER_REASONS) = 2
THEN
HR_GENERAL.DECODE_LOOKUP('ANC_ABS_PLAN_OTHER_REASONS',AAPF.OTHER_REASONS)
WHEN LENGTH(AAPF.OTHER_REASONS) = 6
THEN HR_GENERAL.DECODE_LOOKUP('ANC_ABS_PLAN_OTHER_REASONS',SUBSTR(AAPF.OTHER_REASONS,0,2))
|| ',' ||
HR_GENERAL.DECODE_LOOKUP('ANC_ABS_PLAN_OTHER_REASONS',SUBSTR(AAPF.OTHER_REASONS,5,2))
WHEN LENGTH(AAPF.OTHER_REASONS) = 10
THEN HR_GENERAL.DECODE_LOOKUP('ANC_ABS_PLAN_OTHER_REASONS',SUBSTR(AAPF.OTHER_REASONS,0,2))
|| ',' ||
HR_GENERAL.DECODE_LOOKUP('ANC_ABS_PLAN_OTHER_REASONS',SUBSTR(AAPF.OTHER_REASONS,5,2))
|| ',' ||
HR_GENERAL.DECODE_LOOKUP('ANC_ABS_PLAN_OTHER_REASONS',SUBSTR(AAPF.OTHER_REASONS,9,2))
WHEN LENGTH(AAPF.OTHER_REASONS) = 14
THEN HR_GENERAL.DECODE_LOOKUP('ANC_ABS_PLAN_OTHER_REASONS',SUBSTR(AAPF.OTHER_REASONS,0,2))
|| ',' ||
HR_GENERAL.DECODE_LOOKUP('ANC_ABS_PLAN_OTHER_REASONS',SUBSTR(AAPF.OTHER_REASONS,5,2))
|| ',' ||
HR_GENERAL.DECODE_LOOKUP('ANC_ABS_PLAN_OTHER_REASONS',SUBSTR(AAPF.OTHER_REASONS,9,2))
|| ',' ||
HR_GENERAL.DECODE_LOOKUP('ANC_ABS_PLAN_OTHER_REASONS',SUBSTR(AAPF.OTHER_REASONS,13,2))
ELSE NULL END OTHER_ADJUSTMENT_REASON
, HR_GENERAL.DECODE_LOOKUP('YES_NO', AAPF.BEN_IMPACT_FLAG) ENABLE_BEN_INT
, HR_GENERAL.DECODE_LOOKUP('YES_NO', AAPF.PENDING_DISBURSE_FLAG) MARK_AS_PENDING
, HR_GENERAL.DECODE_LOOKUP('ORA_ANC_ELECT_DT_RULE' ,AAPF.ELECTION_DATE_RULE ) ELECTION_DATE_RULE
, AAPF.ELECT_DISB_PAY_RATE_FACTOR DFLT_PYMNT_PRCNTG
, ( SELECT FFV.FORMULA_NAME
FROM FF_FORMULAS_VL FFV
WHERE FFV.FORMULA_ID = AAPF.ELECT_DISB_VALID_FORMULA_ID
AND :P_AS_OF_DATE BETWEEN FFV.EFFECTIVE_START_DATE AND FFV.EFFECTIVE_END_DATE
) EVALUATION_FORMULA
, AAPF.YREND_DISB_PAY_RATE_FACTOR YRND_DFLT_PYMNT_PRCNTG
, AAPF.ADMIN_MANUAL_ADJUSTMENT DD_ADMIN
, AAPF.MGR_MANUAL_ADJUSTMENT DD_MGR
, AAPF.EMP_MANUAL_ADJUSTMENT DD_EMP
, HR_GENERAL.DECODE_LOOKUP ('ORA_ANC_DISB_DON_RULE', AAPF.DISB_RULE ) DD_DISBURSEMENT_RULE
, ( SELECT FFV.FORMULA_NAME
FROM FF_FORMULAS_VL FFV
WHERE FFV.FORMULA_ID = AAPF.DISB_FORMULA_ID
AND :P_AS_OF_DATE BETWEEN FFV.EFFECTIVE_START_DATE AND FFV.EFFECTIVE_END_DATE
) DD_FORMULA
, AAPF.DISB_MIN_FLAT_AMT DD_MIN
, AAPF.DISB_MAX_FLAT_AMT DD_MAX
, AAPF.DISB_INCR_FLAT_AMT DD_INCR
, AAPF.DON_ADMIN_MANUAL_ADJ DON_ADMIN
, AAPF.DON_MGR_MANUAL_ADJ DON_MGR
, AAPF.DON_EMP_MANUAL_ADJ DON_EMP
, HR_GENERAL.DECODE_LOOKUP ('ORA_ANC_DISB_DON_RULE', AAPF.DON_RULE ) DON_RULE
, ( SELECT FFV.FORMULA_NAME
FROM FF_FORMULAS_VL FFV
WHERE FFV.FORMULA_ID = AAPF.DON_FORMULA_ID
AND :P_AS_OF_DATE BETWEEN FFV.EFFECTIVE_START_DATE AND FFV.EFFECTIVE_END_DATE
) DON_FORMULA
, AAPF.DON_MIN_FLAT_AMT DON_MIN
, AAPF.DON_MAX_FLAT_AMT DON_MAX
, AAPF.DON_INCR_FLAT_AMT DON_INCR
, AAPF.ADMIN_EXPIRATION_UPDATE ED_ADMIN
, AAPF.MGR_EXPIRATION_UPDATE ED_MGR
, HR_GENERAL.DECODE_LOOKUP('ORA_ANC_COMP_ADJRSN' , AAPF.COMP_EXP_ADJRSN) ED_REASON
, HR_GENERAL.DECODE_LOOKUP('ORA_ANC_COMP_EXPRSN' , AAPF.COMP_MNUL_ADJRSN) MANUAL_AD_REASON
, NULL ACC_TRNSF_FROM_PREV_EMP -- Need to identify
, NULL USED_TIME_TRNSF_FROM_PREV_EMP -- Need to identify
, HR_GENERAL.DECODE_LOOKUP('YES_NO',AAPF.CASH_OUT_FLAG) DISCR_DISB_OF_ACC_BAL
, HR_GENERAL.DECODE_LOOKUP('YES_NO',AAPF.BALANCE_TRANSFER_FLAG) ACC_BAL_TRNSF_ACROSS_PLANS
, HR_GENERAL.DECODE_LOOKUP('ANC_ABS_PLAN_USE_BASE_RATE',AAPF.PLAN_USE_RATE_RULE) ABSENCE_PAYMENT_RATE_RULE
, (SELECT NAME FROM PAY_RATE_DEFINITIONS_F_VL WHERE RATE_DEFINITION_ID=AAPF.PLAN_USE_RATE_ID) RATE_NAME -- Need to identify, Not able to find the base table for this
, ( SELECT FFV.FORMULA_NAME
FROM FF_FORMULAS_VL FFV
WHERE FFV.FORMULA_ID = AAPF.PLAN_USE_FORMULA_ID
AND :P_AS_OF_DATE BETWEEN FFV.EFFECTIVE_START_DATE AND FFV.EFFECTIVE_END_DATE
) RATE_FORMULA
, HR_GENERAL.DECODE_LOOKUP('ANC_ABS_PLAN_PAYOUT_RATE',AAPF.PAYOUT_RATE_RULE) FINAL_BAL_PAYMENT_RATE_RULE
, (SELECT NAME FROM PAY_RATE_DEFINITIONS_F_VL WHERE RATE_DEFINITION_ID=AAPF.PAYOUT_RATE_ID) PAYOUT_RATE_NAME
, ( SELECT FFV.FORMULA_NAME
FROM FF_FORMULAS_VL FFV
WHERE FFV.FORMULA_ID = AAPF.PAYOUT_RATE_FORMULA_ID
AND :P_AS_OF_DATE BETWEEN FFV.EFFECTIVE_START_DATE AND FFV.EFFECTIVE_END_DATE
) PAYOUT_RATE_FORMULA
, HR_GENERAL.DECODE_LOOKUP('ANC_ABS_PLAN_CASH_OUT_RATE',AAPF.CASHOUT_RATE_RULE) DISBURSEMENT_RATE_RULE
, (SELECT NAME FROM PAY_RATE_DEFINITIONS_F_VL WHERE RATE_DEFINITION_ID=AAPF.CASHOUT_RATE_ID) CASH_RATE_NAME
, ( SELECT FFV.FORMULA_NAME
FROM FF_FORMULAS_VL FFV
WHERE FFV.FORMULA_ID = AAPF.CASHOUT_RATE_FORMULA_ID
AND :P_AS_OF_DATE BETWEEN FFV.EFFECTIVE_START_DATE AND FFV.EFFECTIVE_END_DATE
) CASHOUT_RATE_FORMULA
, HR_GENERAL.DECODE_LOOKUP('ANC_ABS_PLAN_LIABILITY_RATE',AAPF.LIABILITY_RATE_RULE) LIABILITY_BOOKING_RATE_RULE
, (SELECT NAME FROM PAY_RATE_DEFINITIONS_F_VL WHERE RATE_DEFINITION_ID=AAPF.LIABILITY_RATE_ID) LIABILITY_RATE_NAME
, (SELECT FFV.FORMULA_NAME
FROM FF_FORMULAS_VL FFV
WHERE FFV.FORMULA_ID = AAPF.LIABILITY_RATE_FORMULA_ID
AND :P_AS_OF_DATE BETWEEN FFV.EFFECTIVE_START_DATE AND FFV.EFFECTIVE_END_DATE
) LIABILITY_RATE_FORMULA
, HR_GENERAL.DECODE_LOOKUP('YES_NO',AAPF.PAYROLL_IMPACT_FLAG) TRNSF_ABS_PAY_INF_FOR_PAY_PROC
, AAPF.PAYROLL_MAPPING_ID ELEMENT -- Need to identify the Element name
, HR_GENERAL.DECODE_LOOKUP('ANC_DEFN_TYPE',AAPF.ACC_DEFINITION_TYPE) ACCRUAL_DEFINITION
, ( SELECT FFV.FORMULA_NAME
FROM FF_FORMULAS_VL FFV
WHERE FFV.FORMULA_ID = AAPF.ACC_FORMULA_ID
AND :P_AS_OF_DATE BETWEEN FFV.EFFECTIVE_START_DATE AND FFV.EFFECTIVE_END_DATE
) ACCRUAL_FORMULA
, AAPF.PAY_RATE_FACTOR||'%' PAYMENT_PERCENTAGE
, HR_GENERAL.DECODE_LOOKUP('ANC_ACCRUAL_METHOD',AAPF.ACCRUAL_METHOD_CD) ACCRUAL_METHOD
, ( SELECT FFV.FORMULA_NAME
FROM FF_FORMULAS_VL FFV
WHERE FFV.FORMULA_ID = AAPF.PARTIAL_ACCRUAL_FORMULA_ID
AND :P_AS_OF_DATE BETWEEN FFV.EFFECTIVE_START_DATE AND FFV.EFFECTIVE_END_DATE
) PARTIAL_ACCRUAL_PERIOD_FORMULA
, HR_GENERAL.DECODE_LOOKUP('ANC_ACCRUAL_VESTING',AAPF.ACCRUAL_VESTING_CD) ACCRUAL_VESTING_RULE
, HR_GENERAL.DECODE_LOOKUP('ANC_PRORATION',AAPF.PRORATION_RULE) ACCRUAL_PRORATION_RULE
, HR_GENERAL.DECODE_LOOKUP('ANC_ACC_PER_FREQUENCY',AAPF.ACC_PERIOD_FREQUENCY) ACCRUAL_FREQUENCY_SOURCE
, HPV.NAME REPEATING_PERIOD
, HR_GENERAL.DECODE_LOOKUP('ANC_CEIL_LMT',AAPF.CEIL_LIMIT_RULE) CEILING_RULE
, ( SELECT FFV.FORMULA_NAME
FROM FF_FORMULAS_VL FFV
WHERE FFV.FORMULA_ID = AAPF.CEIL_LIMIT_FORMULA_ID
AND :P_AS_OF_DATE BETWEEN FFV.EFFECTIVE_START_DATE AND FFV.EFFECTIVE_END_DATE
) CEILNG_FORMULA
, AAPF.CEIL_LIMIT_FLAT_AMT CELING
, HR_GENERAL.DECODE_LOOKUP('YES_NO',AAPF.ACC_NEGATIVE_BAL_FLAG) ALLOW_NEGATIVE_BALANCES
, AAPF.ACC_NEGATIVE_BAL_LIMIT NEGATIVE_BAL_LIMIT
, HR_GENERAL. DECODE_LOOKUP ('ANC_CEIL_LMT' ,AAPF.ACC_LMT_RULE) ANNUAL_ACC_LMT_RULE
, AAPF.ACC_LMT_FLAT_AMT ANNUAL_ACCRUAL_LIMIT
, HR_GENERAL.DECODE_LOOKUP ('ANC_CEIL_LMT_PRORATION', AAPF.ACC_LIMIT_PRORATE_RULE) LIMIT_PRORATE_RULE
, ( SELECT FFV.FORMULA_NAME
FROM FF_FORMULAS_VL FFV
WHERE FFV.FORMULA_ID = AAPF.ACC_LIMIT_FORMULA_ID
AND :P_AS_OF_DATE BETWEEN FFV.EFFECTIVE_START_DATE AND FFV.EFFECTIVE_END_DATE
) ANNL_LIMIT_PRORATION_FORMULA
, HR_GENERAL.DECODE_LOOKUP ( 'ORA_ANC_ROLLOVER_RULE', AAPF.ROLLOVR_LMT_RULE) ROLLOVER_RULE
, AAPF.ROLLOVR_LMT_FLAT_AMT ROLLOVER_LIMIT
, HR_GENERAL. DECODE_LOOKUP ( 'ANC_PRORATION', AAPF.ROLLOVR_LMT_PRORATE_RULE) LMT_PRORATE_ROLL_RULE
, ( SELECT FFV.FORMULA_NAME
FROM FF_FORMULAS_VL FFV
WHERE FFV.FORMULA_ID = AAPF.ROLLOVR_LMT_PRORATE_FORMULA_ID
AND :P_AS_OF_DATE BETWEEN FFV.EFFECTIVE_START_DATE AND FFV.EFFECTIVE_END_DATE
) LIMIT_ROLL_PRORATE_FORMULA
, (SELECT AAPFT2.NAME
FROM ANC_ABSENCE_PLANS_F_TL AAPFT2
WHERE AAPFT2.ABSENCE_PLAN_ID = AAPF.ROLLOVR_TARGET_PLAN
AND AAPFT2.LANGUAGE = USERENV('LANG')
AND :P_AS_OF_DATE BETWEEN AAPFT2.EFFECTIVE_START_DATE AND AAPFT2.EFFECTIVE_END_DATE
) ROLL_TARGET_PLAN
, AAPF.ROLLOVR_PLAN_CONVERSION_RATE ROLL_CONVERSION_FACTOR
, AAPF.YEAR_END_BALANCE_DISBURSE_FLAG DISBURSE_REMAIN_BAL
, HR_GENERAL.DECODE_LOOKUP('ANC_CARRY_OVER',AAPF.CARRY_OVER_RULE) CARRYOVER_RULE
, ( SELECT FFV.FORMULA_NAME
FROM FF_FORMULAS_VL FFV
WHERE FFV.FORMULA_ID = AAPF.CARRY_OVER_FORMULA_ID
AND :P_AS_OF_DATE BETWEEN FFV.EFFECTIVE_START_DATE AND FFV.EFFECTIVE_END_DATE
) CARRYOVER_FORMULA
, AAPF.CARRY_OVER_FLAT_AMT CARRYOVER_LIMIT
, HR_GENERAL.DECODE_LOOKUP('ANC_CARRY_OVER_PRORATION',AAPF.CARRY_OVER_PRORATE_RULE) CARRYOVER_PRORATION_RULE
, HR_GENERAL.DECODE_LOOKUP('YES_NO',AAPF.CARRY_OVER_EXPIRED_FLAG) CARRYOVER_EXP_AFT_SPECFC_TIME
, HR_GENERAL.DECODE_LOOKUP('ANC_TYPE_RULE_UOM',AAPF.CARRY_OVER_EXPIRED_UOM) CARRYOVER_EXP_UOM
, AAPF.CARRY_OVER_EXPIRED_UNITS CARRYOVER_EXP_DUR
, AABF.ACC_BAND_SEQUENCE ACC_MATRIX_SEQUENCE -- Need to identify
, AABF.ACC_INPUT_EXPRESSION ACC_MATRIX_EXPRESSION_BUILDER -- Need toidentify
, AABF.ACCRUAL_RATE ACC_MATRIX_ACCRUAL_RATE -- Need to identify
, AABF.ACCRUAL_CEILING ACCRUAL_CEILING
, AABF.ACC_CARRYOVER_LIMIT ACC_CARRYOVER_LIMIT
, ( SELECT FFV.FORMULA_NAME
FROM FF_FORMULAS_VL FFV
WHERE FFV.FORMULA_ID = AABF.ACC_OUTPUT_FORMULA_ID
AND :P_AS_OF_DATE BETWEEN FFV.EFFECTIVE_START_DATE AND FFV.EFFECTIVE_END_DATE
) ACC_MATRIX_ACCRUAL_FORMULA -- Need to identify
, TO_CHAR( AABF.EFFECTIVE_START_DATE,'MM/DD/RR' ) ACC_MATRIX_EFF_START_DATE -- Need to identify
, DECODE( TO_CHAR( AABF.EFFECTIVE_END_DATE ,'MM/DD/RRRR' )
, '12/31/4712', NULL
, TO_CHAR( AABF.EFFECTIVE_END_DATE ,'MM/DD/RR' )
) ACC_MATRIX_EFF_END_DATE
FROM ANC_ABSENCE_PLANS_F AAPF
, ANC_ABSENCE_PLANS_F_TL AAPFT
, FND_TERRITORIES_VL FTV
, PER_LEGISLATIVE_DATA_GROUPS_VL PLDGV
, ANC_PLAN_ELIG_PROFILES_F APEPF
, BEN_ELIGY_PRFL BEP
, ANC_ENTL_BANDS_F AEBF
, ANC_ENTL_BANDS_DTL_F AEBDEF
, ANC_ENTL_BANDS_DTL_F_TL AEBDEFT
, ANC_ACCRUAL_BANDS_F AABF
, HWM_RP_TM_PERIODS_VL HPV
WHERE :P_AS_OF_DATE BETWEEN AAPF.EFFECTIVE_START_DATE(+) AND AAPF.EFFECTIVE_END_DATE(+)
AND AAPF.ABSENCE_PLAN_ID = AAPFT.ABSENCE_PLAN_ID
AND AAPFT.LANGUAGE = USERENV('LANG')
AND AAPF.LEGISLATION_CODE = FTV.TERRITORY_CODE
AND AAPF.LEGISLATIVE_DATA_GROUP_ID = PLDGV.LEGISLATIVE_DATA_GROUP_ID
AND AAPF.ABSENCE_PLAN_ID = APEPF.ABSENCE_PLAN_ID(+)
AND :P_AS_OF_DATE BETWEEN APEPF.EFFECTIVE_START_DATE(+) AND APEPF.EFFECTIVE_END_DATE(+)
AND APEPF.BEN_ELIG_PROFILE_ID = BEP.eligy_prfl_id(+)
AND AAPF.ABSENCE_PLAN_ID = AEBF.ABSENCE_PLAN_ID(+)
AND :P_AS_OF_DATE BETWEEN AEBF.EFFECTIVE_START_DATE(+) AND AEBF.EFFECTIVE_END_DATE(+)
AND AEBF.ENTL_BAND_ID = AEBDEF.ENTL_BAND_ID(+)
AND AEBDEF.ENTL_BAND_DTL_ID = AEBDEFT.ENTL_BAND_DTL_ID(+)
AND :P_AS_OF_DATE BETWEEN AEBDEF.EFFECTIVE_START_DATE(+) AND AEBDEF.EFFECTIVE_END_DATE(+)
AND :P_AS_OF_DATE BETWEEN AEBDEFT.EFFECTIVE_START_DATE(+) AND AEBDEFT.EFFECTIVE_END_DATE(+)
AND AEBDEFT.LANGUAGE(+) = USERENV('LANG')
AND AAPF.ABSENCE_PLAN_ID = AABF.ABSENCE_PLAN_ID(+)
AND AAPF.ACC_PERIOD_WFM_CALEDAR_ID = HPV.REPEATING_TM_PERIOD_ID(+)
AND :P_AS_OF_DATE BETWEEN AABF.EFFECTIVE_START_DATE(+) AND AABF.EFFECTIVE_END_DATE(+)
AND :P_AS_OF_DATE BETWEEN AAPFT.EFFECTIVE_START_DATE(+) AND AAPFT.EFFECTIVE_END_DATE(+)
AND :P_AS_OF_DATE BETWEEN BEP.START_DATE(+) AND BEP.END_DATE(+)
AND (
(:P_CREATION_TYPE= 'Custom' and AAPF.created_by <> 'SEED_DATA_FROM_APPLICATION' )
OR (:P_CREATION_TYPE = 'Seeded' and AAPF.created_by = 'SEED_DATA_FROM_APPLICATION' )
OR (:P_CREATION_TYPE = 'Seeded Modified' and AAPF.created_by = 'SEED_DATA_FROM_APPLICATION' and AAPF. last_updated_by <> 'SEED_DATA_FROM_APPLICATION')
OR (:P_CREATION_TYPE = 'ALL')
)
AND (
(:P_CREATION_TYPE= 'Custom' and AAPFT.created_by <> 'SEED_DATA_FROM_APPLICATION' )
OR (:P_CREATION_TYPE = 'Seeded' and AAPFT.created_by = 'SEED_DATA_FROM_APPLICATION' )
OR (:P_CREATION_TYPE = 'Seeded Modified' and AAPFT.created_by = 'SEED_DATA_FROM_APPLICATION' and AAPFT. last_updated_by <> 'SEED_DATA_FROM_APPLICATION')
OR (:P_CREATION_TYPE = 'ALL')
)
ORDER BY AAPFT.NAME
