VIEW: APPS.PAY_JP_BAL_MATRIX_BY_DATE_V

Source

View Text - Preformatted

SELECT /*+ ORDERED USE_NL(PAA PPA PRR PRRV FEED PBT PBTTL) */ paa.assignment_id, pbttl.balance_type_id, pbttl.balance_name, pbt.balance_uom, substrb(hr_general.decode_lookup('UNITS', pbt.balance_uom), 1, 80) D_BALANCE_UOM, pbt.currency_code, pay_jp_balance_view_pkg.get_value(paa.assignment_id, fs.effective_date, pbttl.balance_type_id, '_ASG_RUN', ppa.business_group_id) ASG_RUN_BAL, pay_jp_balance_view_pkg.get_value(paa.assignment_id, fs.effective_date, pbttl.balance_type_id, '_ASG_PTD', ppa.business_group_id) ASG_PROC_PTD_BAL, pay_jp_balance_view_pkg.get_value(paa.assignment_id, fs.effective_date, pbttl.balance_type_id, '_ASG_MTD                      EFFECTIVE_DATE 01-01 RESET 12', ppa.business_group_id) ASG_MTD_JP_BAL, pay_jp_balance_view_pkg.get_value(paa.assignment_id, fs.effective_date, pbttl.balance_type_id, '_ASG_YTD                      EFFECTIVE_DATE 01-01 RESET 01', ppa.business_group_id) ASG_YTD_JP_BAL, pay_jp_balance_view_pkg.get_value(paa.assignment_id, fs.effective_date, pbttl.balance_type_id, '_ASG_FYTD                     DATE_EARNED          RESET 01', ppa.business_group_id) ASG_FYTD_JP_BAL, pay_jp_balance_view_pkg.get_value(paa.assignment_id, fs.effective_date, pbttl.balance_type_id, '_ASG_AUGTD                    EFFECTIVE_DATE 01-08 RESET 01', ppa.business_group_id) ASG_AUG2JUL_JP_BAL, pay_jp_balance_view_pkg.get_value(paa.assignment_id, fs.effective_date, pbttl.balance_type_id, '_ASG_LTD', ppa.business_group_id) ASG_ITD_BAL, pay_jp_balance_view_pkg.get_value(paa.assignment_id, fs.effective_date, pbttl.balance_type_id, '_ASG_JULTD                    EFFECTIVE_DATE 01-07 RESET 01', ppa.business_group_id) ASG_JUL2JUN_JP_BAL, pay_jp_balance_view_pkg.get_value(paa.assignment_id, fs.effective_date, pbttl.balance_type_id, '_ASG_BYTD', ppa.business_group_id) ASG_FYTD2_JP_BAL, pay_jp_balance_view_pkg.get_value(paa.assignment_id, fs.effective_date, pbttl.balance_type_id, '_ASG_APRTD                    EFFECTIVE_DATE 01-04 RESET 01', ppa.business_group_id) ASG_APR2MAR_JP_BAL from fnd_sessions fs, pay_assignment_actions paa, pay_payroll_actions ppa, pay_run_results prr, pay_run_result_values prrv, pay_balance_feeds_f feed, pay_balance_types pbt, pay_balance_types_tl pbttl where fs.session_id = userenv('sessionid') and ppa.payroll_action_id = paa.payroll_action_id and ppa.effective_date <= fs.effective_date and ppa.action_type in ('R', 'Q', 'B', 'I', 'V') and prr.assignment_action_id = paa.assignment_action_id and prr.status in ('P', 'PA') and prrv.run_result_id = prr.run_result_id and feed.input_value_id = prrv.input_value_id and ppa.effective_date between feed.effective_start_date and feed.effective_end_date and pbt.balance_type_id = feed.balance_type_id and pbttl.balance_type_id = pbt.balance_type_id and pbttl.language = userenv('LANG') group by paa.assignment_id, ppa.business_group_id, pbttl.balance_type_id, pbttl.balance_name, pbt.balance_uom, pbt.currency_code, fs.effective_date

View Text - HTML Formatted

SELECT /*+ ORDERED USE_NL(PAA PPA PRR PRRV FEED PBT PBTTL) */ PAA.ASSIGNMENT_ID
, PBTTL.BALANCE_TYPE_ID
, PBTTL.BALANCE_NAME
, PBT.BALANCE_UOM
, SUBSTRB(HR_GENERAL.DECODE_LOOKUP('UNITS'
, PBT.BALANCE_UOM)
, 1
, 80) D_BALANCE_UOM
, PBT.CURRENCY_CODE
, PAY_JP_BALANCE_VIEW_PKG.GET_VALUE(PAA.ASSIGNMENT_ID
, FS.EFFECTIVE_DATE
, PBTTL.BALANCE_TYPE_ID
, '_ASG_RUN'
, PPA.BUSINESS_GROUP_ID) ASG_RUN_BAL
, PAY_JP_BALANCE_VIEW_PKG.GET_VALUE(PAA.ASSIGNMENT_ID
, FS.EFFECTIVE_DATE
, PBTTL.BALANCE_TYPE_ID
, '_ASG_PTD'
, PPA.BUSINESS_GROUP_ID) ASG_PROC_PTD_BAL
, PAY_JP_BALANCE_VIEW_PKG.GET_VALUE(PAA.ASSIGNMENT_ID
, FS.EFFECTIVE_DATE
, PBTTL.BALANCE_TYPE_ID
, '_ASG_MTD EFFECTIVE_DATE 01-01 RESET 12'
, PPA.BUSINESS_GROUP_ID) ASG_MTD_JP_BAL
, PAY_JP_BALANCE_VIEW_PKG.GET_VALUE(PAA.ASSIGNMENT_ID
, FS.EFFECTIVE_DATE
, PBTTL.BALANCE_TYPE_ID
, '_ASG_YTD EFFECTIVE_DATE 01-01 RESET 01'
, PPA.BUSINESS_GROUP_ID) ASG_YTD_JP_BAL
, PAY_JP_BALANCE_VIEW_PKG.GET_VALUE(PAA.ASSIGNMENT_ID
, FS.EFFECTIVE_DATE
, PBTTL.BALANCE_TYPE_ID
, '_ASG_FYTD DATE_EARNED RESET 01'
, PPA.BUSINESS_GROUP_ID) ASG_FYTD_JP_BAL
, PAY_JP_BALANCE_VIEW_PKG.GET_VALUE(PAA.ASSIGNMENT_ID
, FS.EFFECTIVE_DATE
, PBTTL.BALANCE_TYPE_ID
, '_ASG_AUGTD EFFECTIVE_DATE 01-08 RESET 01'
, PPA.BUSINESS_GROUP_ID) ASG_AUG2JUL_JP_BAL
, PAY_JP_BALANCE_VIEW_PKG.GET_VALUE(PAA.ASSIGNMENT_ID
, FS.EFFECTIVE_DATE
, PBTTL.BALANCE_TYPE_ID
, '_ASG_LTD'
, PPA.BUSINESS_GROUP_ID) ASG_ITD_BAL
, PAY_JP_BALANCE_VIEW_PKG.GET_VALUE(PAA.ASSIGNMENT_ID
, FS.EFFECTIVE_DATE
, PBTTL.BALANCE_TYPE_ID
, '_ASG_JULTD EFFECTIVE_DATE 01-07 RESET 01'
, PPA.BUSINESS_GROUP_ID) ASG_JUL2JUN_JP_BAL
, PAY_JP_BALANCE_VIEW_PKG.GET_VALUE(PAA.ASSIGNMENT_ID
, FS.EFFECTIVE_DATE
, PBTTL.BALANCE_TYPE_ID
, '_ASG_BYTD'
, PPA.BUSINESS_GROUP_ID) ASG_FYTD2_JP_BAL
, PAY_JP_BALANCE_VIEW_PKG.GET_VALUE(PAA.ASSIGNMENT_ID
, FS.EFFECTIVE_DATE
, PBTTL.BALANCE_TYPE_ID
, '_ASG_APRTD EFFECTIVE_DATE 01-04 RESET 01'
, PPA.BUSINESS_GROUP_ID) ASG_APR2MAR_JP_BAL
FROM FND_SESSIONS FS
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, PAY_RUN_RESULTS PRR
, PAY_RUN_RESULT_VALUES PRRV
, PAY_BALANCE_FEEDS_F FEED
, PAY_BALANCE_TYPES PBT
, PAY_BALANCE_TYPES_TL PBTTL
WHERE FS.SESSION_ID = USERENV('SESSIONID')
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PPA.EFFECTIVE_DATE <= FS.EFFECTIVE_DATE
AND PPA.ACTION_TYPE IN ('R'
, 'Q'
, 'B'
, 'I'
, 'V')
AND PRR.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PRR.STATUS IN ('P'
, 'PA')
AND PRRV.RUN_RESULT_ID = PRR.RUN_RESULT_ID
AND FEED.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID
AND PPA.EFFECTIVE_DATE BETWEEN FEED.EFFECTIVE_START_DATE
AND FEED.EFFECTIVE_END_DATE
AND PBT.BALANCE_TYPE_ID = FEED.BALANCE_TYPE_ID
AND PBTTL.BALANCE_TYPE_ID = PBT.BALANCE_TYPE_ID
AND PBTTL.LANGUAGE = USERENV('LANG') GROUP BY PAA.ASSIGNMENT_ID
, PPA.BUSINESS_GROUP_ID
, PBTTL.BALANCE_TYPE_ID
, PBTTL.BALANCE_NAME
, PBT.BALANCE_UOM
, PBT.CURRENCY_CODE
, FS.EFFECTIVE_DATE