View: PAY_JP_PAYJPDMW_VALUES_V

Product:	PAY - Payroll
Description:	This is a balance view. This view is used for Japanese localization only.
Implementation/DBA Data:	APPS.PAY_JP_PAYJPDMW_VALUES_V

View Text

SELECT /*+ ORDERED USE_NL(ASG BAL DEF DIM) */ ASG.ASSIGNMENT_ID
, DEF.BALANCE_TYPE_ID
, DEF.DEFINED_BALANCE_ID
, DIM.DATABASE_ITEM_SUFFIX
, PAY_JP_BALANCE_VIEW_PKG.GET_VALUE( ASG.ASSIGNMENT_ID
, FS.EFFECTIVE_DATE
, DEF.DEFINED_BALANCE_ID
, DIM.DIMENSION_LEVEL
, PAY_CORE_UTILS.GET_PARAMETER('DATE_TYPE'
, DIM.DESCRIPTION)
, DIM.PERIOD_TYPE
, DIM.START_DATE_CODE
, DIM.DIMENSION_NAME) VALUE
, NULL ELEMENT_NAME
, NULL EFFECTIVE_DATE
, NULL SOURCE_ID
, 1 DISPLAY_ORDER FROM FND_SESSIONS FS
, PER_ALL_ASSIGNMENTS_F ASG
, PAY_DEFINED_BALANCES DEF
, PAY_BALANCE_DIMENSIONS_VL DIM WHERE FS.SESSION_ID = USERENV('SESSIONID') AND FS.EFFECTIVE_DATE BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE AND NVL(DEF.BUSINESS_GROUP_ID
, ASG.BUSINESS_GROUP_ID) = ASG.BUSINESS_GROUP_ID AND NVL(DEF.LEGISLATION_CODE
, 'JP') = 'JP' AND DIM.BALANCE_DIMENSION_ID = DEF.BALANCE_DIMENSION_ID AND NVL(DIM.DIMENSION_LEVEL
, 'ASG') <> 'GRP' AND NVL(DIM.PERIOD_TYPE
, 'RUN') <> 'PAYMENT' AND DIM.DIMENSION_NAME NOT IN ('_ELM_PTD'
, '_ELM_LTD') UNION ALL SELECT /*+ ORDERED NO_MERGE(V) USE_NL(ASG2 DEF DIM V) */ ASG2.ASSIGNMENT_ID
, DEF.BALANCE_TYPE_ID
, DEF.DEFINED_BALANCE_ID
, DIM.DATABASE_ITEM_SUFFIX
, PAY_BALANCE_PKG.GET_VALUE( DEF.DEFINED_BALANCE_ID
, DECODE(DIM.DIMENSION_NAME
, '_ELM_PTD'
, V.PERIOD_LATEST_ACTION_ID
, V.LATEST_ACTION_ID)
, NULL
, NULL
, NULL
, NULL
, NULL
, V.SOURCE_ID
, NULL) VALUE
, ( SELECT MIN(PETTL.ELEMENT_NAME) FROM PAY_ELEMENT_ENTRIES_F PEE
, PAY_ELEMENT_TYPES_F_TL PETTL WHERE PEE.ELEMENT_ENTRY_ID = V.SOURCE_ID AND PETTL.ELEMENT_TYPE_ID = PEE.ELEMENT_TYPE_ID AND PETTL.LANGUAGE = USERENV('LANG') ) ELEMENT_NAME
, DECODE(DIM.DIMENSION_NAME
, '_ELM_PTD'
, V.PERIOD_EFFECTIVE_DATE
, V.EFFECTIVE_DATE) EFFECTIVE_DATE
, V.SOURCE_ID
, 0 DISPLAY_ORDER FROM FND_SESSIONS FS2
, PER_ALL_ASSIGNMENTS_F ASG2
, PAY_DEFINED_BALANCES DEF
, PAY_BALANCE_DIMENSIONS_VL DIM
, ( SELECT /*+ ORDERED PUSH_SUBQ USE_NL(CON ASG PTP PAC PAA PPA PRR PIV FEED) */ ASG.ASSIGNMENT_ID
, FEED.BALANCE_TYPE_ID
, FND_NUMBER.CANONICAL_TO_NUMBER(PAC.CONTEXT_VALUE) SOURCE_ID
, TO_NUMBER(SUBSTR( MAX(DECODE(PPA.TIME_PERIOD_ID
, PTP.TIME_PERIOD_ID
, TO_CHAR(PAA.ACTION_SEQUENCE
, 'FM099999999999999') || TO_CHAR(PAA.ASSIGNMENT_ACTION_ID)))
, 16)) PERIOD_LATEST_ACTION_ID
, TO_DATE(SUBSTR( MAX(DECODE(PPA.TIME_PERIOD_ID
, PTP.TIME_PERIOD_ID
, TO_CHAR(PAA.ACTION_SEQUENCE
, 'FM099999999999999') || TO_CHAR(PPA.EFFECTIVE_DATE
, 'YYYY/MM/DD')))
, 16)
, 'YYYY/MM/DD') PERIOD_EFFECTIVE_DATE
, TO_NUMBER(SUBSTR( MAX(TO_CHAR(PAA.ACTION_SEQUENCE
, 'FM099999999999999') || TO_CHAR(PAA.ASSIGNMENT_ACTION_ID))
, 16)) LATEST_ACTION_ID
, TO_DATE(SUBSTR( MAX(TO_CHAR(PAA.ACTION_SEQUENCE
, 'FM099999999999999') || TO_CHAR(PPA.EFFECTIVE_DATE
, 'YYYY/MM/DD'))
, 16)
, 'YYYY/MM/DD') EFFECTIVE_DATE FROM FND_SESSIONS FS
, FF_CONTEXTS CON
, PER_ALL_ASSIGNMENTS_F ASG
, PER_TIME_PERIODS PTP
, PAY_ACTION_CONTEXTS PAC
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, PAY_RUN_RESULTS PRR
, PAY_INPUT_VALUES_F PIV
, PAY_BALANCE_FEEDS_F FEED WHERE FS.SESSION_ID = USERENV('SESSIONID') AND CON.CONTEXT_NAME = 'ORIGINAL_ENTRY_ID' AND FS.EFFECTIVE_DATE BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE AND PTP.PAYROLL_ID(+) = ASG.PAYROLL_ID AND FS.EFFECTIVE_DATE BETWEEN NVL(PTP.START_DATE
, FS.EFFECTIVE_DATE)
AND NVL(PTP.END_DATE
, FS.EFFECTIVE_DATE) AND PAC.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID AND PAC.CONTEXT_ID = CON.CONTEXT_ID AND PAA.ASSIGNMENT_ACTION_ID = PAC.ASSIGNMENT_ACTION_ID AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID AND PPA.EFFECTIVE_DATE <= FS.EFFECTIVE_DATE /* ORIGINAL_ENTRY_ID ACTION CONTEXT IS STORED EVEN WHEN THE ELEMENT IS SKIPPED (RUN RESULT DOES NOT EXIST). CHECK ORIGINAL_ENTRY_ID REALLY EXISTS OR NOT. */ AND PRR.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID AND PRR.STATUS IN ('P'
, 'PA') /* IN CASE OF ADDITIVE ENTRY
, BOTH P
AND PA CAN EXIST FOR A SAME SOURCE_ID. CHECK LEGISLATION_RULE ADJUSTMENT_EE_SOURCE = "T"(TARGET_ENTRY_ID). */ AND DECODE(DECODE(PRR.SOURCE_TYPE
, 'R'
, 'V'
, PRR.SOURCE_TYPE)
, 'V'
, ( SELECT PRR2.SOURCE_ID FROM PAY_RUN_RESULTS PRR2 WHERE PRR2.RUN_RESULT_ID = PRR.SOURCE_ID )
, PRR.SOURCE_ID) = FND_NUMBER.CANONICAL_TO_NUMBER(PAC.CONTEXT_VALUE) /* DO NOT JOIN PRRV WHICH CAN BE NO_DATA_FOUND BY SPARSE MATRIX. NOTE PRR.ELEMENT_TYPE_ID DOES NOT POINT THE SAME ELEMENT AS FOLLOWS. 1) BALANCE INITIALIZATION POINTS BALANCE INITIALIZATION ELEMENT. 2) BALANCE ADJUSTMENT POINTS BALANCE ADJUSTED ELEMENT. 3) OTHER ACTIONS POINTS THE SAME ELEMENT.(STRICTLY SPEAKING
, ELEMENT_LINK_ID MUST BE THE SAME). 4) IN CASE OF INDIRECT RESULT
, MULTIPLE RESULTS CAN EXIST FOR THE SAME SOURCE_ID. */ AND PIV.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID AND PPA.EFFECTIVE_DATE BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE AND FEED.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID AND PPA.EFFECTIVE_DATE BETWEEN FEED.EFFECTIVE_START_DATE
AND FEED.EFFECTIVE_END_DATE GROUP BY ASG.ASSIGNMENT_ID
, FEED.BALANCE_TYPE_ID
, PAC.CONTEXT_VALUE ) V WHERE FS2.SESSION_ID = USERENV('SESSIONID') AND FS2.EFFECTIVE_DATE BETWEEN ASG2.EFFECTIVE_START_DATE
AND ASG2.EFFECTIVE_END_DATE AND NVL(DEF.BUSINESS_GROUP_ID
, ASG2.BUSINESS_GROUP_ID) = ASG2.BUSINESS_GROUP_ID AND NVL(DEF.LEGISLATION_CODE
, 'JP') = 'JP' AND DIM.BALANCE_DIMENSION_ID = DEF.BALANCE_DIMENSION_ID AND DIM.DIMENSION_NAME IN ('_ELM_PTD'
, '_ELM_LTD') AND V.ASSIGNMENT_ID = ASG2.ASSIGNMENT_ID AND V.BALANCE_TYPE_ID = DEF.BALANCE_TYPE_ID AND NOT ( DIM.DIMENSION_NAME = '_ELM_PTD' AND V.PERIOD_LATEST_ACTION_ID IS NULL)

Columns