View: PAY_JP_GEPPEN_SANTEI_V

Product:	PAY - Payroll
Description:	This view is used by Japanese statutory report of GEPPEN SANTEI.
Implementation/DBA Data:	APPS.PAY_JP_GEPPEN_SANTEI_V

View Text

SELECT /*+ ORDERED USE_NL(PPA
, PAA
, PEE_APPL
, PEEV_APPL_MTH
, PEEV_APPL_CAT
, PLIV_ORG
, PEE_ORG
, PEEV_ORG
, PEEV_HI_NUM
, PEEV_WP_NUM) INDEX(PPA PAY_PAYROLL_ACTIONS_FK6) INDEX(PAA PAY_ASSIGNMENT_ACTIONS_N50) INDEX(PEE_APPL PAY_ELEMENT_ENTRIES_F_FK3) INDEX(PEEV_APPL_MTH PAY_ELEMENT_ENTRY_VALUES_F_N50) INDEX(PEEV_APPL_CAT PAY_ELEMENT_ENTRY_VALUES_F_N50) INDEX(PLIV_ORG PAY_LINK_INPUT_VALUES_F_N2) INDEX(PEE_ORG PAY_ELEMENT_ENTRIES_F_N51) INDEX(PEEV_ORG PAY_ELEMENT_ENTRY_VALUES_F_N50) INDEX(PEEV_HI_NUM PAY_ELEMENT_ENTRY_VALUES_F_N50) INDEX(PEEV_WP_NUM PAY_ELEMENT_ENTRY_VALUES_F_N50) */ PAA.ASSIGNMENT_ACTION_ID
, MIN(PAA.ASSIGNMENT_ID) ASSIGNMENT_ID
, MIN(PPA.BUSINESS_GROUP_ID) BUSINESS_GROUP_ID
, MIN(PPA.PAYROLL_ID) PAYROLL_ID
, MIN(PPA.CONSOLIDATION_SET_ID) CONSOLIDATION_SET_ID
, MIN(PPA.DATE_EARNED) DATE_EARNED
, MIN(PPA.EFFECTIVE_DATE) EFFECTIVE_DATE
, MIN(NVL(PEEV_APPL_CAT.SCREEN_ENTRY_VALUE
, 'O')) APPLIED_CATEGORY
, MAX(TO_NUMBER(NVL(PEEV_ORG.SCREEN_ENTRY_VALUE
, -1))) ORGANIZATION_ID
, MIN(SUBSTRB(PEEV_HI_NUM.SCREEN_ENTRY_VALUE
, 1
, 10)) HI_NUMBER
, MIN(SUBSTRB(PEEV_WP_NUM.SCREEN_ENTRY_VALUE
, 1
, 10)) WP_NUMBER
, SUM(DECODE(PEEV_ORG.INPUT_VALUE_ID
, PAY_JP_REPORT_PKG.GET_SI_REC_ID('HI_ORG_IV_ID')
, 1
, PAY_JP_REPORT_PKG.GET_SI_REC_ID('WP_ORG_IV_ID')
, 2
, PAY_JP_REPORT_PKG.GET_SI_REC_ID('WPF_ORG_IV_ID')
, 4
, 0)) SI_TYPE
, MIN(NVL(SUBSTRB(PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR( PAY_JP_REPORT_PKG.GET_SI_REC_ID('EXC_IV_ID')
, PAA.ASSIGNMENT_ID
, PPA.EFFECTIVE_DATE)
, 1
, 1)
, 'N')) OUTPUT_FLAG
, PPA.ELEMENT_SET_ID
, MIN(PEEV_APPL_MTH.SCREEN_ENTRY_VALUE) APPLIED_MONTH
FROM PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_ELEMENT_ENTRIES_F PEE_APPL
, PAY_ELEMENT_ENTRY_VALUES_F PEEV_APPL_MTH
, PAY_ELEMENT_ENTRY_VALUES_F PEEV_APPL_CAT
, (SELECT PAY_JP_REPORT_PKG.GET_SI_REC_ID('HI_ORG_IV_ID') ORG_IV_ID
FROM DUAL UNION SELECT PAY_JP_REPORT_PKG.GET_SI_REC_ID('WP_ORG_IV_ID') ORG_IV_ID
FROM DUAL UNION SELECT PAY_JP_REPORT_PKG.GET_SI_REC_ID('WPF_ORG_IV_ID') ORG_IV_ID
FROM DUAL) IV_ORG
, PAY_LINK_INPUT_VALUES_F PLIV_ORG
, PAY_ELEMENT_ENTRIES_F PEE_ORG
, PAY_ELEMENT_ENTRY_VALUES_F PEEV_ORG
, PAY_ELEMENT_ENTRY_VALUES_F PEEV_HI_NUM
, PAY_ELEMENT_ENTRY_VALUES_F PEEV_WP_NUM
WHERE PPA.ELEMENT_SET_ID IN ( PAY_JP_REPORT_PKG.GET_GS_REC_ID('SAN_ELE_SET_ID')
, PAY_JP_REPORT_PKG.GET_GS_REC_ID('GEP_ELE_SET_ID')
, PAY_JP_REPORT_PKG.GET_GS_REC_ID('IKU_ELE_SET_ID'))
AND PPA.ACTION_TYPE = 'R'
AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PAA.ACTION_STATUS = 'C'
AND PEE_APPL.UPDATING_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PEE_APPL.EFFECTIVE_START_DATE = PPA.EFFECTIVE_DATE
AND PEE_APPL.ENTRY_TYPE = 'E'
AND PEEV_APPL_MTH.ELEMENT_ENTRY_ID = PEE_APPL.ELEMENT_ENTRY_ID
AND PEEV_APPL_MTH.EFFECTIVE_START_DATE = PEE_APPL.EFFECTIVE_START_DATE
AND PEEV_APPL_MTH.EFFECTIVE_END_DATE = PEE_APPL.EFFECTIVE_END_DATE
AND PEEV_APPL_MTH.INPUT_VALUE_ID IN ( PAY_JP_REPORT_PKG.GET_GS_REC_ID('HI_APPL_MTH_IV_ID')
, PAY_JP_REPORT_PKG.GET_GS_REC_ID('WP_APPL_MTH_IV_ID'))
AND PEEV_APPL_MTH.SCREEN_ENTRY_VALUE = DECODE(PPA.ELEMENT_SET_ID
, PAY_JP_REPORT_PKG.GET_GS_REC_ID('SAN_ELE_SET_ID')
, DECODE(SIGN(TO_DATE('2003/04/01'
, 'YYYY/MM/DD') - PPA.DATE_EARNED)
, 1
, TO_CHAR(PPA.DATE_EARNED
, 'YYYY')||'10'
, TO_CHAR(PPA.DATE_EARNED
, 'YYYY')||'09')
, TO_CHAR(PPA.DATE_EARNED
, 'YYYYMM'))
AND PEEV_APPL_CAT.ELEMENT_ENTRY_ID = PEE_APPL.ELEMENT_ENTRY_ID
AND PEEV_APPL_CAT.EFFECTIVE_START_DATE = PEE_APPL.EFFECTIVE_START_DATE
AND PEEV_APPL_CAT.EFFECTIVE_END_DATE = PEE_APPL.EFFECTIVE_END_DATE
AND PEEV_APPL_CAT.INPUT_VALUE_ID IN ( PAY_JP_REPORT_PKG.GET_GS_REC_ID('HI_APPL_CAT_IV_ID')
, PAY_JP_REPORT_PKG.GET_GS_REC_ID('WP_APPL_CAT_IV_ID'))
AND PEEV_APPL_CAT.SCREEN_ENTRY_VALUE = DECODE(PPA.ELEMENT_SET_ID
, PAY_JP_REPORT_PKG.GET_GS_REC_ID('SAN_ELE_SET_ID')
, 'S'
, PAY_JP_REPORT_PKG.GET_GS_REC_ID('GEP_ELE_SET_ID')
, 'G'
, 'I')
AND PLIV_ORG.INPUT_VALUE_ID = IV_ORG.ORG_IV_ID
AND ((PEEV_APPL_CAT.INPUT_VALUE_ID = PAY_JP_REPORT_PKG.GET_GS_REC_ID('HI_APPL_CAT_IV_ID')
AND PLIV_ORG.INPUT_VALUE_ID = PAY_JP_REPORT_PKG.GET_SI_REC_ID('HI_ORG_IV_ID')) OR (PEEV_APPL_CAT.INPUT_VALUE_ID = PAY_JP_REPORT_PKG.GET_GS_REC_ID('WP_APPL_CAT_IV_ID')
AND PLIV_ORG.INPUT_VALUE_ID IN ( PAY_JP_REPORT_PKG.GET_SI_REC_ID('WP_ORG_IV_ID')
, PAY_JP_REPORT_PKG.GET_SI_REC_ID('WPF_ORG_IV_ID'))))
AND PPA.EFFECTIVE_DATE BETWEEN PLIV_ORG.EFFECTIVE_START_DATE
AND PLIV_ORG.EFFECTIVE_END_DATE
AND PEE_ORG.ELEMENT_LINK_ID = PLIV_ORG.ELEMENT_LINK_ID
AND PEE_ORG.ASSIGNMENT_ID = PEE_APPL.ASSIGNMENT_ID
AND PPA.EFFECTIVE_DATE BETWEEN PEE_ORG.EFFECTIVE_START_DATE
AND PEE_ORG.EFFECTIVE_END_DATE
AND PEEV_ORG.ELEMENT_ENTRY_ID = PEE_ORG.ELEMENT_ENTRY_ID
AND PEEV_ORG.INPUT_VALUE_ID = PLIV_ORG.INPUT_VALUE_ID
AND PEEV_ORG.EFFECTIVE_START_DATE = PEE_ORG.EFFECTIVE_START_DATE
AND PEEV_ORG.EFFECTIVE_END_DATE = PEE_ORG.EFFECTIVE_END_DATE
AND PEEV_HI_NUM.ELEMENT_ENTRY_ID = PEE_ORG.ELEMENT_ENTRY_ID
AND PEEV_HI_NUM.INPUT_VALUE_ID = PAY_JP_REPORT_PKG.GET_SI_REC_ID('HI_NUM_IV_ID')
AND PEEV_HI_NUM.EFFECTIVE_START_DATE = PEE_ORG.EFFECTIVE_START_DATE
AND PEEV_HI_NUM.EFFECTIVE_END_DATE = PEE_ORG.EFFECTIVE_END_DATE
AND PEEV_WP_NUM.ELEMENT_ENTRY_ID = PEE_ORG.ELEMENT_ENTRY_ID
AND PEEV_WP_NUM.INPUT_VALUE_ID = PAY_JP_REPORT_PKG.GET_SI_REC_ID('WP_NUM_IV_ID')
AND PEEV_WP_NUM.EFFECTIVE_START_DATE = PEE_ORG.EFFECTIVE_START_DATE
AND PEEV_WP_NUM.EFFECTIVE_END_DATE = PEE_ORG.EFFECTIVE_END_DATE GROUP BY PAA.ASSIGNMENT_ACTION_ID
, PPA.ELEMENT_SET_ID

Columns