Payroll Balance Configuration
SELECT PLD.NAME LEGISLATIVE_DATA_GROUP_BAL,
PBC.USER_CATEGORY_NAME BALANCE_CATEGORY_BAL,
HR_GENERAL.DECODE_LOOKUP('PAY_UNITS',PBT.BALANCE_UOM) UOM_BAL,
(SELECT FCV.NAME
FROM FND_CURRENCIES_VL FCV
WHERE FCV.CURRENCY_CODE = PBT.CURRENCY_CODE) BAL_CURRENCY,
(SELECT PET.ELEMENT_NAME
FROM PAY_ELEMENT_TYPES_VL PET,
PAY_INPUT_VALUES_VL PIVV
WHERE PIVV.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PIVV.INPUT_VALUE_ID = PBT.INPUT_VALUE_ID
AND :P_AS_OF_DATE BETWEEN PIVV.EFFECTIVE_START_DATE AND PIVV.EFFECTIVE_END_DATE
AND :P_AS_OF_DATE BETWEEN PET.EFFECTIVE_START_DATE AND PET.EFFECTIVE_END_DATE) BAL_ELEMENT_NAME,
(SELECT PIVV.NAME
FROM PAY_INPUT_VALUES_VL PIVV
WHERE PIVV.INPUT_VALUE_ID = PBT.INPUT_VALUE_ID
AND :P_AS_OF_DATE BETWEEN PIVV.EFFECTIVE_START_DATE AND PIVV.EFFECTIVE_END_DATE) BAL_ELE_INPUT_VALUE,
PDUV.DIMENSION_NAME DIMENSION_NAME_BAL,
' ' RUN_BALANCE_STATUS_BAL,
TO_CHAR(PBV.BALANCE_LOAD_DATE,'MM/DD/YYYY') BALANCE_VALID_FROM_DATE,
HR_GENERAL.DECODE_LOOKUP('YES_NO' ,PDB.GROSSUP_ALLOWED_FLAG) GROSS_CAL_ENABLE_FLAG_BAL,
NULL LEGACY_CODE,
PBT.BALANCE_NAME BALANCE_BAL,
PBT.REPORTING_NAME REPORTING_NAME_BAL,
PECV.CLASSIFICATION_NAME BAL_CLASSIFICATION,
HR_GENERAL.DECODE_LOOKUP('PAY_ADD_SUBTRACT',PBC_ADD_SUB.SCALE) BAL_CLASSIFICATION_ADD_SUB,
PET.ELEMENT_NAME ELEMENT_NAME_BAL,
DECODE(PET.ELEMENT_NAME,NULL,'',PET.ELEMENT_NAME||' Earnings Results') ELEMENT_NAME1_BAL,
PIV.NAME INPUT_VALUE1_BAL,
HR_GENERAL.DECODE_LOOKUP('PAY_ADD_SUBTRACT',PBF.SCALE) ADD_SUBTRACT_BAL,
NULL LEGACY_CODE1_BAL,
NULL WORKSTREAM_BAL,
PECV_PRI.CLASSIFICATION_NAME PRI_CLASSIFICATION_BAL,
PECV_SEC.CLASSIFICATION_NAME SEC_CLASSIFICATION_BAL,
TO_CHAR(PET.EFFECTIVE_START_DATE,'MM/DD/YYY') BAL_EFFECTIVE_DATE_BAL
FROM
PAY_BALANCE_TYPES_VL PBT,
PAY_BALANCE_FEEDS_F PBF,
PAY_ELEMENT_TYPES_VL PET,
PAY_INPUT_VALUES_VL PIV,
PER_LEGISLATIVE_DATA_GROUPS_VL PLD,
PAY_BALANCE_CATEGORIES_VL PBC,
PAY_DIMENSION_USAGES_VL PDUV,
PAY_ELE_CLASSIFICATIONS_VL PECV_PRI,
PAY_ELE_CLASSIFICATIONS_VL PECV_SEC,
PAY_DEFINED_BALANCES PDB,
PAY_BALANCE_VALIDATION PBV,
PAY_ELE_CLASSIFICATIONS_VL PECV,
PAY_BAL_CLASSIFICATIONS PBC1,
PAY_ELE_CLASSIFICATIONS_VL PECV_ADD_SUB,
PAY_BAL_CLASSIFICATIONS PBC_ADD_SUB
WHERE PLD.LEGISLATIVE_DATA_GROUP_ID = PET.LEGISLATIVE_DATA_GROUP_ID
AND PET.CLASSIFICATION_ID = PECV_PRI.BASE_CLASSIFICATION_ID(+)
AND PET.SECONDARY_CLASSIFICATION_ID = PECV_SEC.BASE_CLASSIFICATION_ID(+)
AND PECV_PRI.BASE_CLASSIFICATION_ID = PECV_SEC.PARENT_CLASSIFICATION_ID
AND PLD.LEGISLATION_CODE = PECV_PRI.LEGISLATION_CODE
AND PLD.LEGISLATION_CODE = PECV_SEC.LEGISLATION_CODE
AND PET.ELEMENT_TYPE_ID = PIV.ELEMENT_TYPE_ID
AND PBF.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID(+)
AND PLD.LEGISLATIVE_DATA_GROUP_ID(+) = PBF.LEGISLATIVE_DATA_GROUP_ID
AND PBT.BALANCE_TYPE_ID = PBF.BALANCE_TYPE_ID(+)
AND PLD.LEGISLATIVE_DATA_GROUP_ID = PBT.LEGISLATIVE_DATA_GROUP_ID
AND PBF.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID
AND PBT.BALANCE_CATEGORY_ID = PBC.BASE_BALANCE_CATEGORY_ID
AND PBC.LEGISLATION_CODE = PLD.LEGISLATION_CODE
AND PBT.BALANCE_TYPE_ID = PDB.BALANCE_TYPE_ID
AND PDB.LEGISLATIVE_DATA_GROUP_ID = PBT.LEGISLATIVE_DATA_GROUP_ID
AND PDB.BALANCE_DIMENSION_ID = PDUV.BALANCE_DIMENSION_ID
AND PLD.LEGISLATION_CODE = PDUV.LEGISLATION_CODE
AND PDB.DEFINED_BALANCE_ID = PBV.DEFINED_BALANCE_ID
AND PLD.LEGISLATIVE_DATA_GROUP_ID(+) = PBV.LEGISLATIVE_DATA_GROUP_ID
AND PECV.CLASSIFICATION_ID = PBC1.CLASSIFICATION_ID
AND PBC1.BALANCE_TYPE_ID = PBT.BALANCE_TYPE_ID
AND PECV_ADD_SUB.CLASSIFICATION_ID = PBC_ADD_SUB.CLASSIFICATION_ID
AND PBC_ADD_SUB.BALANCE_TYPE_ID = PBT.BALANCE_TYPE_ID
AND TRUNC(:P_AS_OF_DATE) BETWEEN PET.EFFECTIVE_START_DATE(+) AND PET.EFFECTIVE_END_DATE(+)
AND TRUNC(:P_AS_OF_DATE) BETWEEN PECV_PRI.DATE_FROM AND NVL(PECV_PRI.DATE_TO,:P_AS_OF_DATE + 1)
AND TRUNC(:P_AS_OF_DATE) BETWEEN PECV_SEC.DATE_FROM AND NVL(PECV_SEC.DATE_TO,:P_AS_OF_DATE + 1)
AND TRUNC(:P_AS_OF_DATE) BETWEEN PBF.EFFECTIVE_START_DATE(+) AND PBF.EFFECTIVE_END_DATE(+)
AND TRUNC(:P_AS_OF_DATE) BETWEEN PIV.EFFECTIVE_START_DATE(+) AND PIV.EFFECTIVE_END_DATE(+)
AND TRUNC(:P_AS_OF_DATE) BETWEEN PBC.EFFECTIVE_START_DATE(+) AND PBC.EFFECTIVE_END_DATE(+)
AND ((:P_CREATION_TYPE= 'Custom' AND PBT.created_by <> 'SEED_DATA_FROM_APPLICATION' )
OR (:P_CREATION_TYPE = 'Seeded' AND PBT.created_by = 'SEED_DATA_FROM_APPLICATION' )
OR (:P_CREATION_TYPE = 'Seeded Modified'
AND PBT.created_by = 'SEED_DATA_FROM_APPLICATION'
AND PBT.last_updated_by <> 'SEED_DATA_FROM_APPLICATION'
)
OR (:P_CREATION_TYPE = 'ALL')
)
ORDER BY 1,12
