View: PAY_JP_SI_BON_PAYMENT_V

Product:	PAY - Payroll
Description:	This view returns Assignment Action of Bonus Payroll Process based on business_group_id and si_org_id.
Implementation/DBA Data:	APPS.PAY_JP_SI_BON_PAYMENT_V

View Text

SELECT /*+ ORDERED NO_MERGE(IV) AND_EQUAL(PPA PAY_PAYROLL_ACTIONS_FK6
, PAY_PAYROLL_ACTIONS_FK1) INDEX(PAA PAY_ASSIGNMENT_ACTIONS_N50) AND_EQUAL(PRR PAY_RUN_RESULTS_N50
, PAY_RUN_RESULTS_N1) INDEX(PRRV PAY_RUN_RESULT_VALUES_PK) INDEX(PA PER_ASSIGNMENTS_F_PK) */ PPA.PAYROLL_ACTION_ID PAYROLL_ACTION_ID
, PAA.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID
, PAA.ACTION_SEQUENCE ACTION_SEQUENCE
, PA.ASSIGNMENT_ID ASSIGNMENT_ID
, PA.PERSON_ID PERSON_ID
, IV.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PPA.EFFECTIVE_DATE EFFECTIVE_DATE
, PPA.DATE_EARNED DATE_EARNED
, PAY_CORE_UTILS.GET_PARAMETER('SI_SUM_MTD_BON'
, PPA.LEGISLATIVE_PARAMETERS) SI_SUM_MTD_BON
, PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_NUMBER(IV.ORG_IV_ID
, PAA.ASSIGNMENT_ID
, PPA.EFFECTIVE_DATE) SI_ORG_ID
, PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR(IV.HI_NUM_IV_ID
, PAA.ASSIGNMENT_ID
, PPA.EFFECTIVE_DATE) HI_NUMBER
, PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR(IV.WP_NUM_IV_ID
, PAA.ASSIGNMENT_ID
, PPA.EFFECTIVE_DATE) WP_NUMBER
, SUM(IV.SI_TYPE) SI_TYPE
FROM (SELECT /*+ ORDERED INDEX(SI_ELE PAY_ELEMENT_TYPES_F_UK2) INDEX(HIWP_RSLT_ELE PAY_ELEMENT_TYPES_F_UK2) INDEX(HIWP_RSLT_SAL_IV PAY_INPUT_VALUES_F_UK2) INDEX(HIWP_RSLT_MAT_IV PAY_INPUT_VALUES_F_UK2) INDEX(ORG PAY_INPUT_VALUES_F_UK2) INDEX(HI_NUM PAY_INPUT_VALUES_F_UK2) INDEX(WP_NUM PAY_INPUT_VALUES_F_UK2) */ PBG.BUSINESS_GROUP_ID
, PBG.LEGISLATION_CODE
, HIWP_RSLT_ELE.ELEMENT_TYPE_ID HIWP_RSLT_ELE_ID
, HIWP_RSLT_SAL_IV.INPUT_VALUE_ID HIWP_RSLT_SAL_IV_ID
, HIWP_RSLT_SAL_IV.UOM HIWP_RSLT_SAL_UOM
, HIWP_RSLT_MAT_IV.INPUT_VALUE_ID HIWP_RSLT_MAT_IV_ID
, HIWP_RSLT_MAT_IV.UOM HIWP_RSLT_MAT_UOM
, PES.ELEMENT_SET_NAME BON_ELE_SET_CODE
, PES.ELEMENT_SET_ID BON_ELE_SET_ID
, ORG.INPUT_VALUE_ID ORG_IV_ID
, HI_NUM.INPUT_VALUE_ID HI_NUM_IV_ID
, WP_NUM.INPUT_VALUE_ID WP_NUM_IV_ID
, DECODE(ORG.NAME
, 'HI_LOCATION'
, 1
, 'WP_LOCATION'
, 2
, 'WPF_LOCATION'
, 4) SI_TYPE
FROM /* THIS IS FASTER THAN PERF HERE */ PER_BUSINESS_GROUPS PBG
, PAY_ELEMENT_SETS PES
, PAY_ELEMENT_TYPES_F SI_ELE
, PAY_ELEMENT_TYPES_F HIWP_RSLT_ELE
, PAY_INPUT_VALUES_F HIWP_RSLT_SAL_IV
, PAY_INPUT_VALUES_F HIWP_RSLT_MAT_IV
, PAY_INPUT_VALUES_F ORG
, PAY_INPUT_VALUES_F HI_NUM
, PAY_INPUT_VALUES_F WP_NUM
WHERE PES.ELEMENT_SET_NAME = 'BON'
AND NVL(PES.BUSINESS_GROUP_ID
, PBG.BUSINESS_GROUP_ID) = PBG.BUSINESS_GROUP_ID
AND NVL(PES.LEGISLATION_CODE
, PBG.LEGISLATION_CODE) = PBG.LEGISLATION_CODE
AND SI_ELE.ELEMENT_NAME = 'COM_SI_INFO' /* ELM_SI */
AND NVL(SI_ELE.BUSINESS_GROUP_ID
, PBG.BUSINESS_GROUP_ID) = PBG.BUSINESS_GROUP_ID
AND NVL(SI_ELE.LEGISLATION_CODE
, PBG.LEGISLATION_CODE) = PBG.LEGISLATION_CODE
AND HIWP_RSLT_ELE.ELEMENT_NAME IN ('BON_HI_STD_BON'
, 'BON_WP_STD_BON')
AND NVL(HIWP_RSLT_ELE.BUSINESS_GROUP_ID
, PBG.BUSINESS_GROUP_ID) = PBG.BUSINESS_GROUP_ID
AND NVL(HIWP_RSLT_ELE.LEGISLATION_CODE
, PBG.LEGISLATION_CODE) = PBG.LEGISLATION_CODE
AND HIWP_RSLT_SAL_IV.ELEMENT_TYPE_ID = HIWP_RSLT_ELE.ELEMENT_TYPE_ID
AND HIWP_RSLT_SAL_IV.NAME = 'ERN_MONEY' /* IV_SAL */
AND HIWP_RSLT_MAT_IV.ELEMENT_TYPE_ID = HIWP_RSLT_ELE.ELEMENT_TYPE_ID
AND HIWP_RSLT_MAT_IV.NAME = 'ERN_KIND' /* IV_MAT */
AND ORG.ELEMENT_TYPE_ID = SI_ELE.ELEMENT_TYPE_ID
AND ((HIWP_RSLT_ELE.ELEMENT_NAME = 'BON_HI_STD_BON'
AND ORG.NAME = 'HI_LOCATION') /* IV_HI_ORG */ OR (HIWP_RSLT_ELE.ELEMENT_NAME = 'BON_WP_STD_BON'
AND ORG.NAME IN ('WP_LOCATION'
, /* IV_WP_ORG */ 'WPF_LOCATION'))) /* IV_WPF_ORG */
AND HI_NUM.ELEMENT_TYPE_ID = SI_ELE.ELEMENT_TYPE_ID
AND HI_NUM.NAME = 'HI_CARD_NUM' /* IV_HI_NUMBER */
AND WP_NUM.ELEMENT_TYPE_ID = SI_ELE.ELEMENT_TYPE_ID
AND WP_NUM.NAME = 'WP_SERIAL_NUM') IV
, /* IV_WP_NUMBER */ PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_RUN_RESULTS PRR
, PAY_RUN_RESULT_VALUES SAL_RV
, PAY_RUN_RESULT_VALUES MAT_RV
, PER_ALL_ASSIGNMENTS_F PA
WHERE PPA.BUSINESS_GROUP_ID = IV.BUSINESS_GROUP_ID
AND PPA.ACTION_TYPE='R'
AND PPA.ELEMENT_SET_ID = IV.BON_ELE_SET_ID
AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PAA.ACTION_STATUS = 'C' /* THIS IS FOR EXCLUDING NOT ONLY PAYMENT AMOUNT<1000 BUT ALSO SKIPPED PROCESS
FROM SI_TYPE. */ /* IF NVL(BON_HI(WP)_STD_BON.ERN_MONEY + ERN_KIND
, 0) < 1000 THEN THE EMP IS EXCLUDED. */ /* CHILDCARE ABSENT
AND EXPECTED TERMINATION ASSIGNMENT CAN BE INCLUDED BY SETTING PREMIUM ELEMENT ENTRY AS 0 */
AND PRR.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PRR.ELEMENT_TYPE_ID = IV.HIWP_RSLT_ELE_ID
AND SAL_RV.RUN_RESULT_ID = PRR.RUN_RESULT_ID
AND SAL_RV.INPUT_VALUE_ID = IV.HIWP_RSLT_SAL_IV_ID
AND MAT_RV.RUN_RESULT_ID = PRR.RUN_RESULT_ID
AND MAT_RV.INPUT_VALUE_ID = IV.HIWP_RSLT_MAT_IV_ID
AND DECODE(IV.HIWP_RSLT_SAL_UOM
, 'M'
, TO_NUMBER(NVL(SAL_RV.RESULT_VALUE
, '0'))
, 0) + DECODE(IV.HIWP_RSLT_MAT_UOM
, 'M'
, TO_NUMBER(NVL(MAT_RV.RESULT_VALUE
, '0'))
, 0) >= 1000 /* THIS VALIDATION IS FOR CASE OF INCONSISTENT BETWEEN SI_TYPE
AND RESULT BY PROCESS_FLAG */ /* THIS LOGIC CAN COVER THE CASE OF CHILDCARE ABSENCE
AND EXPECTED TERMINATION BECAUSE RESULT 0 PREMIUM WILL BE RETURNED. */
AND EXISTS( SELECT /*+ ORDERED INDEX(SI_BON_ERR_ELE PAY_ELEMENT_TYPES_F_UK2) AND_EQUAL(SI_BON_ERR_RV PAY_RUN_RESULTS_N50
, PAY_RUN_RESULTS_N1) */ NULL
FROM PAY_ELEMENT_TYPES_F SI_BON_ERR_ELE
, PAY_RUN_RESULTS SI_BON_ERR_RV
WHERE SI_BON_ERR_ELE.ELEMENT_NAME = DECODE(IV.SI_TYPE
, 1
, 'BON_HI_PREM_ER'
, /* JP_ELM_RSLT_HI_ER_BON */ 2
, 'BON_WP_PREM_ER'
, /* JP_ELM_RSLT_WP_ER_BON */ 4
, 'BON_WPF_PREM_ER') /* JP_ELM_RSLT_WPF_ER_BON */
AND NVL(SI_BON_ERR_ELE.BUSINESS_GROUP_ID
, IV.BUSINESS_GROUP_ID) = IV.BUSINESS_GROUP_ID
AND NVL(SI_BON_ERR_ELE.LEGISLATION_CODE
, IV.LEGISLATION_CODE) = IV.LEGISLATION_CODE
AND SI_BON_ERR_RV.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND SI_BON_ERR_RV.ELEMENT_TYPE_ID = SI_BON_ERR_ELE.ELEMENT_TYPE_ID)
AND PA.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID /* COMMENTED FOR REHIRE BEFORE FINAL PROCESSING DATE */ /*
AND PA.PRIMARY_FLAG = 'Y' */
AND PPA.EFFECTIVE_DATE BETWEEN PA.EFFECTIVE_START_DATE
AND PA.EFFECTIVE_END_DATE GROUP BY PPA.PAYROLL_ACTION_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ACTION_SEQUENCE
, PA.ASSIGNMENT_ID
, PA.PERSON_ID
, IV.BUSINESS_GROUP_ID
, PPA.EFFECTIVE_DATE
, PPA.DATE_EARNED
, PAY_CORE_UTILS.GET_PARAMETER('SI_SUM_MTD_BON'
, PPA.LEGISLATIVE_PARAMETERS)
, PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_NUMBER(IV.ORG_IV_ID
, PAA.ASSIGNMENT_ID
, PPA.EFFECTIVE_DATE)
, PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR(IV.HI_NUM_IV_ID
, PAA.ASSIGNMENT_ID
, PPA.EFFECTIVE_DATE)
, PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR(IV.WP_NUM_IV_ID
, PAA.ASSIGNMENT_ID
, PPA.EFFECTIVE_DATE)

Columns