VIEW: APPS.PAY_JP_BALANCES_BY_DATE_V2

Source

View Text - Preformatted

SELECT PAF.ROWID ROW_ID, PAF.ASSIGNMENT_ID, PBT.BALANCE_TYPE_ID, PBT.BALANCE_NAME, PDB.DEFINED_BALANCE_ID, PBD.DATABASE_ITEM_SUFFIX, SES.EFFECTIVE_DATE, hr_jpbal.calc_all_balances(SES.EFFECTIVE_DATE, PAF.ASSIGNMENT_ID, PDB.DEFINED_BALANCE_ID) VALUE FROM PAY_BALANCE_DIMENSIONS PBD, PAY_DEFINED_BALANCES PDB, PAY_BALANCE_TYPES PBT, PAY_BALANCE_FEEDS_F FEED, PAY_RUN_RESULT_VALUES PRRV, PAY_RUN_RESULTS PRR, PAY_PAYROLL_ACTIONS PACT, PAY_ASSIGNMENT_ACTIONS ASSACT, PER_ALL_ASSIGNMENTS_F PAF, FND_SESSIONS SES WHERE SES.SESSION_ID = USERENV('SESSIONID') AND SES.EFFECTIVE_DATE BETWEEN PAF.EFFECTIVE_START_DATE AND PAF.EFFECTIVE_END_DATE AND ASSACT.assignment_id = PAF.assignment_id AND ASSACT.action_status = 'C' AND PACT.payroll_action_id = ASSACT.payroll_action_id AND PRR.assignment_action_id = ASSACT.assignment_action_id AND PRRV.run_result_id = PRR.run_result_id AND FEED.input_value_id = PRRV.input_value_id AND PACT.effective_date BETWEEN FEED.effective_start_date AND FEED.effective_end_date AND PBT.balance_type_id = FEED.balance_type_id AND nvl(PBT.LEGISLATION_CODE,'JP') = 'JP' AND PDB.BALANCE_TYPE_ID = PBT.balance_type_id AND PBD.BALANCE_DIMENSION_ID = PDB.balance_dimension_id AND NOT (PBD.DATABASE_ITEM_SUFFIX LIKE '_E%') group by PAF.ROWID, PAF.ASSIGNMENT_ID, PBT.BALANCE_TYPE_ID, PBT.BALANCE_NAME, PDB.DEFINED_BALANCE_ID, PBD.DATABASE_ITEM_SUFFIX, SES.EFFECTIVE_DATE

View Text - HTML Formatted

SELECT PAF.ROWID ROW_ID
, PAF.ASSIGNMENT_ID
, PBT.BALANCE_TYPE_ID
, PBT.BALANCE_NAME
, PDB.DEFINED_BALANCE_ID
, PBD.DATABASE_ITEM_SUFFIX
, SES.EFFECTIVE_DATE
, HR_JPBAL.CALC_ALL_BALANCES(SES.EFFECTIVE_DATE
, PAF.ASSIGNMENT_ID
, PDB.DEFINED_BALANCE_ID) VALUE
FROM PAY_BALANCE_DIMENSIONS PBD
, PAY_DEFINED_BALANCES PDB
, PAY_BALANCE_TYPES PBT
, PAY_BALANCE_FEEDS_F FEED
, PAY_RUN_RESULT_VALUES PRRV
, PAY_RUN_RESULTS PRR
, PAY_PAYROLL_ACTIONS PACT
, PAY_ASSIGNMENT_ACTIONS ASSACT
, PER_ALL_ASSIGNMENTS_F PAF
, FND_SESSIONS SES
WHERE SES.SESSION_ID = USERENV('SESSIONID')
AND SES.EFFECTIVE_DATE BETWEEN PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE
AND ASSACT.ASSIGNMENT_ID = PAF.ASSIGNMENT_ID
AND ASSACT.ACTION_STATUS = 'C'
AND PACT.PAYROLL_ACTION_ID = ASSACT.PAYROLL_ACTION_ID
AND PRR.ASSIGNMENT_ACTION_ID = ASSACT.ASSIGNMENT_ACTION_ID
AND PRRV.RUN_RESULT_ID = PRR.RUN_RESULT_ID
AND FEED.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID
AND PACT.EFFECTIVE_DATE BETWEEN FEED.EFFECTIVE_START_DATE
AND FEED.EFFECTIVE_END_DATE
AND PBT.BALANCE_TYPE_ID = FEED.BALANCE_TYPE_ID
AND NVL(PBT.LEGISLATION_CODE
, 'JP') = 'JP'
AND PDB.BALANCE_TYPE_ID = PBT.BALANCE_TYPE_ID
AND PBD.BALANCE_DIMENSION_ID = PDB.BALANCE_DIMENSION_ID
AND NOT (PBD.DATABASE_ITEM_SUFFIX LIKE '_E%') GROUP BY PAF.ROWID
, PAF.ASSIGNMENT_ID
, PBT.BALANCE_TYPE_ID
, PBT.BALANCE_NAME
, PDB.DEFINED_BALANCE_ID
, PBD.DATABASE_ITEM_SUFFIX
, SES.EFFECTIVE_DATE