VIEW: APPS.PAY_JP_SI_FD_ACTION_DATE_V

Source

View Text - Preformatted

SELECT /*+ ORDERED INDEX(PPA PAY_PAYROLL_ACTIONS_FK6) */ pbg.business_group_id business_group_id, ppa.payroll_action_id payroll_action_id, pes.element_set_name element_set_code, ppa.element_set_id element_set_id, ppa.date_earned date_earned, ppa.effective_date effective_date, ppa.action_status action_status, ppa.payroll_id payroll_id, ppa.assignment_set_id assignment_set_id, ppa.request_id request_id, ppa.legislative_parameters legislative_parameters, 'Y' default_value_flag from per_business_groups pbg, pay_element_sets pes, pay_payroll_actions ppa where pes.element_set_name in ('SAN','GEP','BON') and nvl(pes.business_group_id,pbg.business_group_id) = pbg.business_group_id and nvl(pes.legislation_code,pbg.legislation_code) = pbg.legislation_code /* PAY_PAYROLL_ACTIONS_FK1 does not work as AND-EQUAL index */ and ppa.business_group_id = pbg.business_group_id and ppa.element_set_id = pes.element_set_id union select /*+ ORDERED */ pbg.business_group_id business_group_id, to_number(null) payroll_action_id, pes.element_set_name element_set_code, to_number(null) element_set_id, fs.effective_date date_earned, fs.effective_date effective_date, null action_status, to_number(null) payroll_id, to_number(null) assignment_set_id, to_number(null) request_id, null legislative_parameters, 'N' default_value_flag from fnd_sessions fs, pay_element_sets pes, per_business_groups pbg where pes.element_set_name in ('SAN','GEP','BON') /* Include case when legislation code of business group is not JP */ and pes.legislation_code = 'JP' and fs.session_id = userenv('sessionid')

View Text - HTML Formatted

SELECT /*+ ORDERED INDEX(PPA PAY_PAYROLL_ACTIONS_FK6) */ PBG.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PPA.PAYROLL_ACTION_ID PAYROLL_ACTION_ID
, PES.ELEMENT_SET_NAME ELEMENT_SET_CODE
, PPA.ELEMENT_SET_ID ELEMENT_SET_ID
, PPA.DATE_EARNED DATE_EARNED
, PPA.EFFECTIVE_DATE EFFECTIVE_DATE
, PPA.ACTION_STATUS ACTION_STATUS
, PPA.PAYROLL_ID PAYROLL_ID
, PPA.ASSIGNMENT_SET_ID ASSIGNMENT_SET_ID
, PPA.REQUEST_ID REQUEST_ID
, PPA.LEGISLATIVE_PARAMETERS LEGISLATIVE_PARAMETERS
, 'Y' DEFAULT_VALUE_FLAG
FROM PER_BUSINESS_GROUPS PBG
, PAY_ELEMENT_SETS PES
, PAY_PAYROLL_ACTIONS PPA
WHERE PES.ELEMENT_SET_NAME IN ('SAN'
, 'GEP'
, 'BON')
AND NVL(PES.BUSINESS_GROUP_ID
, PBG.BUSINESS_GROUP_ID) = PBG.BUSINESS_GROUP_ID
AND NVL(PES.LEGISLATION_CODE
, PBG.LEGISLATION_CODE) = PBG.LEGISLATION_CODE /* PAY_PAYROLL_ACTIONS_FK1 DOES NOT WORK AS AND-EQUAL INDEX */
AND PPA.BUSINESS_GROUP_ID = PBG.BUSINESS_GROUP_ID
AND PPA.ELEMENT_SET_ID = PES.ELEMENT_SET_ID UNION SELECT /*+ ORDERED */ PBG.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, TO_NUMBER(NULL) PAYROLL_ACTION_ID
, PES.ELEMENT_SET_NAME ELEMENT_SET_CODE
, TO_NUMBER(NULL) ELEMENT_SET_ID
, FS.EFFECTIVE_DATE DATE_EARNED
, FS.EFFECTIVE_DATE EFFECTIVE_DATE
, NULL ACTION_STATUS
, TO_NUMBER(NULL) PAYROLL_ID
, TO_NUMBER(NULL) ASSIGNMENT_SET_ID
, TO_NUMBER(NULL) REQUEST_ID
, NULL LEGISLATIVE_PARAMETERS
, 'N' DEFAULT_VALUE_FLAG
FROM FND_SESSIONS FS
, PAY_ELEMENT_SETS PES
, PER_BUSINESS_GROUPS PBG
WHERE PES.ELEMENT_SET_NAME IN ('SAN'
, 'GEP'
, 'BON') /* INCLUDE CASE WHEN LEGISLATION CODE OF BUSINESS GROUP IS NOT JP */
AND PES.LEGISLATION_CODE = 'JP'
AND FS.SESSION_ID = USERENV('SESSIONID')