View: PAY_JP_GEPPEN_SANTEI_MT_V

Product:	PAY - Payroll
Description:	PAY_JP_GEPPEN_SANTEI_MT_V is used by Japanese statutory report of GEPPEN SANTEI for magtape file
Implementation/DBA Data:	APPS.PAY_JP_GEPPEN_SANTEI_MT_V

View Text

SELECT /*+ ORDERED NO_MERGE(HRV) INDEX(PPA PAY_PAYROLL_ACTIONS_FK1) INDEX(PAA PAY_ASSIGNMENT_ACTIONS_N50) INDEX(PEE PAY_ELEMENT_ENTRIES_F_FK3) INDEX(PEEV PAY_ELEMENT_ENTRY_VALUES_F_N50) INDEX(PA PER_ASSIGNMENTS_F_PK) */ PAA.ASSIGNMENT_ACTION_ID
, PA.ASSIGNMENT_ID + 0
, PA.PERSON_ID + 0
, HRV.BUSINESS_GROUP_ID
, PPA.DATE_EARNED
, PPA.EFFECTIVE_DATE
, DECODE(PPA.ELEMENT_SET_ID
, HRV.ELEMENT_SET_ID_GEPPEN
, 'G'
, 'S')
, PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_NUMBER(HRV.IV_ID_SI_ORG
, PEE.ASSIGNMENT_ID
, PPA.EFFECTIVE_DATE)
, SUBSTRB(PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR(HRV.IV_ID_HI_NUM
, PEE.ASSIGNMENT_ID
, PPA.DATE_EARNED)
, 1
, 10)
, SUBSTRB(PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR(HRV.IV_ID_WP_NUM
, PEE.ASSIGNMENT_ID
, PPA.DATE_EARNED)
, 1
, 10)
, SUM(HRV.SI_TYPE)
FROM ( SELECT PBG.BUSINESS_GROUP_ID
, PESG.ELEMENT_SET_ID ELEMENT_SET_ID_GEPPEN
, PESS.ELEMENT_SET_ID ELEMENT_SET_ID_SANTEI
, PIVHI1.INPUT_VALUE_ID IV_ID_HI_COMP_APPLY_MTH
, PIVWP1.INPUT_VALUE_ID IV_ID_WP_COMP_APPLY_MTH
, PIVHI2.INPUT_VALUE_ID IV_ID_HI_COMP_APPLY_TYP
, PIVWP2.INPUT_VALUE_ID IV_ID_WP_COMP_APPLY_TYP
, PIVCHI.INPUT_VALUE_ID IV_ID_HI_NUM
, PIVCWP.INPUT_VALUE_ID IV_ID_WP_NUM
, PIVCO.INPUT_VALUE_ID IV_ID_SI_ORG
, DECODE(PIVCO.NAME
, 'HI_LOCATION'
, 1
, 'WP_LOCATION'
, 2
, 'WPF_LOCATION'
, 4) SI_TYPE
FROM PER_BUSINESS_GROUPS_PERF PBG
, PAY_ELEMENT_SETS PESG
, PAY_ELEMENT_SETS PESS
, PAY_ELEMENT_TYPES_F PETHI
, PAY_INPUT_VALUES_F PIVHI1
, PAY_INPUT_VALUES_F PIVHI2
, PAY_ELEMENT_TYPES_F PETWP
, PAY_INPUT_VALUES_F PIVWP1
, PAY_INPUT_VALUES_F PIVWP2
, PAY_ELEMENT_TYPES_F PETC
, PAY_INPUT_VALUES_F PIVCHI
, PAY_INPUT_VALUES_F PIVCWP
, PAY_INPUT_VALUES_F PIVCO
WHERE PESG.ELEMENT_SET_NAME = 'GEP'
AND NVL(PESG.BUSINESS_GROUP_ID
, PBG.BUSINESS_GROUP_ID) = PBG.BUSINESS_GROUP_ID
AND NVL(PESG.LEGISLATION_CODE
, PBG.LEGISLATION_CODE) = PBG.LEGISLATION_CODE
AND PESS.ELEMENT_SET_NAME = 'SAN'
AND NVL(PESS.BUSINESS_GROUP_ID
, PBG.BUSINESS_GROUP_ID) = PBG.BUSINESS_GROUP_ID
AND NVL(PESS.LEGISLATION_CODE
, PBG.LEGISLATION_CODE) = PBG.LEGISLATION_CODE
AND PETHI.ELEMENT_NAME = 'COM_HI_SMR_INFO'
AND NVL(PETHI.BUSINESS_GROUP_ID
, PBG.BUSINESS_GROUP_ID) = PBG.BUSINESS_GROUP_ID
AND NVL(PETHI.LEGISLATION_CODE
, PBG.LEGISLATION_CODE) = PBG.LEGISLATION_CODE
AND PIVHI1.ELEMENT_TYPE_ID = PETHI.ELEMENT_TYPE_ID
AND PIVHI1.NAME = 'APPLY_MTH'
AND PIVHI2.ELEMENT_TYPE_ID = PETHI.ELEMENT_TYPE_ID
AND PIVHI2.NAME = 'APPLY_TYPE'
AND PETWP.ELEMENT_NAME = 'COM_WP_SMR_INFO'
AND NVL(PETWP.BUSINESS_GROUP_ID
, PBG.BUSINESS_GROUP_ID) = PBG.BUSINESS_GROUP_ID
AND NVL(PETWP.LEGISLATION_CODE
, PBG.LEGISLATION_CODE) = PBG.LEGISLATION_CODE
AND PIVWP1.ELEMENT_TYPE_ID = PETWP.ELEMENT_TYPE_ID
AND PIVWP1.NAME = 'APPLY_MTH'
AND PIVWP2.ELEMENT_TYPE_ID = PETWP.ELEMENT_TYPE_ID
AND PIVWP2.NAME = 'APPLY_TYPE'
AND PETC.ELEMENT_NAME = 'COM_SI_INFO'
AND NVL(PETC.BUSINESS_GROUP_ID
, PBG.BUSINESS_GROUP_ID) = PBG.BUSINESS_GROUP_ID
AND NVL(PETC.LEGISLATION_CODE
, PBG.LEGISLATION_CODE) = PBG.LEGISLATION_CODE
AND PIVCHI.ELEMENT_TYPE_ID = PETC.ELEMENT_TYPE_ID
AND PIVCHI.NAME = 'HI_CARD_NUM'
AND PIVCWP.ELEMENT_TYPE_ID = PETC.ELEMENT_TYPE_ID
AND PIVCWP.NAME = 'WP_SERIAL_NUM'
AND PIVCO.ELEMENT_TYPE_ID = PETC.ELEMENT_TYPE_ID
AND PIVCO.NAME IN ('HI_LOCATION'
, 'WP_LOCATION'
, 'WPF_LOCATION') GROUP BY PBG.BUSINESS_GROUP_ID
, PESG.ELEMENT_SET_ID
, PESS.ELEMENT_SET_ID
, PIVHI1.INPUT_VALUE_ID
, PIVWP1.INPUT_VALUE_ID
, PIVHI2.INPUT_VALUE_ID
, PIVWP2.INPUT_VALUE_ID
, PIVCHI.INPUT_VALUE_ID
, PIVCWP.INPUT_VALUE_ID
, PIVCO.INPUT_VALUE_ID
, PIVCO.NAME ) HRV
, PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_ELEMENT_ENTRIES_F PEE
, PAY_ELEMENT_ENTRY_VALUES_F PEEV1
, PAY_ELEMENT_ENTRY_VALUES_F PEEV2
, PER_ALL_ASSIGNMENTS_F PA
WHERE PPA.BUSINESS_GROUP_ID = HRV.BUSINESS_GROUP_ID /* THIS VIEW USED BY REPORTS ONLY TARGET RUN WITH GEPPEN
AND SANTEI ELEMENT SET */
AND PPA.ACTION_TYPE = 'R'
AND PPA.ELEMENT_SET_ID IN (HRV.ELEMENT_SET_ID_GEPPEN
, HRV.ELEMENT_SET_ID_SANTEI)
AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PAA.ACTION_STATUS = 'C'
AND PA.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PA.PRIMARY_FLAG = 'Y' /*
AND PA.BUSINESS_GROUP_ID + 0 = HRV.BUSINESS_GROUP_ID */
AND PPA.EFFECTIVE_DATE BETWEEN PA.EFFECTIVE_START_DATE
AND PA.EFFECTIVE_END_DATE
AND PEE.UPDATING_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PEE.EFFECTIVE_START_DATE = PPA.EFFECTIVE_DATE
AND PEE.ENTRY_TYPE = 'E'
AND PEEV1.ELEMENT_ENTRY_ID = PEE.ELEMENT_ENTRY_ID
AND PEEV1.EFFECTIVE_START_DATE = PEE.EFFECTIVE_START_DATE
AND PEEV1.EFFECTIVE_END_DATE = PEE.EFFECTIVE_END_DATE
AND ( ( PEEV1.INPUT_VALUE_ID = HRV.IV_ID_HI_COMP_APPLY_MTH
AND HRV.SI_TYPE = 1 ) OR ( PEEV1.INPUT_VALUE_ID = HRV.IV_ID_WP_COMP_APPLY_MTH
AND HRV.SI_TYPE IN (2
, 4) ) )
AND ( ( PPA.DATE_EARNED < TO_DATE('2003/04/01'
, 'YYYY/MM/DD')
AND PEEV1.SCREEN_ENTRY_VALUE = DECODE(PPA.ELEMENT_SET_ID
, HRV.ELEMENT_SET_ID_GEPPEN
, TO_CHAR(PPA.DATE_EARNED
, 'YYYYMM')
, TO_CHAR(PPA.DATE_EARNED
, 'YYYY') || '10') ) OR ( PPA.DATE_EARNED >= TO_DATE('2003/04/01'
, 'YYYY/MM/DD')
AND PEEV1.SCREEN_ENTRY_VALUE = DECODE(PPA.ELEMENT_SET_ID
, HRV.ELEMENT_SET_ID_GEPPEN
, TO_CHAR(PPA.DATE_EARNED
, 'YYYYMM')
, TO_CHAR(PPA.DATE_EARNED
, 'YYYY') || '09') ) )
AND PEEV2.ELEMENT_ENTRY_ID = PEE.ELEMENT_ENTRY_ID
AND PEEV2.EFFECTIVE_START_DATE = PEE.EFFECTIVE_START_DATE
AND PEEV2.EFFECTIVE_END_DATE = PEE.EFFECTIVE_END_DATE
AND ( ( PEEV2.INPUT_VALUE_ID = HRV.IV_ID_HI_COMP_APPLY_TYP
AND HRV.SI_TYPE = 1 ) OR ( PEEV2.INPUT_VALUE_ID = HRV.IV_ID_WP_COMP_APPLY_TYP
AND HRV.SI_TYPE IN (2
, 4) ) )
AND PEEV2.SCREEN_ENTRY_VALUE = DECODE(PPA.ELEMENT_SET_ID
, HRV.ELEMENT_SET_ID_GEPPEN
, 'G'
, 'S') GROUP BY PAA.ASSIGNMENT_ACTION_ID
, PA.ASSIGNMENT_ID
, PA.PERSON_ID
, HRV.BUSINESS_GROUP_ID
, PPA.DATE_EARNED
, PPA.EFFECTIVE_DATE
, DECODE(PPA.ELEMENT_SET_ID
, HRV.ELEMENT_SET_ID_GEPPEN
, 'G'
, 'S')
, PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_NUMBER(HRV.IV_ID_SI_ORG
, PEE.ASSIGNMENT_ID
, PPA.EFFECTIVE_DATE)
, SUBSTRB(PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR(HRV.IV_ID_HI_NUM
, PEE.ASSIGNMENT_ID
, PPA.DATE_EARNED)
, 1
, 10)
, SUBSTRB(PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR(HRV.IV_ID_WP_NUM
, PEE.ASSIGNMENT_ID
, PPA.DATE_EARNED)
, 1
, 10) UNION SELECT /*+ ORDERED NO_MERGE(HRV) INDEX(PPA PAY_PAYROLL_ACTIONS_FK1) INDEX(PAA PAY_ASSIGNMENT_ACTIONS_N50) INDEX(PRR PAY_RUN_RESULTS_N50) INDEX(PRRV PAY_RUN_RESULT_VALUES_N50) INDEX(PA PER_ASSIGNMENTS_F_PK) */ PAA.ASSIGNMENT_ACTION_ID
, PA.ASSIGNMENT_ID
, PA.PERSON_ID
, HRV.BUSINESS_GROUP_ID
, PPA.DATE_EARNED
, PPA.EFFECTIVE_DATE
, 'S'
, PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_NUMBER(HRV.IV_ID_SI_ORG
, PAA.ASSIGNMENT_ID
, PPA.EFFECTIVE_DATE)
, SUBSTRB(PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR(HRV.IV_ID_HI_NUM
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED)
, 1
, 10)
, SUBSTRB(PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR(HRV.IV_ID_WP_NUM
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED)
, 1
, 10)
, SUM(HRV.SI_TYPE)
FROM ( SELECT PBG.BUSINESS_GROUP_ID
, PESG.ELEMENT_SET_ID ELEMENT_SET_ID_GEPPEN
, PESS.ELEMENT_SET_ID ELEMENT_SET_ID_SANTEI
, PIVHIG.INPUT_VALUE_ID IV_ID_HI_GEPPEN_MTH
, PIVWPG.INPUT_VALUE_ID IV_ID_WP_GEPPEN_MTH
, PIVCHI.INPUT_VALUE_ID IV_ID_HI_NUM
, PIVCWP.INPUT_VALUE_ID IV_ID_WP_NUM
, PIVCO.INPUT_VALUE_ID IV_ID_SI_ORG
, DECODE(PIVCO.NAME
, 'HI_LOCATION'
, 1
, 'WP_LOCATION'
, 2
, 'WPF_LOCATION'
, 4) SI_TYPE
FROM PER_BUSINESS_GROUPS_PERF PBG
, PAY_ELEMENT_SETS PESG
, PAY_ELEMENT_SETS PESS
, PAY_ELEMENT_TYPES_F PETSIG
, PAY_INPUT_VALUES_F PIVHIG
, PAY_INPUT_VALUES_F PIVWPG
, PAY_ELEMENT_TYPES_F PETC
, PAY_INPUT_VALUES_F PIVCHI
, PAY_INPUT_VALUES_F PIVCWP
, PAY_INPUT_VALUES_F PIVCO
WHERE PESG.ELEMENT_SET_NAME = 'GEP'
AND NVL(PESG.BUSINESS_GROUP_ID
, PBG.BUSINESS_GROUP_ID) = PBG.BUSINESS_GROUP_ID
AND NVL(PESG.LEGISLATION_CODE
, PBG.LEGISLATION_CODE) = PBG.LEGISLATION_CODE
AND PESS.ELEMENT_SET_NAME = 'SAN'
AND NVL(PESS.BUSINESS_GROUP_ID
, PBG.BUSINESS_GROUP_ID) = PBG.BUSINESS_GROUP_ID
AND NVL(PESS.LEGISLATION_CODE
, PBG.LEGISLATION_CODE) = PBG.LEGISLATION_CODE
AND PETSIG.ELEMENT_NAME = 'SAN_GEP_MTH_RSLT'
AND NVL(PETSIG.BUSINESS_GROUP_ID
, PBG.BUSINESS_GROUP_ID) = PBG.BUSINESS_GROUP_ID
AND NVL(PETSIG.LEGISLATION_CODE
, PBG.LEGISLATION_CODE) = PBG.LEGISLATION_CODE
AND PIVHIG.ELEMENT_TYPE_ID = PETSIG.ELEMENT_TYPE_ID
AND PIVHIG.NAME = 'HI'
AND PIVWPG.ELEMENT_TYPE_ID = PETSIG.ELEMENT_TYPE_ID
AND PIVWPG.NAME = 'WP'
AND PETC.ELEMENT_NAME = 'COM_SI_INFO'
AND NVL(PETC.BUSINESS_GROUP_ID
, PBG.BUSINESS_GROUP_ID) = PBG.BUSINESS_GROUP_ID
AND NVL(PETC.LEGISLATION_CODE
, PBG.LEGISLATION_CODE) = PBG.LEGISLATION_CODE
AND PIVCHI.ELEMENT_TYPE_ID = PETC.ELEMENT_TYPE_ID
AND PIVCHI.NAME = 'HI_CARD_NUM'
AND PIVCWP.ELEMENT_TYPE_ID = PETC.ELEMENT_TYPE_ID
AND PIVCWP.NAME = 'WP_SERIAL_NUM'
AND PIVCO.ELEMENT_TYPE_ID = PETC.ELEMENT_TYPE_ID
AND PIVCO.NAME IN ('HI_LOCATION'
, 'WP_LOCATION'
, 'WPF_LOCATION') GROUP BY PBG.BUSINESS_GROUP_ID
, PESG.ELEMENT_SET_ID
, PESS.ELEMENT_SET_ID
, PIVHIG.INPUT_VALUE_ID
, PIVWPG.INPUT_VALUE_ID
, PIVCHI.INPUT_VALUE_ID
, PIVCWP.INPUT_VALUE_ID
, PIVCO.INPUT_VALUE_ID
, PIVCO.NAME ) HRV
, PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_RUN_RESULTS PRR
, PAY_RUN_RESULT_VALUES PRRV
, PER_ALL_ASSIGNMENTS_F PA
WHERE PPA.BUSINESS_GROUP_ID = HRV.BUSINESS_GROUP_ID /* THIS VIEW USED BY REPORTS ONLY TARGET RUN WITH SANTEI ELEMENT SET */
AND PPA.ACTION_TYPE = 'R'
AND PPA.ELEMENT_SET_ID = HRV.ELEMENT_SET_ID_SANTEI
AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PAA.ACTION_STATUS = 'C'
AND PRR.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PRR.STATUS = 'P'
AND PRRV.RUN_RESULT_ID = PRR.RUN_RESULT_ID
AND ( ( PRRV.INPUT_VALUE_ID = HRV.IV_ID_HI_GEPPEN_MTH
AND HRV.SI_TYPE = 1
AND PRRV.RESULT_VALUE IS NOT NULL ) OR ( PRRV.INPUT_VALUE_ID = HRV.IV_ID_WP_GEPPEN_MTH
AND HRV.SI_TYPE IN (2
, 4)
AND PRRV.RESULT_VALUE IS NOT NULL ) )
AND PA.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PA.PRIMARY_FLAG = 'Y' /*
AND PA.BUSINESS_GROUP_ID + 0 = HRV.BUSINESS_GROUP_ID */
AND PPA.EFFECTIVE_DATE BETWEEN PA.EFFECTIVE_START_DATE
AND PA.EFFECTIVE_END_DATE
AND PRRV.RESULT_VALUE IS NOT NULL GROUP BY PAA.ASSIGNMENT_ACTION_ID
, PA.ASSIGNMENT_ID
, PA.PERSON_ID
, HRV.BUSINESS_GROUP_ID
, PPA.DATE_EARNED
, PPA.EFFECTIVE_DATE
, 'S'
, PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_NUMBER(HRV.IV_ID_SI_ORG
, PAA.ASSIGNMENT_ID
, PPA.EFFECTIVE_DATE)
, SUBSTRB(PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR(HRV.IV_ID_HI_NUM
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED)
, 1
, 10)
, SUBSTRB(PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR(HRV.IV_ID_WP_NUM
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED)
, 1
, 10)

Columns