View: PAY_JP_SI_QUALIFIED_V

Product:	PAY - Payroll
Description:	PAY_JP_SI_QUALIFIED_V is used by Japanese statutory report of KENPO KOUNEN HIHOKENSHA SHIKAKU SYUTOKU TODOKE.
Implementation/DBA Data:	APPS.PAY_JP_SI_QUALIFIED_V

View Text

SELECT /*+ ORDERED NO_MERGE(V) INDEX(PEEV PAY_ELEMENT_ENTRY_VALUES_F_N1) INDEX(PEE PAY_ELEMENT_ENTRIES_F_PK) INDEX(PA PER_ASSIGNMENTS_F_PK) */ PA.ASSIGNMENT_ID
, PA.PERSON_ID
, V1.BUSINESS_GROUP_ID
, PA.PERIOD_OF_SERVICE_ID
, FND_DATE.CANONICAL_TO_DATE(PEEV.SCREEN_ENTRY_VALUE)
, PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_NUMBER ( DECODE(PEEV.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 PA.ASSIGNMENT_ID BUT PEE.ASSIGNMENT_ID FOR BETTER PERFORMANCE. */ PEE.ASSIGNMENT_ID
, FND_DATE.CANONICAL_TO_DATE(PEEV.SCREEN_ENTRY_VALUE) )
, SUBSTRB(PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR(V1.HI_NUMBER_IV_ID
, PEE.ASSIGNMENT_ID
, FND_DATE.CANONICAL_TO_DATE(PEEV.SCREEN_ENTRY_VALUE))
, 1
, 10)
, SUBSTRB(PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR(V1.WP_NUMBER_IV_ID
, PEE.ASSIGNMENT_ID
, FND_DATE.CANONICAL_TO_DATE(PEEV.SCREEN_ENTRY_VALUE))
, 1
, 10)
, SUM(DECODE(PEEV.INPUT_VALUE_ID
, V1.HI_QUALIFDATE_IV_ID
, 1
, V1.WP_QUALIFDATE_IV_ID
, 2
, V1.WPF_QUALIFDATE_IV_ID
, 4))
, SUBSTRB(PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR(HR_JP_ID_PKG.INPUT_VALUE_ID('COM_SI_REPORT_INFO'
, 'OUTPUT_FLAG'
, NULL
, 'JP'
, 'FALSE')
, PA.ASSIGNMENT_ID
, FND_DATE.CANONICAL_TO_DATE(PEEV.SCREEN_ENTRY_VALUE))
, 1
, 1)
FROM ( SELECT PBG.BUSINESS_GROUP_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_WP_QUALIFY_INFO'
, 'QUALIFY_DATE'
, PBG.BUSINESS_GROUP_ID
, NULL
, 'FALSE') WP_QUALIFDATE_IV_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_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
, PAY_ELEMENT_ENTRY_VALUES_F PEEV
, PAY_ELEMENT_ENTRIES_F PEE
, PER_ALL_ASSIGNMENTS_F PA
WHERE PEEV.INPUT_VALUE_ID IN (V1.HI_QUALIFDATE_IV_ID
, V1.WP_QUALIFDATE_IV_ID
, V1.WPF_QUALIFDATE_IV_ID)
AND FND_DATE.CANONICAL_TO_DATE(PEEV.SCREEN_ENTRY_VALUE) BETWEEN PEEV.EFFECTIVE_START_DATE
AND PEEV.EFFECTIVE_END_DATE
AND PEE.ELEMENT_ENTRY_ID=PEEV.ELEMENT_ENTRY_ID
AND PEE.EFFECTIVE_START_DATE = PEEV.EFFECTIVE_START_DATE
AND PEE.EFFECTIVE_END_DATE = PEEV.EFFECTIVE_END_DATE
AND PEE.ENTRY_TYPE = 'E'
AND PA.ASSIGNMENT_ID = PEE.ASSIGNMENT_ID
AND PA.PRIMARY_FLAG = 'Y' /* AVOID USING PER_ASSIGNMENTS_F.BUSINESS_GROUP_ID INDEX WHICH CAUSES ORACLE ERROR */
AND PA.BUSINESS_GROUP_ID + 0 = V1.BUSINESS_GROUP_ID
AND FND_DATE.CANONICAL_TO_DATE(PEEV.SCREEN_ENTRY_VALUE) BETWEEN PA.EFFECTIVE_START_DATE
AND PA.EFFECTIVE_END_DATE GROUP BY PA.ASSIGNMENT_ID
, PA.PERSON_ID
, V1.BUSINESS_GROUP_ID
, PA.PERIOD_OF_SERVICE_ID
, FND_DATE.CANONICAL_TO_DATE(PEEV.SCREEN_ENTRY_VALUE)
, PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_NUMBER ( DECODE(PEEV.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 PA.ASSIGNMENT_ID BUT PEE.ASSIGNMENT_ID FOR BETTER PERFORMANCE. */ PEE.ASSIGNMENT_ID
, FND_DATE.CANONICAL_TO_DATE(PEEV.SCREEN_ENTRY_VALUE) )
, SUBSTRB(PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR(V1.HI_NUMBER_IV_ID
, PEE.ASSIGNMENT_ID
, FND_DATE.CANONICAL_TO_DATE(PEEV.SCREEN_ENTRY_VALUE))
, 1
, 10)
, SUBSTRB(PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR(V1.WP_NUMBER_IV_ID
, PEE.ASSIGNMENT_ID
, FND_DATE.CANONICAL_TO_DATE(PEEV.SCREEN_ENTRY_VALUE))
, 1
, 10)
, SUBSTRB(PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR(HR_JP_ID_PKG.INPUT_VALUE_ID('COM_SI_REPORT_INFO'
, 'OUTPUT_FLAG'
, NULL
, 'JP'
, 'FALSE')
, PA.ASSIGNMENT_ID
, FND_DATE.CANONICAL_TO_DATE(PEEV.SCREEN_ENTRY_VALUE))
, 1
, 1)

Columns