VIEW: APPS.PAY_ZA_BALANCES_BY_DATE_V

Source

View Text - Preformatted

SELECT PAF.ASSIGNMENT_ID, SES.EFFECTIVE_DATE, PBT.BALANCE_NAME, PBD.DATABASE_ITEM_SUFFIX, py_za_bal.calc_all_balances(SES.EFFECTIVE_DATE, PAF.ASSIGNMENT_ID, PDB.DEFINED_BALANCE_ID) VALUE, PDB.DEFINED_BALANCE_ID, PBT.BALANCE_TYPE_ID FROM PAY_BALANCE_TYPES PBT, PER_ASSIGNMENTS_F PAF, FND_SESSIONS SES, PAY_DEFINED_BALANCES PDB, PAY_BALANCE_DIMENSIONS PBD WHERE (PBT.LEGISLATION_CODE IS NULL OR PBT.LEGISLATION_CODE = 'ZA') AND PBT.balance_type_id = PDB.BALANCE_TYPE_ID AND PBD.balance_dimension_id = PDB.BALANCE_DIMENSION_ID AND SES.EFFECTIVE_DATE BETWEEN PAF.EFFECTIVE_START_DATE AND PAF.EFFECTIVE_END_DATE AND SES.SESSION_ID = USERENV('SESSIONID') AND EXISTS ( SELECT NULL FROM PAY_RUN_RESULTS PRR , PAY_BALANCE_FEEDS_F FEED , PAY_ASSIGNMENT_ACTIONS PAA2 , PAY_PAYROLL_ACTIONS PPA, PAY_INPUT_VALUES_F PIV WHERE PBT.BALANCE_TYPE_ID = FEED.BALANCE_TYPE_ID AND PRR.ELEMENT_TYPE_ID = PIV.ELEMENT_TYPE_ID AND PPA.EFFECTIVE_DATE BETWEEN PIV.EFFECTIVE_START_DATE and PIV.EFFECTIVE_END_DATE AND FEED.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID AND PPA.PAYROLL_ACTION_ID = PAA2.PAYROLL_ACTION_ID AND PPA.EFFECTIVE_DATE <= SES.EFFECTIVE_DATE AND PAA2.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID AND PAA2.ASSIGNMENT_ID = PAF.ASSIGNMENT_ID) AND PAF.EFFECTIVE_END_DATE = ( SELECT MAX(PAF2.EFFECTIVE_END_DATE) FROM PER_ASSIGNMENTS_F PAF2 WHERE PAF2.ASSIGNMENT_ID = PAF.ASSIGNMENT_ID )

View Text - HTML Formatted

SELECT PAF.ASSIGNMENT_ID
, SES.EFFECTIVE_DATE
, PBT.BALANCE_NAME
, PBD.DATABASE_ITEM_SUFFIX
, PY_ZA_BAL.CALC_ALL_BALANCES(SES.EFFECTIVE_DATE
, PAF.ASSIGNMENT_ID
, PDB.DEFINED_BALANCE_ID) VALUE
, PDB.DEFINED_BALANCE_ID
, PBT.BALANCE_TYPE_ID
FROM PAY_BALANCE_TYPES PBT
, PER_ASSIGNMENTS_F PAF
, FND_SESSIONS SES
, PAY_DEFINED_BALANCES PDB
, PAY_BALANCE_DIMENSIONS PBD
WHERE (PBT.LEGISLATION_CODE IS NULL OR PBT.LEGISLATION_CODE = 'ZA')
AND PBT.BALANCE_TYPE_ID = PDB.BALANCE_TYPE_ID
AND PBD.BALANCE_DIMENSION_ID = PDB.BALANCE_DIMENSION_ID
AND SES.EFFECTIVE_DATE BETWEEN PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE
AND SES.SESSION_ID = USERENV('SESSIONID')
AND EXISTS ( SELECT NULL
FROM PAY_RUN_RESULTS PRR
, PAY_BALANCE_FEEDS_F FEED
, PAY_ASSIGNMENT_ACTIONS PAA2
, PAY_PAYROLL_ACTIONS PPA
, PAY_INPUT_VALUES_F PIV
WHERE PBT.BALANCE_TYPE_ID = FEED.BALANCE_TYPE_ID
AND PRR.ELEMENT_TYPE_ID = PIV.ELEMENT_TYPE_ID
AND PPA.EFFECTIVE_DATE BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE
AND FEED.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID
AND PPA.PAYROLL_ACTION_ID = PAA2.PAYROLL_ACTION_ID
AND PPA.EFFECTIVE_DATE <= SES.EFFECTIVE_DATE
AND PAA2.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
AND PAA2.ASSIGNMENT_ID = PAF.ASSIGNMENT_ID)
AND PAF.EFFECTIVE_END_DATE = ( SELECT MAX(PAF2.EFFECTIVE_END_DATE)
FROM PER_ASSIGNMENTS_F PAF2
WHERE PAF2.ASSIGNMENT_ID = PAF.ASSIGNMENT_ID )