View: PAY_JP_SI_CHANGE_OF_ADDRESS_V

Product:	PAY - Payroll
Description:	PAY_JP_SI_CHANGE_OF_ADDRESS_V is used by Japanese statutory report of KOUNEN HIHOKENSHA JYUSHO HENKOU TODOKE
Implementation/DBA Data:	APPS.PAY_JP_SI_CHANGE_OF_ADDRESS_V

View Text

SELECT /*+ ORDERED NO_MERGE(V) USE_NL(PAD2 PA PEE PEL PEEV1 PEEV2) */ PA.ASSIGNMENT_ID
, PA.PERSON_ID
, V1.BUSINESS_GROUP_ID
, PAD1.DATE_FROM EFFECTIVE_DATE
, PAD1.ADDRESS_ID NEW_ADDRESS_ID
, PAD2.ADDRESS_ID PREV_ADDRESS_ID
, PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_NUMBER( DECODE(PEEV1.INPUT_VALUE_ID
, V1.HI_QUALIFDATE_IV_ID
, V1.HI_ORG_IV_ID
, V1.WP_QUALIFDATE_IV_ID
, V1.WP_ORG_IV_ID
, V1.WPF_QUALIFDATE_IV_ID
, V1.WPF_ORG_IV_ID)
, /* NOT PEE.ASSIGNMENT_ID BUT PA.ASSIGNMENT_ID FOR BETTER PERFORMANCE. */ PA.ASSIGNMENT_ID
, PAD1.DATE_FROM) ORGANIZATION_ID
, SUBSTRB(PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR( V1.HI_NUMBER_IV_ID
, PEE.ASSIGNMENT_ID
, PAD1.DATE_FROM)
, 1
, 10) HI_NUMBER
, SUBSTRB(PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR( V1.WP_NUMBER_IV_ID
, PEE.ASSIGNMENT_ID
, PAD1.DATE_FROM)
, 1
, 10) WP_NUMBER
, SUM(DECODE(PEEV1.INPUT_VALUE_ID
, V1.HI_QUALIFDATE_IV_ID
, 1
, V1.WP_QUALIFDATE_IV_ID
, 2
, V1.WPF_QUALIFDATE_IV_ID
, 4)) SI_TYPE
FROM ( SELECT PBG.BUSINESS_GROUP_ID
, HR_JP_ID_PKG.ELEMENT_TYPE_ID('COM_HI_QUALIFY_INFO'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') HI_ELEMENT_TYPE_ID
, HR_JP_ID_PKG.INPUT_VALUE_ID('COM_HI_QUALIFY_INFO'
, 'QUALIFY_DATE'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') HI_QUALIFDATE_IV_ID
, HR_JP_ID_PKG.INPUT_VALUE_ID('COM_HI_QUALIFY_INFO'
, 'DISQUALIFY_DATE'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') HI_DISQUALIFDATE_IV_ID
, HR_JP_ID_PKG.ELEMENT_TYPE_ID('COM_WP_QUALIFY_INFO'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') WP_ELEMENT_TYPE_ID
, HR_JP_ID_PKG.INPUT_VALUE_ID('COM_WP_QUALIFY_INFO'
, 'QUALIFY_DATE'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') WP_QUALIFDATE_IV_ID
, HR_JP_ID_PKG.INPUT_VALUE_ID('COM_WP_QUALIFY_INFO'
, 'DISQUALIFY_DATE'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') WP_DISQUALIFDATE_IV_ID
, HR_JP_ID_PKG.ELEMENT_TYPE_ID('COM_WPF_QUALIFY_INFO'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') WPF_ELEMENT_TYPE_ID
, HR_JP_ID_PKG.INPUT_VALUE_ID('COM_WPF_QUALIFY_INFO'
, 'QUALIFY_DATE'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') WPF_QUALIFDATE_IV_ID
, HR_JP_ID_PKG.INPUT_VALUE_ID('COM_WPF_QUALIFY_INFO'
, 'DISQUALIFY_DATE'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') WPF_DISQUALIFDATE_IV_ID
, HR_JP_ID_PKG.INPUT_VALUE_ID('COM_SI_INFO'
, 'HI_LOCATION'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') HI_ORG_IV_ID
, HR_JP_ID_PKG.INPUT_VALUE_ID('COM_SI_INFO'
, 'WP_LOCATION'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') WP_ORG_IV_ID
, HR_JP_ID_PKG.INPUT_VALUE_ID('COM_SI_INFO'
, 'WPF_LOCATION'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') WPF_ORG_IV_ID
, HR_JP_ID_PKG.INPUT_VALUE_ID('COM_SI_INFO'
, 'HI_CARD_NUM'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') HI_NUMBER_IV_ID
, HR_JP_ID_PKG.INPUT_VALUE_ID('COM_SI_INFO'
, 'WP_SERIAL_NUM'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') WP_NUMBER_IV_ID FROM PER_BUSINESS_GROUPS_PERF PBG GROUP BY PBG.BUSINESS_GROUP_ID ) V1
, PER_ADDRESSES PAD1
, PER_ADDRESSES PAD2
, PER_ALL_ASSIGNMENTS_F PA
, PAY_ELEMENT_ENTRIES_F PEE
, PAY_ELEMENT_LINKS_F PEL
, PAY_ELEMENT_ENTRY_VALUES_F PEEV1
, PAY_ELEMENT_ENTRY_VALUES_F PEEV2 WHERE PAD1.BUSINESS_GROUP_ID + 0 = V1.BUSINESS_GROUP_ID AND PAD1.ADDRESS_TYPE = 'JP_C' AND PAD2.PERSON_ID = PAD1.PERSON_ID AND PAD2.ADDRESS_TYPE = 'JP_C' AND PAD2.DATE_TO = PAD1.DATE_FROM - 1 AND ( PAD1.ADDRESS_LINE1 <> PAD2.ADDRESS_LINE1 OR (PAD1.ADDRESS_LINE1 IS NULL
AND PAD2.ADDRESS_LINE1 IS NOT NULL) OR (PAD2.ADDRESS_LINE1 IS NULL
AND PAD1.ADDRESS_LINE1 IS NOT NULL) OR PAD1.ADDRESS_LINE2 <> PAD2.ADDRESS_LINE2 OR (PAD1.ADDRESS_LINE2 IS NULL
AND PAD2.ADDRESS_LINE2 IS NOT NULL) OR (PAD2.ADDRESS_LINE2 IS NULL
AND PAD1.ADDRESS_LINE2 IS NOT NULL) OR PAD1.ADDRESS_LINE3 <> PAD2.ADDRESS_LINE3 OR (PAD1.ADDRESS_LINE3 IS NULL
AND PAD2.ADDRESS_LINE3 IS NOT NULL) OR (PAD2.ADDRESS_LINE3 IS NULL
AND PAD1.ADDRESS_LINE3 IS NOT NULL)) AND PA.PERSON_ID = PAD1.PERSON_ID AND PA.PRIMARY_FLAG = 'Y' AND PAD1.DATE_FROM BETWEEN PA.EFFECTIVE_START_DATE
AND PA.EFFECTIVE_END_DATE AND PEE.ASSIGNMENT_ID = PA.ASSIGNMENT_ID AND PEE.ENTRY_TYPE = 'E' AND PAD1.DATE_FROM BETWEEN PEE.EFFECTIVE_START_DATE
AND PEE.EFFECTIVE_END_DATE AND PEL.ELEMENT_LINK_ID = PEE.ELEMENT_LINK_ID AND PAD1.DATE_FROM BETWEEN PEL.EFFECTIVE_START_DATE
AND PEL.EFFECTIVE_END_DATE AND PEL.ELEMENT_TYPE_ID IN (V1.HI_ELEMENT_TYPE_ID
, V1.WP_ELEMENT_TYPE_ID
, V1.WPF_ELEMENT_TYPE_ID) 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 IN (V1.HI_QUALIFDATE_IV_ID
, V1.WP_QUALIFDATE_IV_ID
, V1.WPF_QUALIFDATE_IV_ID) 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 IN (V1.HI_DISQUALIFDATE_IV_ID
, V1.WP_DISQUALIFDATE_IV_ID
, V1.WPF_DISQUALIFDATE_IV_ID) AND PAD1.DATE_FROM BETWEEN FND_DATE.CANONICAL_TO_DATE(PEEV1.SCREEN_ENTRY_VALUE)
AND FND_DATE.CANONICAL_TO_DATE(NVL(PEEV2.SCREEN_ENTRY_VALUE
, '4712/12/31')) - 1 AND PAD2.DATE_TO BETWEEN FND_DATE.CANONICAL_TO_DATE(PEEV1.SCREEN_ENTRY_VALUE)
AND FND_DATE.CANONICAL_TO_DATE(NVL(PEEV2.SCREEN_ENTRY_VALUE
, '4712/12/31')) - 1 GROUP BY PA.ASSIGNMENT_ID
, PA.PERSON_ID
, V1.BUSINESS_GROUP_ID
, PAD1.DATE_FROM
, PAD1.ADDRESS_ID
, PAD2.ADDRESS_ID
, PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_NUMBER( DECODE(PEEV1.INPUT_VALUE_ID
, V1.HI_QUALIFDATE_IV_ID
, V1.HI_ORG_IV_ID
, V1.WP_QUALIFDATE_IV_ID
, V1.WP_ORG_IV_ID
, V1.WPF_QUALIFDATE_IV_ID
, V1.WPF_ORG_IV_ID)
, /* NOT PEE.ASSIGNMENT_ID BUT PA.ASSIGNMENT_ID FOR BETTER PERFORMANCE. */ PA.ASSIGNMENT_ID
, PAD1.DATE_FROM)
, SUBSTRB(PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR( V1.HI_NUMBER_IV_ID
, PEE.ASSIGNMENT_ID
, PAD1.DATE_FROM)
, 1
, 10)
, SUBSTRB(PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR( V1.WP_NUMBER_IV_ID
, PEE.ASSIGNMENT_ID
, PAD1.DATE_FROM)
, 1
, 10)

Columns